package com.test.util; import com.bositone.frame.service.transaction.transactionQuery.impl.TransactionQueryServiceImpl; import com.bositone.gongcheng.util.DateUtils; import org.apache.poi.hssf.usermodel.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; import java.util.Arrays; import java.util.List; import java.util.Map; /** * Excel工具 * * @author 林圣斌 * @version 创建时间:2018/1/23 9:03 */ public class ExcelUtils { private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class); /** * Excel导出 * */ public static void excelOut(HttpServletResponse response,String name,String key,List<Map<String, String>> list, String sheetName, String headerName ){ List<String> namelist = stringToList(name); List<String> valueList = stringToList(key); //创建一个新的Excel HSSFWorkbook workBook = new HSSFWorkbook(); //创建sheet页 HSSFSheet sheet = workBook.createSheet(); //sheet页名称 workBook.setSheetName(0, sheetName); //创建header页 HSSFHeader header = sheet.getHeader(); //设置标题居中 header.setCenter(headerName); //设置第一行为Header HSSFRow row = sheet.createRow(0); HSSFCell cell0 = null; HSSFCell cell1 = null; HSSFCell cell2 = null; HSSFCell cell3 = null; HSSFCell cell4 = null; HSSFCell cell5 = null; HSSFCell cell6 = null; HSSFCell cell7 = null; HSSFCell cell8 = null; HSSFCell cell9 = null; HSSFCell cell10 = null; HSSFCell cell11 = null; HSSFCell cell12 = null; for (int i = 0; i < namelist.size(); i++) { if (i == 0){ cell0 = row.createCell(i); cell0.setCellValue( namelist.get(i)); } if (i == 1){ cell1 = row.createCell(i); cell1.setCellValue( namelist.get(i)); } if (i == 2){ cell2 = row.createCell(i); cell2.setCellValue( namelist.get(i)); } if (i == 3){ cell3 = row.createCell(i); cell3.setCellValue( namelist.get(i)); } if (i == 4){ cell4 = row.createCell(i); cell4.setCellValue( namelist.get(i)); } if (i == 5){ cell5 = row.createCell(i); cell5.setCellValue( namelist.get(i)); } if (i == 6){ cell6 = row.createCell(i); cell6.setCellValue( namelist.get(i)); } if (i == 7){ cell7 = row.createCell(i); cell7.setCellValue( namelist.get(i)); } if (i == 8){ cell8 = row.createCell(i); cell8.setCellValue( namelist.get(i)); } if (i == 9){ cell9 = row.createCell(i); cell9.setCellValue( namelist.get(i)); } if (i == 10){ cell10 = row.createCell(i); cell10.setCellValue( namelist.get(i)); } if (i == 11){ cell11 = row.createCell(i); cell11.setCellValue( namelist.get(i)); } if (i == 12){ cell12 = row.createCell(i); cell12.setCellValue( namelist.get(i)); } } if(list != null && !list.isEmpty()) { for (int j = 0; j < list.size(); j++) { Map<String, String> targetStock = list.get(j); row = sheet.createRow(j + 1); for (int i = 0; i < valueList.size(); i++) { if (i == 0) { cell0 = row.createCell(i); cell0.setCellValue(object2String(targetStock.get(object2String(valueList.get(i))))); sheet.setColumnWidth(i, 4000); } if (i == 1) { cell1 = row.createCell(i); cell1.setCellValue(object2String(targetStock.get(object2String(valueList.get(i))))); sheet.setColumnWidth(i, 4000); } if (i == 2) { cell2 = row.createCell(i); cell2.setCellValue(object2String(targetStock.get(object2String(valueList.get(i))))); sheet.setColumnWidth(i, 4000); } if (i == 3) { cell3 = row.createCell(i); cell3.setCellValue(object2String(targetStock.get(object2String(valueList.get(i))))); sheet.setColumnWidth(i, 4000); } if (i == 4) { cell4 = row.createCell(i); cell4.setCellValue(object2String(targetStock.get(object2String(valueList.get(i))))); sheet.setColumnWidth(i, 4000); } if (i == 5) { cell5 = row.createCell(i); cell5.setCellValue(object2String(targetStock.get(object2String(valueList.get(i))))); sheet.setColumnWidth(i, 4000); } if (i == 6) { cell6 = row.createCell(i); cell6.setCellValue(object2String(targetStock.get(object2String(valueList.get(i))))); sheet.setColumnWidth(i, 4000); } if (i == 7) { cell7 = row.createCell(i); cell7.setCellValue(object2String(targetStock.get(object2String(valueList.get(i))))); sheet.setColumnWidth(i, 4000); } if (i == 8) { cell8 = row.createCell(i); cell8.setCellValue(object2String(targetStock.get(object2String(valueList.get(i))))); sheet.setColumnWidth(i, 4000); } if (i == 9) { cell9 = row.createCell(i); cell9.setCellValue(object2String(targetStock.get(object2String(valueList.get(i))))); sheet.setColumnWidth(i, 4000); } if (i == 10) { cell10 = row.createCell(i); cell10.setCellValue(object2String(targetStock.get(object2String(valueList.get(i))))); sheet.setColumnWidth(i, 4000); } if (i == 11) { cell11 = row.createCell(i); cell11.setCellValue(object2String(targetStock.get(object2String(valueList.get(i))))); sheet.setColumnWidth(i, 4000); } if (i == 12) { cell12 = row.createCell(i); cell12.setCellValue(object2String(targetStock.get(object2String(valueList.get(i))))); sheet.setColumnWidth(i, 4000); } } } } //通过Response把数据以Excel格 式保存 response.reset(); response.setContentType("application/msexcel;charset=UTF-8"); try { response.addHeader("Content-Disposition", "attachment;filename=\"" + new String((headerName + DateUtils.getCurrentDateTimeAsString() +".xls").getBytes("GBK"), "ISO8859_1") + "\""); OutputStream out = response.getOutputStream(); workBook.write(out); out.flush(); out.close(); }catch (Exception e){ logger.info(e.getMessage(),e); throw new RuntimeException("你错啦"); } } public static List<String> stringToList(String strs){ String str[] = strs.split(","); return Arrays.asList(str); } public static String object2String(Object strs){ String string; if(strs == null){ string = ""; }else { string = String.valueOf(strs); } return string; } }
ExcelUtils 工具类
最新推荐文章于 2024-07-28 09:45:40 发布