/** SERVICE中实现
* down excel
*/
【注：】getInputStream方法用于下载时提供一个输入流
public InputStream getInputStream() throws Exception {
List dataList = getAllTaxpayerInfo();
HSSFWorkbook wb = exportExcel(dataList);
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
wb.write(os);
} catch (IOException e) {
e.printStackTrace();
}
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
return is;
}
//excel 下载用此方法
public List getAllTaxpayerInfo() {
return taxpayerInfoDAO.getAllTaxpayerInfo();// from TaxpayerInfo order id desc;
}
/**
* 纳税人资料下载到Excel表格
* @param dataList
* @return
* @throws Exception
*/
public HSSFWorkbook exportExcel(List dataList) throws Exception {
HSSFWorkbook workbook = null;
try {
// 这里的数据即时你要从后台取得的数据
// 创建工作簿实例
workbook = new HSSFWorkbook();
// 创建工作表实例
HSSFSheet sheet = workbook.createSheet("nsrList");
// 设置列宽
this.setSheetColumnWidth(sheet);
// 获取样式
HSSFCellStyle style = this.createTitleStyle(workbook);
//
// 创建第一行标题,标题名字的本地信息通过resources从资源文件中获取
HSSFRow row = sheet.createRow((short) 0);// 建立新行
this.createCell(row, 0, style, HSSFCell.CELL_TYPE_STRING,
"税务登记表类型");
this.createCell(row, 1, style, HSSFCell.CELL_TYPE_STRING,
"纳税人识别号");
this.createCell(row, 2, style, HSSFCell.CELL_TYPE_STRING,
"纳税人编码");
this.createCell(row, 3, style, HSSFCell.CELL_TYPE_STRING,
"纳税人名称");
this.createCell(row, 4, style, HSSFCell.CELL_TYPE_STRING,
"注册地址");
this.createCell(row, 5, style, HSSFCell.CELL_TYPE_STRING,
"生产经营地址");
this.createCell(row, 6, style, HSSFCell.CELL_TYPE_STRING,
"经营范围");
this.createCell(row, 7, style, HSSFCell.CELL_TYPE_STRING,
"主管税务机关");
this.createCell(row, 8, style, HSSFCell.CELL_TYPE_STRING,
"工商机关名称");
this.createCell(row, 9, style, HSSFCell.CELL_TYPE_STRING,
"营业执照名称");
this.createCell(row, 10, style, HSSFCell.CELL_TYPE_STRING,
"营业执照字号");
this.createCell(row, 11, style, HSSFCell.CELL_TYPE_STRING,
"发照日期");
this.createCell(row, 12, style, HSSFCell.CELL_TYPE_STRING,
"工商开业日期");
this.createCell(row, 13, style, HSSFCell.CELL_TYPE_STRING,
"有效期起");
this.createCell(row, 14, style, HSSFCell.CELL_TYPE_STRING,
"有效期止");
this.createCell(row, 15, style, HSSFCell.CELL_TYPE_STRING,
"经营方式");
this.createCell(row, 16, style, HSSFCell.CELL_TYPE_STRING,
"行业");
this.createCell(row, 17, style, HSSFCell.CELL_TYPE_STRING,
"核准税务登记日期");
this.createCell(row, 18, style, HSSFCell.CELL_TYPE_STRING,
"纳税人状态");
this.createCell(row, 19, style, HSSFCell.CELL_TYPE_STRING,
"主管税务官员");
this.createCell(row, 20, style, HSSFCell.CELL_TYPE_STRING,
"街道乡镇");
if (dataList != null && dataList.size() > 0) {
// 给excel填充数据
for (int i = 0; i < dataList.size(); i++) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
TaxpayerInfo taxpayerInfo = (TaxpayerInfo)dataList.get(i);
HSSFRow row1 = sheet.createRow((short) (i + 1));// excel title
if(taxpayerInfo.getRecroadType() != null)
this.createCell(row1, 0,style, HSSFCell.CELL_TYPE_STRING, taxpayerInfo.getRecroadType());
if(taxpayerInfo.getTaxpayerId() != null)
this.createCell(row1, 1,style, HSSFCell.CELL_TYPE_STRING, taxpayerInfo.getTaxpayerId());
if(taxpayerInfo.getTaxpayerNumber() != null)
this.createCell(row1, 2,style, HSSFCell.CELL_TYPE_STRING, taxpayerInfo.getTaxpayerNumber());
if(taxpayerInfo.getTaxpayerName() != null)
this.createCell(row1, 3,style, HSSFCell.CELL_TYPE_STRING, taxpayerInfo.getTaxpayerName());
if(taxpayerInfo.getTaxpayerRegAddress() != null)
this.createCell(row1, 4,style, HSSFCell.CELL_TYPE_STRING, taxpayerInfo.getTaxpayerRegAddress());
if(taxpayerInfo.getTaxpayerDealAddress() !=null )
this.createCell(row1, 5,style, HSSFCell.CELL_TYPE_STRING, taxpayerInfo.getTaxpayerDealAddress());
if(taxpayerInfo.getTaxpayerDealScope() !=null)
this.createCell(row1, 6,style, HSSFCell.CELL_TYPE_STRING, taxpayerInfo.getTaxpayerDealAddress());
if(taxpayerInfo.getChargeTaxOrgan() != null)
this.createCell(row1, 7,style, HSSFCell.CELL_TYPE_STRING, taxpayerInfo.getChargeTaxOrgan());
if(taxpayerInfo.getBusinessOrganName() != null )
this.createCell(row1, 8,style, HSSFCell.CELL_TYPE_STRING, taxpayerInfo.getBusinessOrganName());
if(taxpayerInfo.getBusinessLicenceName() !=null)
this.createCell(row1, 9,style, HSSFCell.CELL_TYPE_STRING, taxpayerInfo.getBusinessLicenceName());
if(taxpayerInfo.getBusinessTradeName() != null )
this.createCell(row1, 10,style, HSSFCell.CELL_TYPE_STRING, taxpayerInfo.getBusinessTradeName());
if(taxpayerInfo.getIssuerDatetime() != null ){
String str1 = sdf.format(taxpayerInfo.getIssuerDatetime());
this.createCell(row1, 11,style, HSSFCell.CELL_TYPE_STRING, str1);
}else if(taxpayerInfo.getIssuerDatetime() == null ){
String str1 = "";
this.createCell(row1, 11,style, HSSFCell.CELL_TYPE_STRING, str1);
}
if(taxpayerInfo.getStartBusinessTime() !=null ){
String str2 = sdf.format(taxpayerInfo.getStartBusinessTime());
this.createCell(row1, 12,style, HSSFCell.CELL_TYPE_STRING, str2);
}else if(taxpayerInfo.getStartBusinessTime() ==null ){
String str2 = "";
this.createCell(row1, 12,style, HSSFCell.CELL_TYPE_STRING, str2);
}
if(taxpayerInfo.getValidityStart()!=null ){
String str3 = sdf.format(taxpayerInfo.getValidityStart());
this.createCell(row1, 13,style, HSSFCell.CELL_TYPE_STRING, str3);
}else if(taxpayerInfo.getValidityStart()== null){
String str3 = "";
this.createCell(row1, 13,style, HSSFCell.CELL_TYPE_STRING, str3);
}
if(taxpayerInfo.getValidityEnd() !=null ){
String str4 = sdf.format(taxpayerInfo.getValidityEnd());
this.createCell(row1, 14,style, HSSFCell.CELL_TYPE_STRING, str4);
}else if(taxpayerInfo.getValidityEnd() == null){
String str4 = "";
this.createCell(row1, 14,style, HSSFCell.CELL_TYPE_STRING, str4);
}
if(taxpayerInfo.getDealPattern() !=null){
this.createCell(row1, 15,style, HSSFCell.CELL_TYPE_STRING, taxpayerInfo.getDealPattern());
}
if(taxpayerInfo.getIndustry() !=null ){
this.createCell(row1, 16,style, HSSFCell.CELL_TYPE_STRING, taxpayerInfo.getIndustry());
}
if(taxpayerInfo.getApproveTaxRegTime() !=null){
String str5 = sdf.format(taxpayerInfo.getApproveTaxRegTime());
this.createCell(row1, 17,style, HSSFCell.CELL_TYPE_STRING, str5);
}else if(taxpayerInfo.getApproveTaxRegTime() == null){
String str5 = "";
this.createCell(row1, 17,style, HSSFCell.CELL_TYPE_STRING, str5);
}
if(taxpayerInfo.getTaxpayerState() !=null ){
this.createCell(row1, 18,style, HSSFCell.CELL_TYPE_STRING, taxpayerInfo.getTaxpayerState());
}
if(taxpayerInfo.getChargeTaxOfficer() !=null){
this.createCell(row1, 19,style, HSSFCell.CELL_TYPE_STRING, taxpayerInfo.getChargeTaxOfficer());
}
if(taxpayerInfo.getStreetVillageTowns() !=null){
this.createCell(row1, 20,style, HSSFCell.CELL_TYPE_STRING, taxpayerInfo.getStreetVillageTowns());
}
}//for
}else {
this.createCell(sheet.createRow(1), 0, style,
HSSFCell.CELL_TYPE_STRING, "纳税人资料为空");
}
} catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
private void setSheetColumnWidth(HSSFSheet sheet) {
// 根据你数据里面的记录有多少列，就设置多少列
sheet.setColumnWidth((short) 0, (short) 7000);
sheet.setColumnWidth((short) 1, (short) 5000);
sheet.setColumnWidth((short) 2, (short) 5000);
sheet.setColumnWidth((short) 3, (short) 5000);
sheet.setColumnWidth((short) 4, (short) 5000);
sheet.setColumnWidth((short) 5, (short) 9000);
sheet.setColumnWidth((short) 6, (short) 9000);
sheet.setColumnWidth((short) 7, (short) 3000);
sheet.setColumnWidth((short) 8, (short) 3000);
sheet.setColumnWidth((short) 9, (short) 5000);
sheet.setColumnWidth((short) 10, (short) 3000);
sheet.setColumnWidth((short) 11, (short) 3000);
sheet.setColumnWidth((short) 12, (short) 3000);
sheet.setColumnWidth((short) 13, (short) 3000);
sheet.setColumnWidth((short) 14, (short) 5000);
sheet.setColumnWidth((short) 15, (short) 5000);
sheet.setColumnWidth((short) 16, (short) 3000);
sheet.setColumnWidth((short) 17, (short) 5000);
sheet.setColumnWidth((short) 18, (short) 3000);
sheet.setColumnWidth((short) 19, (short) 3000);
sheet.setColumnWidth((short) 20, (short) 3000);
}
/**
* 设置excel的title样式
*/
private HSSFCellStyle createTitleStyle(HSSFWorkbook wb) {
HSSFFont boldFont = wb.createFont();
boldFont.setColor(HSSFFont.COLOR_RED);
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
boldFont.setFontHeight((short) 200);
HSSFCellStyle style = wb.createCellStyle();
style.setFont(boldFont);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
return style;
}
/**
* 创建Excel单元格
*/
private void createCell(HSSFRow row, int column, HSSFCellStyle style,
int cellType, Object value) {
HSSFCell cell = row.createCell((short) column);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
if (style != null) {
cell.setCellStyle(style);
}
switch (cellType) {
case HSSFCell.CELL_TYPE_BLANK: {
}
break;
case HSSFCell.CELL_TYPE_STRING: {
cell.setCellValue(value.toString());
}
break;
case HSSFCell.CELL_TYPE_NUMERIC: {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
// DecimalFormat format = new DecimalFormat("###,##0.00");
// cell.setCellValue(Float.parseFloat(value.toString()));
cell.setCellValue(Double.parseDouble(value.toString()));
}
break;
default:
break;
}
}
/**
* Excel中时间转换函数
*/
public String parseExcelDate(HSSFCell cell){
if(cell == null)
return "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC://String
return cell.getNumericCellValue() + "";
case HSSFCell.CELL_TYPE_STRING:
return cell.getStringCellValue();
case HSSFCell.CELL_TYPE_FORMULA://单元格类型为公式
return cell.getCellFormula();
case HSSFCell.CELL_TYPE_BLANK://单元格类型为空白
return "";
case HSSFCell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue() + "";
case HSSFCell.CELL_TYPE_ERROR:
return cell.getErrorCellValue() + "";
}
return "";
}