//申报查询列表导出
@ResponseBody
@RequestMapping(value="/exportTaxpayerExcel")
public void exportTaxpayerExcel(HttpSession session,String name){
String userId = (String) session.getAttribute("USERID");
String fileName =new SimpleDateFormat("yyyyMMdd").format(new Date());
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("申报查询");
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow(0);
HSSFFont font = wb.createFont();
font.setFontName("宋体"); //字体
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
//
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFont(font);
//
HSSFCellStyle styleContent = wb.createCellStyle();
styleContent.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
styleContent.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//生成表头
HSSFCell cell = row.createCell(0);
cell = row.createCell(0);
cell.setCellValue("编号");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("****");
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("****");
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("****");
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue("****");
cell.setCellStyle(style);
cell = row.createCell(5);
cell.setCellValue("****");
cell.setCellStyle(style);
cell = row.createCell(6);
cell.setCellValue("****");
cell.setCellStyle(style);
cell = row.createCell(7);
cell.setCellValue("****");
cell.setCellStyle(style);
List<CustomerEntity> list = customerService.searchData(name, userId);
// 第五步,写入实体数据
for (int i = 0; i < list.size(); i++){
row = sheet.createRow(i + 1);
CustomerEntity total = list.get(i);
HSSFCell cell2 = row.createCell(0);
cell2.setCellStyle(styleContent);
cell2.setCellValue(i+1);
cell2 = row.createCell(1);
cell2.setCellStyle(styleContent);
cell2.setCellValue(total.getName());
cell2 = row.createCell(2);
cell2.setCellStyle(styleContent);
cell2.setCellValue(total.getTaxId());
cell2 = row.createCell(3);
cell2.setCellStyle(styleContent);
cell2.setCellValue(total.getPasswordST());
cell2 = row.createCell(4);
cell2.setCellStyle(styleContent);
cell2.setCellValue(total.getPasswordCA());
cell2 = row.createCell(5);
cell2.setCellStyle(styleContent);
String postionCA = total.getPositionCA();
if(postionCA == null){
postionCA = "";
}
cell2.setCellValue(postionCA);
cell2 = row.createCell(6);
cell2.setCellStyle(styleContent);
String cano = total.getDeviceNoCA();
if(cano == null){
cano = "";
}
cell2.setCellValue(cano);
cell2 = row.createCell(7);
cell2.setCellStyle(styleContent);
Integer sflsb = total.getSflsb();
if(sflsb != null && sflsb == 0){
cell2.setCellValue("*****");
}else{
cell2.setCellValue("*****");
}
}
//设置自动调整宽度
sheet.setColumnWidth(0, 3000);
sheet.setColumnWidth(1, 10000);
sheet.setColumnWidth(2, 7000);
sheet.setColumnWidth(3, 5000);
sheet.setColumnWidth(4, 5000);
sheet.setColumnWidth(5, 5000);
sheet.setColumnWidth(6, 5000);
sheet.setColumnWidth(7, 5000);
// 第六步,保存文件
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "utf-8"));
ServletOutputStream out = response.getOutputStream();
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
bos.close();
bis.close();
} catch (Exception e) {
//跳过sonarLint检测
}finally {
try {
if(bis != null)
bis.close();
if(bos != null)
bos.close();
} catch (IOException e) {
//此处注释可消除sonarLint bug
}
}
}
导出Excel表格
最新推荐文章于 2022-10-30 18:15:05 发布