中午我想了下,其实上午写的那个导出方法瓶颈在删除Excel的列,如果能提前删除,那速度就很快了,我也看了下项目的导出方法,发现是写List<List>数据,并不是我想象中的使用ResultSet,所以中午把我写的方法优化了下,导出速度还不错,现在我对我的方法很满意。
思路:
(1)先遍历数据集得到满足值全部为0的index位置,记录到nullList中
(2)删除表头中下标和nullList下标相同的数据
(3)数据集写入时候判断是否在nullList中,有则跳过。
下面直接上代码了,我就不解释了
import java.io.FileOutputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Random; import java.util.Set; import java.util.TreeSet; import org.apache.poi.POIXMLProperties; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFDataFormat; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class 删除Excel的零值 { /** * @param args */ public static void main(String[] args) throws Exception { 删除Excel的零值 t = new 删除Excel的零值(); XSSFWorkbook wb = new XSSFWorkbook(); Map<String, XSSFCellStyle> styles = t.createStyles(wb); String sheetName = "测试数据"; int startR = 2;// 第二行开始写数据 int totalCol = 1000; XSSFSheet sheet = wb.createSheet(sheetName); int[] headInfo = new int[] { 9 + totalCol * 3, 9, 3 }; System.out.println(headInfo[0] + "---=" + headInfo[1] + "---=" + headInfo[2]); long start = System.currentTimeMillis(); // 填充数据 t.createSheetBody(wb, sheet, styles, totalCol, startR, 40, headInfo); t.createExcelInfo(wb); String fileName = "f:/saveFile/temp/" + System.currentTimeMillis() + ".xlsx"; t.writeExcel(wb, fileName); System.out.println((System.currentTimeMillis() - start) / 1000 + "秒"); styles = null; wb = null; startR = 0;// 删除标题列 } private void createSheetTitle(XSSFSheet sheet, List headList, XSSFCellStyle headStyle) { createSheetTitle(sheet, 0, headList, headStyle); } /** * * @param sheet * @param styles * @param startR * 开始行 * @param totalNum * @param headinfo * 0:列总长度 1子列开始位置 2子列长度 * @throws Exception */ public void createSheetBody(XSSFWorkbook wb, XSSFSheet sheet, Map<String, XSSFCellStyle> styles, int totalCol, int startR, int totalNum, int[] headinfo) throws Exception { // 先产生所有的List数据,在挑选满足等于0的index,干掉index,在写如Excel XSSFCellStyle cell_style = styles.get("cell_center"); XSSFCellStyle long_style = styles.get("cell_long"); XSSFRow row = null; XSSFCell cell = null; //得到测试数据 List<List<Object>> tmpDataList = generateTestData(startR, totalNum, headinfo); //产生表头数据 List<String> header = generateExcelHead(totalCol); List<String> realHeader = new ArrayList<String>(); List<Integer> nullList = deleteListNullValue(tmpDataList, headinfo); // 删除表头空的数据 List<Object> dataList = null; int n = 0, col = 0; for (int i = 0, len = header.size(); i < len; i++) { if (n < nullList.size() && i == nullList.get(n)) { //注意为空的子标题数量 for (int k = 1; k < headinfo[2]; k++) { i++; } n++; } else { realHeader.add(header.get(i)); } } header = null; n = 0; // 创建表头 createExcelHeader(wb, sheet, styles, realHeader); realHeader = null; for (int i = 0; i < totalNum; i++) { n = 0; col = 0; dataList = tmpDataList.get(i); row = sheet.createRow(startR); startR++; //公告列 for (int s3 = 0, len3 = headinfo[1]; s3 < len3; s3++) { cell = row.createCell(s3); cell.setCellStyle(cell_style); cell.setCellValue(dataList.get(s3).toString()); col++; } //判断下标是否满足条件 for (int s4 = headinfo[1], len4 = headinfo[0]; s4 < len4; s4++) { //满足值为0,跳过 if (n < nullList.size() && s4 == nullList.get(n)) { for (int k = 1; k < headinfo[2]; k++) { s4++; } n++; } else { cell = row.createCell(col); cell.setCellStyle(long_style); cell.setCellValue((Integer) dataList.get(s4)); col++; } } cell = null; row = null; dataList.clear(); dataList = null; } cell = null; row = null; dataList = null; nullList = null; tmpDataList = null; } // 产生随机数据 public List<List<Object>> generateTestData(int startR, int totalNum, int[] headinfo) { int subTotal = (headinfo[0] - headinfo[1]) / headinfo[2] - 1; Random random = new Random(); // 产生随机为0的列 List<Integer> tmpList = new ArrayList<Integer>(); for (int i = 0, len = subTotal * 3 / 4; i < len; i++) { tmpList.add(random.nextInt(subTotal) % (subTotal + 1)); } // 去重 Set<Integer> nullValue = new TreeSet(tmpList); int[] nullIndex = new int[nullValue.size()]; int j = 0, n = 0; // 赋值给数组,保存为0的列的index for (Integer it : nullValue) { nullIndex[j++] = it; } System.out.println(); List<List<Object>> tmpDataList = new ArrayList<List<Object>>(); List<Object> dataList = null; random = new Random(); for (int i = 1, len = totalNum + 1; i < len; i++) { dataList = new ArrayList<Object>(); n = 0; dataList.add(i); for (int k = 1; k < headinfo[1]; k++) { dataList.add("测试" + (i)); } j++; for (int k = 0; k < subTotal; k++) { if (n < nullIndex.length && k == nullIndex[n]) { for (int l = 0; l < headinfo[2]; l++) { dataList.add(0); } n++; } else { for (int l = 0; l < headinfo[2]; l++) { dataList.add(random.nextInt(8000)); } } } for (int l = 0; l < headinfo[2]; l++) { dataList.add(random.nextInt(20000)); } tmpDataList.add(dataList); dataList = null; } return tmpDataList; } public List<Integer> deleteListNullValue(List<List<Object>> tmpValueList, int[] headinfo) { List<Integer> tmpNullIndexList = new ArrayList<Integer>(); List<Integer> nullIndexList = null; int startIndex = headinfo[1]; int subLen = headinfo[2]; int beforeIndex = 0; List<Object> list = null; boolean isFirst = true; for (int j = 0, len2 = tmpValueList.size(); j < len2; j++) { list = tmpValueList.get(j); nullIndexList = new ArrayList<Integer>(); isFirst = true; for (int i = startIndex, len = list.size(); i < len; i++) { if (isFirst && "0".equals(list.get(i).toString())) { beforeIndex = i; isFirst = false; } else if (!"0".equals(list.get(i).toString())) { beforeIndex = i; isFirst = true; } else if (!isFirst && (i - beforeIndex + 1) == subLen) { nullIndexList.add(beforeIndex); isFirst = true; } } if (j == 0) { tmpNullIndexList = new ArrayList(nullIndexList); } else { tmpNullIndexList.retainAll(nullIndexList); tmpNullIndexList = new ArrayList(tmpNullIndexList); } nullIndexList.clear(); nullIndexList = null; } for (Integer it : tmpNullIndexList) { System.out.print(it + ","); } System.out.println(); list = null; nullIndexList = null; return tmpNullIndexList; } public void createExcelInfo(XSSFWorkbook workbook) { POIXMLProperties.CoreProperties coreProp = workbook.getProperties() .getCoreProperties(); coreProp.setCreator("测试作者属性"); coreProp.setCategory("测试类别属性"); coreProp.setTitle("Excel标题属性"); coreProp.setSubjectProperty("测试主题属性"); coreProp.setKeywords("报表测试,POI测试"); } /** * 主要思路:优先把第一行跨多列的数据先合并,再合并第2行子数据,暂只支持2行N列 如果要修改该功能,建议重写一个更简单,如果看不懂我写的 * * @param sheet * @startR 从第几行开始(下标从0开始) * @param headList * @param headStyle */ private void createSheetTitle(XSSFSheet sheet, int startR, List headList, XSSFCellStyle headStyle) { XSSFRow row_head_0 = null; XSSFRow row_head_1 = null; XSSFCell cell = null; // 保存未拆分时的值 String attr = null; // 保存第一次拆分时的值 String[] tmp = null; // 拆分后第一行的值 String[] tmp2 = null; // 拆分后第2行的值 String[] tmp3 = null; // 第一次匹配时的值 String preAttr = null; // 下一次匹配时的值 String lastAttr = null; int preIdx = 0; int start_row = startR;// 起始行 int start_col = 0;// 起始列 row_head_0 = sheet.createRow(start_row + 0); row_head_1 = sheet.createRow(start_row + 1); CellRangeAddress range = new CellRangeAddress(0, 0, 0, 0); for (int i = 0; i < headList.size(); i++) { attr = String.valueOf(headList.get(i)); // 以$分割数据 tmp = attr.split("\\$"); if (tmp.length == 1) { cell = row_head_0.createCell(start_col + i); // #分割的是行 列信息 cell.setCellValue(tmp[0].split("\\#")[0]); cell.setCellStyle(headStyle); cell = row_head_1.createCell(start_col + i); cell.setCellStyle(headStyle); // 创建cell主要是指定样式,防止跨列时少样式 for (int k = start_col + i + 1; k <= start_col + i; k++) { row_head_0.createCell(k).setCellStyle(headStyle); row_head_1.createCell(k).setCellStyle(headStyle); } range.setFirstRow(start_row + 0); range.setLastRow(start_row + 1); range.setFirstColumn(start_col + i); range.setLastColumn(start_col + i); sheet.addMergedRegion(range); if ("xsd".equalsIgnoreCase(tmp[0].split("\\#")[0])) { sheet.setColumnWidth(i, 50 * 256); // 256基本单位 } else if (tmp[0].split("\\#")[0].getBytes().length > 16) { sheet.setColumnWidth(i, 25 * 256); // 256基本单位 } preIdx = i + 1; } else if (tmp.length == 2) { preAttr = tmp[0]; tmp2 = tmp[0].split("\\#"); tmp3 = tmp[1].split("\\#"); int tmpI = Integer.parseInt(tmp2[2]); int tmpI2 = Integer.parseInt(tmp3[2]); if (!preAttr.equals(lastAttr)) { lastAttr = tmp[0]; cell = row_head_0.createCell(start_col + i); cell.setCellValue(tmp2[0]); cell.setCellStyle(headStyle); // 创建cell主要是指定样式,防止跨列时少样式 for (int k = start_col + preIdx + 1; k <= start_col + preIdx + tmpI - 1; k++) { row_head_0.createCell(k).setCellStyle(headStyle); } range.setFirstRow(start_row + 0); range.setLastRow(start_row + 0); range.setFirstColumn(start_col + preIdx); range.setLastColumn(start_col + preIdx + tmpI - 1); sheet.addMergedRegion(range); } cell = row_head_1.createCell(start_col + i); cell.setCellValue(tmp3[0]); cell.setCellStyle(headStyle); // 第二行跨列 if (tmpI2 != 1) { // 创建cell主要是指定样式,防止跨列时少样式 for (int k = start_col + preIdx + 1; k <= start_col + preIdx + tmpI2 - 1; k++) { row_head_1.createCell(k).setCellStyle(headStyle); } range.setFirstRow(start_row + 1); range.setLastRow(start_row + 1); range.setFirstColumn(start_col + preIdx); range.setLastColumn(start_col + preIdx + tmpI2 - 1); sheet.getRow(start_row + 1) .createCell(start_col + preIdx + tmpI2 - 1) .setCellStyle(headStyle); sheet.addMergedRegion(range); start_col = start_col + tmpI2 - 1; } preIdx++; } } } // 创建样式库 private Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) { Map<String, XSSFCellStyle> stylesMap = new HashMap<String, XSSFCellStyle>(); XSSFCellStyle style = null; XSSFFont sheetFont = wb.createFont(); sheetFont.setColor(IndexedColors.WHITE.getIndex()); sheetFont.setFontName("楷体"); sheetFont.setFontHeightInPoints((short) 10); sheetFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setFont(sheetFont); style.setFillForegroundColor(IndexedColors.AQUA.getIndex()); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); style.setBorderTop(XSSFCellStyle.BORDER_DOTTED); style.setTopBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderLeft(XSSFCellStyle.BORDER_DOTTED); style.setLeftBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderRight(XSSFCellStyle.BORDER_DOTTED); style.setRightBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderBottom(XSSFCellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.WHITE.getIndex()); stylesMap.put("sheet_title", style); sheetFont = wb.createFont(); sheetFont.setColor(IndexedColors.WHITE.getIndex()); sheetFont.setFontName("楷体"); sheetFont.setFontHeightInPoints((short) 10); sheetFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setFont(sheetFont); style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex()); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); style.setBorderTop(XSSFCellStyle.BORDER_DOTTED); style.setTopBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderLeft(XSSFCellStyle.BORDER_DOTTED); style.setLeftBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderRight(XSSFCellStyle.BORDER_DOTTED); style.setRightBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderBottom(XSSFCellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.WHITE.getIndex()); stylesMap.put("sheet_title_style", style); style = wb.createCellStyle(); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); stylesMap.put("cell_center", style); style = wb.createCellStyle(); XSSFDataFormat format = wb.createDataFormat(); style.setDataFormat(format.getFormat("0")); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); stylesMap.put("cell_long", style); return stylesMap; } public void writeExcel(XSSFWorkbook wb, String fileName) { if (wb != null) { try { FileOutputStream fileOutStream = new FileOutputStream(fileName); wb.write(fileOutStream); if (fileOutStream != null) { fileOutStream.close(); } } catch (Exception e) { e.printStackTrace(); } } } public List<String> generateExcelHead(int subTotal) { List<String> header = new ArrayList<String>(); header = new ArrayList<String>(); // 表头_行数_列数$ header.add("编号#2#1$"); header.add("MSN#2#1$"); header.add("一级标题#2#1$"); header.add("二级标题#2#1$"); header.add("品牌名#2#1$"); header.add("型号#2#1$"); header.add("颜色#2#1$"); header.add("性质#2#1$"); header.add("状态#2#1$"); for (int i = 1; i < subTotal; i++) { header.add("下游合作商_" + i + "#1#3$数量#1#1$"); header.add("下游合作商_" + i + "#1#3$金额#1#1$"); header.add("下游合作商_" + i + "#1#3$小计#1#1$"); } header.add("总数量#2#1$"); header.add("总金额#2#1$"); header.add("总计#2#1$"); return header; } public void createExcelHeader(XSSFWorkbook wb, XSSFSheet sheet, Map<String, XSSFCellStyle> styles, List<String> header) throws Exception { XSSFCellStyle titleStyle = null; titleStyle = styles.get("sheet_title"); // 为某一位sheet填充数据 createSheetTitle(sheet, header, titleStyle); titleStyle = null; header = null; } }
结果为:
也可以使用SXSSFWorkbook,导出100行,1000列速度还不错。
import java.io.FileOutputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Random; import java.util.Set; import java.util.TreeSet; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFWorkbook; public class 删除Excel零值优化版 { /** * @param args */ public static void main(String[] args) throws Exception { 删除Excel零值优化版 t = new 删除Excel零值优化版(); Workbook wb = new SXSSFWorkbook(200); Map<String, CellStyle> styles = t.createStyles(wb); String sheetName = "测试数据"; int startR = 2;// 第二行开始写数据 int totalCol = 1000; Sheet sheet = wb.createSheet(sheetName); int[] headInfo = new int[] { 9 + totalCol * 3, 9, 3 }; System.out.println(headInfo[0] + "---=" + headInfo[1] + "---=" + headInfo[2]); long start = System.currentTimeMillis(); // 填充数据 t.createSheetBody(wb, sheet, styles, totalCol, startR, 100, headInfo); String fileName = "f:/saveFile/temp/" + System.currentTimeMillis() + ".xlsx"; t.writeExcel(wb, fileName); System.out.println((System.currentTimeMillis() - start) / 1000 + "秒"); styles = null; wb = null; startR = 0;// 删除标题列 } private void createSheetTitle(Sheet sheet, List headList, CellStyle headStyle) { createSheetTitle(sheet, 0, headList, headStyle); } /** * * @param sheet * @param styles * @param startR * 开始行 * @param totalNum * @param headinfo * 0:列总长度 1子列开始位置 2子列长度 * @throws Exception */ public void createSheetBody(Workbook wb, Sheet sheet, Map<String, CellStyle> styles, int totalCol, int startR, int totalNum, int[] headinfo) throws Exception { // 先产生所有的List数据,在挑选满足等于0的index,干掉index,在写如Excel CellStyle cell_style = styles.get("cell_center"); CellStyle long_style = styles.get("cell_long"); Row row = null; Cell cell = null; // 得到测试数据 List<List<Object>> tmpDataList = generateTestData(startR, totalNum, headinfo); // 产生表头数据 List<String> header = generateExcelHead(totalCol); List<String> realHeader = new ArrayList<String>(); List<Integer> nullList = deleteListNullValue(tmpDataList, headinfo); // 删除表头空的数据 List<Object> dataList = null; int n = 0, col = 0; for (int i = 0, len = header.size(); i < len; i++) { if (n < nullList.size() && i == nullList.get(n)) { // 注意为空的子标题数量 for (int k = 1; k < headinfo[2]; k++) { i++; } n++; } else { realHeader.add(header.get(i)); } } header = null; n = 0; // 创建表头 createExcelHeader(wb, sheet, styles, realHeader); realHeader = null; for (int i = 0; i < totalNum; i++) { n = 0; col = 0; dataList = tmpDataList.get(i); row = sheet.createRow(startR); startR++; // 公告列 for (int s3 = 0, len3 = headinfo[1]; s3 < len3; s3++) { cell = row.createCell(s3); cell.setCellStyle(cell_style); cell.setCellValue(dataList.get(s3).toString()); col++; } // 判断下标是否满足条件 for (int s4 = headinfo[1], len4 = headinfo[0]; s4 < len4; s4++) { // 满足值为0,跳过 if (n < nullList.size() && s4 == nullList.get(n)) { for (int k = 1; k < headinfo[2]; k++) { s4++; } n++; } else { cell = row.createCell(col); cell.setCellStyle(long_style); cell.setCellValue((Integer) dataList.get(s4)); col++; } } dataList.clear(); dataList = null; } cell = null; row = null; dataList = null; nullList = null; tmpDataList = null; } // 产生随机数据 public List<List<Object>> generateTestData(int startR, int totalNum, int[] headinfo) { int subTotal = (headinfo[0] - headinfo[1]) / headinfo[2] - 1; Random random = new Random(); // 产生随机为0的列 List<Integer> tmpList = new ArrayList<Integer>(); for (int i = 0, len = subTotal * 3 / 4; i < len; i++) { tmpList.add(random.nextInt(subTotal) % (subTotal + 1)); } // 去重 Set<Integer> nullValue = new TreeSet(tmpList); int[] nullIndex = new int[nullValue.size()]; int j = 0, n = 0; // 赋值给数组,保存为0的列的index for (Integer it : nullValue) { nullIndex[j++] = it; } System.out.println(); List<List<Object>> tmpDataList = new ArrayList<List<Object>>(); List<Object> dataList = null; random = new Random(); for (int i = 1, len = totalNum + 1; i < len; i++) { dataList = new ArrayList<Object>(); n = 0; dataList.add(i); for (int k = 1; k < headinfo[1]; k++) { dataList.add("测试" + (i)); } j++; for (int k = 0; k < subTotal; k++) { if (n < nullIndex.length && k == nullIndex[n]) { for (int l = 0; l < headinfo[2]; l++) { dataList.add(0); } n++; } else { for (int l = 0; l < headinfo[2]; l++) { dataList.add(random.nextInt(8000)); } } } for (int l = 0; l < headinfo[2]; l++) { dataList.add(random.nextInt(20000)); } tmpDataList.add(dataList); dataList = null; } return tmpDataList; } public List<Integer> deleteListNullValue(List<List<Object>> tmpValueList, int[] headinfo) { List<Integer> tmpNullIndexList = new ArrayList<Integer>(); List<Integer> nullIndexList = null; int startIndex = headinfo[1]; int subLen = headinfo[2]; int beforeIndex = 0; List<Object> list = null; boolean isFirst = true; for (int j = 0, len2 = tmpValueList.size(); j < len2; j++) { list = tmpValueList.get(j); nullIndexList = new ArrayList<Integer>(); isFirst = true; for (int i = startIndex, len = list.size(); i < len; i++) { if (isFirst && "0".equals(list.get(i).toString())) { beforeIndex = i; isFirst = false; } else if (!"0".equals(list.get(i).toString())) { beforeIndex = i; isFirst = true; } else if (!isFirst && (i - beforeIndex + 1) == subLen) { nullIndexList.add(beforeIndex); isFirst = true; } } if (j == 0) { tmpNullIndexList = new ArrayList(nullIndexList); } else { tmpNullIndexList.retainAll(nullIndexList); tmpNullIndexList = new ArrayList(tmpNullIndexList); } nullIndexList.clear(); nullIndexList = null; } for (Integer it : tmpNullIndexList) { System.out.print(it + ","); } System.out.println(); list = null; nullIndexList = null; return tmpNullIndexList; } /** * 主要思路:优先把第一行跨多列的数据先合并,再合并第2行子数据,暂只支持2行N列 如果要修改该功能,建议重写一个更简单,如果看不懂我写的 * * @param sheet * @startR 从第几行开始(下标从0开始) * @param headList * @param headStyle */ private void createSheetTitle(Sheet sheet, int startR, List headList, CellStyle headStyle) { Row row_head_0 = null; Row row_head_1 = null; Cell cell = null; // 保存未拆分时的值 String attr = null; // 保存第一次拆分时的值 String[] tmp = null; // 拆分后第一行的值 String[] tmp2 = null; // 拆分后第2行的值 String[] tmp3 = null; // 第一次匹配时的值 String preAttr = null; // 下一次匹配时的值 String lastAttr = null; int preIdx = 0; int start_row = startR;// 起始行 int start_col = 0;// 起始列 row_head_0 = sheet.createRow(start_row + 0); row_head_1 = sheet.createRow(start_row + 1); CellRangeAddress range = new CellRangeAddress(0, 0, 0, 0); for (int i = 0; i < headList.size(); i++) { attr = String.valueOf(headList.get(i)); // 以$分割数据 tmp = attr.split("\\$"); if (tmp.length == 1) { cell = row_head_0.createCell(start_col + i); // #分割的是行 列信息 cell.setCellValue(tmp[0].split("\\#")[0]); cell.setCellStyle(headStyle); cell = row_head_1.createCell(start_col + i); cell.setCellStyle(headStyle); // 创建cell主要是指定样式,防止跨列时少样式 for (int k = start_col + i + 1; k <= start_col + i; k++) { row_head_0.createCell(k).setCellStyle(headStyle); row_head_1.createCell(k).setCellStyle(headStyle); } range.setFirstRow(start_row + 0); range.setLastRow(start_row + 1); range.setFirstColumn(start_col + i); range.setLastColumn(start_col + i); sheet.addMergedRegion(range); if ("xsd".equalsIgnoreCase(tmp[0].split("\\#")[0])) { sheet.setColumnWidth(i, 50 * 256); // 256基本单位 } else if (tmp[0].split("\\#")[0].getBytes().length > 16) { sheet.setColumnWidth(i, 25 * 256); // 256基本单位 } preIdx = i + 1; } else if (tmp.length == 2) { preAttr = tmp[0]; tmp2 = tmp[0].split("\\#"); tmp3 = tmp[1].split("\\#"); int tmpI = Integer.parseInt(tmp2[2]); int tmpI2 = Integer.parseInt(tmp3[2]); if (!preAttr.equals(lastAttr)) { lastAttr = tmp[0]; cell = row_head_0.createCell(start_col + i); cell.setCellValue(tmp2[0]); cell.setCellStyle(headStyle); // 创建cell主要是指定样式,防止跨列时少样式 for (int k = start_col + preIdx + 1; k <= start_col + preIdx + tmpI - 1; k++) { row_head_0.createCell(k).setCellStyle(headStyle); } range.setFirstRow(start_row + 0); range.setLastRow(start_row + 0); range.setFirstColumn(start_col + preIdx); range.setLastColumn(start_col + preIdx + tmpI - 1); sheet.addMergedRegion(range); } cell = row_head_1.createCell(start_col + i); cell.setCellValue(tmp3[0]); cell.setCellStyle(headStyle); // 第二行跨列 if (tmpI2 != 1) { // 创建cell主要是指定样式,防止跨列时少样式 for (int k = start_col + preIdx + 1; k <= start_col + preIdx + tmpI2 - 1; k++) { row_head_1.createCell(k).setCellStyle(headStyle); } range.setFirstRow(start_row + 1); range.setLastRow(start_row + 1); range.setFirstColumn(start_col + preIdx); range.setLastColumn(start_col + preIdx + tmpI2 - 1); sheet.getRow(start_row + 1) .createCell(start_col + preIdx + tmpI2 - 1) .setCellStyle(headStyle); sheet.addMergedRegion(range); start_col = start_col + tmpI2 - 1; } preIdx++; } } } // 创建样式库 private Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> stylesMap = new HashMap<String, CellStyle>(); CellStyle style = null; Font sheetFont = wb.createFont(); sheetFont.setColor(IndexedColors.WHITE.getIndex()); sheetFont.setFontName("楷体"); sheetFont.setFontHeightInPoints((short) 10); sheetFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setFont(sheetFont); style.setFillForegroundColor(IndexedColors.AQUA.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setBorderTop(CellStyle.BORDER_DOTTED); style.setTopBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderLeft(CellStyle.BORDER_DOTTED); style.setLeftBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderRight(CellStyle.BORDER_DOTTED); style.setRightBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.WHITE.getIndex()); stylesMap.put("sheet_title", style); sheetFont = wb.createFont(); sheetFont.setColor(IndexedColors.WHITE.getIndex()); sheetFont.setFontName("楷体"); sheetFont.setFontHeightInPoints((short) 10); sheetFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setFont(sheetFont); style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setBorderTop(CellStyle.BORDER_DOTTED); style.setTopBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderLeft(CellStyle.BORDER_DOTTED); style.setLeftBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderRight(CellStyle.BORDER_DOTTED); style.setRightBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.WHITE.getIndex()); stylesMap.put("sheet_title_style", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); stylesMap.put("cell_center", style); style = wb.createCellStyle(); DataFormat format = wb.createDataFormat(); style.setDataFormat(format.getFormat("0")); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); stylesMap.put("cell_long", style); return stylesMap; } public void writeExcel(Workbook wb, String fileName) { if (wb != null) { try { FileOutputStream fileOutStream = new FileOutputStream(fileName); wb.write(fileOutStream); if (fileOutStream != null) { fileOutStream.close(); } } catch (Exception e) { e.printStackTrace(); } } } public List<String> generateExcelHead(int subTotal) { List<String> header = new ArrayList<String>(); header = new ArrayList<String>(); // 表头_行数_列数$ header.add("编号#2#1$"); header.add("MSN#2#1$"); header.add("一级标题#2#1$"); header.add("二级标题#2#1$"); header.add("品牌名#2#1$"); header.add("型号#2#1$"); header.add("颜色#2#1$"); header.add("性质#2#1$"); header.add("状态#2#1$"); for (int i = 1; i < subTotal; i++) { header.add("下游合作商_" + i + "#1#3$数量#1#1$"); header.add("下游合作商_" + i + "#1#3$金额#1#1$"); header.add("下游合作商_" + i + "#1#3$小计#1#1$"); } header.add("总数量#2#1$"); header.add("总金额#2#1$"); header.add("总计#2#1$"); return header; } public void createExcelHeader(Workbook wb, Sheet sheet, Map<String, CellStyle> styles, List<String> header) throws Exception { CellStyle titleStyle = null; titleStyle = styles.get("sheet_title"); // 为某一位sheet填充数据 createSheetTitle(sheet, header, titleStyle); titleStyle = null; header = null; } }
待解决问题:
(1)多个Excel合并为一个Excel
本文系原创,转载请注明出处,谢谢。
全文完。
相关推荐
可以运行的POI导出Excel文件实例,里面有两种方法,一个是Servlet,一个是main
使用apach poi 导出Excel,可直接在项目中使用使用apach poi 导出Excel,可直接在项目中使用使用apach poi 导出Excel,可直接在项目中使用
Poi导出Excel工具类 支持大数据量 多sheet页方式 已经封装好,直接拿来调用里面的方法传入参数就行 很简单
java使用POI导出 Excel+图片工具类 ,里面含有poi jar包,只调用接口即可直接保存Excel。使用的时候需先把数据封装,具体包装需根据实际导出数据进行处理。文件demo中只提供包装格式。
poi导出excel demo,poi导出excel demo,poi导出excel demo,poi导出excel demo,poi导出excel demo,
poi作为导出excel常用的工具,方便快捷。对于excel指定下拉列表的列,如何生成呢?本文提供如何生成下拉列表的excel列
一个POI导出Excel万级数据分页实现 解决内存溢出问题 完整的 project demo 有数据库dmp文件
POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI...
该实例集成maven开发环境,里面讲解了POI导入和导出Excel表格的基本用法
POI导出Excel工具类,自动设置标题 列名 文件名,可插入图片,合并单元格
struts2中利用poi导出excel的简单例子
poi 导出EXCEL 例子 poi 导出EXCEL 例子poi 导出EXCEL 例子poi 导出EXCEL 例子
poi导出excel文档
这个是java用poi操作Excel进行导出,并且可以自动换行
springboot+poi导出指定格式Excel模板,完整项目,导出即用。springboot+poi导出指定格式Excel模板,完整项目,导出即用。springboot+poi导出指定格式Excel模板,完整项目,导出即用。springboot+poi导出指定格式...
JAVA POI导出EXCEL代码
poi导出excel需要的jar
POI使用excel模板循环输出行到文件并导出的一个小例子
Java Poi 导出excel(支持各种设置字体、颜色、垂直居中)
Java实现POI导出Excel 博文参考:http://blog.csdn.net/itmyhome1990/article/details/49818045