package com.numa.util;
import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletResponse;import java.io.*;import java.net.InetAddress;import java.util.List;import java.util.Map;
/** * @Company:wftdlx * @Author: wjf * @Description: 一次性插入 SXSSFWorkbook 存在溢出 * @Date: Created in 14:47 2018/12/13 */public class SXSSExcelUtil {
public static SXSSFWorkbook createWb() { SXSSFWorkbook wb = new SXSSFWorkbook(); return wb; }
public static void createSheet(SXSSFWorkbook wb, String sheetx, String[] listTitle, List<Map<Integer, String>> list) throws Exception { //整体 CellStyle cellStyle = wb.createCellStyle(); // cellStyle.setAlignment(SXSSFCellStyle.ALIGN_CENTER); // 水平居中 cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平居中 cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 垂直居中
//第一行 CellStyle titleCellStyle = wb.createCellStyle(); Font font = wb.createFont(); font.setColor(HSSFColor.GREEN.index); titleCellStyle.setFont(font); titleCellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平居中 titleCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 垂直居中
Sheet sheet = wb.createSheet(sheetx);
//冻结首行 sheet.createFreezePane(0, 1, 0, 1); //sheet.createFreezePane(1, 1, 1, 1);//
Row row0 = sheet.createRow(0);
//listSize 总列数 标题列=正文列 int listSize = listTitle.length; //输入标题栏 for (int j = 0; j < listSize; j++) {// 5列 Cell cell = row0.createCell((short) j); cell.setCellValue(listTitle[j].toString()); cell.setCellStyle(titleCellStyle); } CellRangeAddress region = new CellRangeAddress(0, 0, 10, 13); sheet.addMergedRegion(region); //rowSize总行数 不加标题 int rowSize = list.size(); // 从第二行输入 或者说输入数据的第一行 先创建再放值 for (int i = 0; i < rowSize; i++) { // 几行 总体大循环 //String[] listRow = list.get(i); //一行数据 Map<Integer, String> map = list.get(i); Row row = sheet.createRow(i + 1);//创建同一个序列号的第一行 for (int p = 0; p < map.size(); p++) { Cell cell = row.createCell(p); cell.setCellValue(map.get(p));//填充数值 包含合并列 后面在合并 cell.setCellStyle(cellStyle); } //=========================前 前17列合并 int j = 0;//相同行 String serial = map.get(0); for (int k = i + 1; k < rowSize; k++) {//查前17列重 Map<Integer, String> _map = list.get(k); String _serial = _map.get(0); if (serial.equals(_serial)) { j++; } else { break; } } if (j > 0) { for (int p = 0; p < 17; p++) { CellRangeAddress cra = new CellRangeAddress(i + 1, i + j + 1, p, p); sheet.addMergedRegion(cra);//合并 }
} //===========================中 创建中间段 17--到倒数1列 for (int k = 0; k < j; k++) { Map<Integer, String> _map1 = list.get(i + 1 + k); Row _row = sheet.createRow(i + 1 + k + 1);//创建行
for (int p = 17; p < _map1.size() - 1; p++) { Cell cell = _row.createCell(p);
cell.setCellValue(_map1.get(p)); cell.setCellStyle(cellStyle); } } //===========================后 最后一行的合并 if (j > 0) { CellRangeAddress cra = new CellRangeAddress(i + 1, i + j + 1, map.size() - 1, map.size() - 1); sheet.addMergedRegion(cra);//合并 } i += j; } setSheet(sheet, listSize); }
// 自适应宽度(中文支持) 注意输入完成输入内容后起作用 size单元格总列数 private static void setSheet(Sheet sheet, int size) { for (int columnNum = 0; columnNum < size; columnNum++) { int columnWidth = sheet.getColumnWidth(columnNum) / 256; for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) { Row currentRow; //当前行未被使用过 if (sheet.getRow(rowNum) == null) { currentRow = sheet.createRow(rowNum); } else { currentRow = sheet.getRow(rowNum); }
if (currentRow.getCell(columnNum) != null) { Cell currentCell = currentRow.getCell(columnNum); if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { int length = currentCell.getStringCellValue().getBytes().length; if (columnWidth < length) { columnWidth = length; } } } } sheet.setColumnWidth(columnNum, columnWidth * 256); } }
//主动设置单元格宽度 /* public static void setSheet(SXSSFSheet sheet) {
sheet.setColumnWidth(0, 256 * 34 + 184);//列宽 sheet.setColumnWidth(2, 256 * 19 + 184);//列宽 sheet.setColumnWidth(5, 256 * 25 + 184);//列宽 sheet.setColumnWidth(6, 256 * 19 + 184);//列宽 sheet.setColumnWidth(7, 256 * 16 + 184);//列宽 sheet.setColumnWidth(13, 256 * 40 + 184);//列宽 sheet.setColumnWidth(17, 256 * 12 + 184);//列宽 sheet.setColumnWidth(18, 256 * 18 + 184);//列宽*//* }*/
//1.输出流到客户端 public static void outExcel(HttpServletResponse response, SXSSFWorkbook wb, String excelName) throws IOException { ByteArrayOutputStream os = new ByteArrayOutputStream(); wb.write(os); byte[] content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); // 设置response参数，可以打开下载页面 response.reset(); response.setContentType("application/force-download;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename=" + new String((excelName + ".xls").getBytes(), "iso-8859-1")); ServletOutputStream out = response.getOutputStream(); BufferedInputStream bis = null; BufferedOutputStream bos = null; try { bis = new BufferedInputStream(is); bos = new BufferedOutputStream(out); byte[] buff = new byte[2048]; int bytesRead; // Simple read/write loop. while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } } catch (Exception e) { // TODO: handle exception Log.error("输出到客户端异常" + e.getMessage()); } finally { if (bis != null) bis.close(); if (bos != null) bos.close(); } }
public static String outExcel4(SXSSFWorkbook wb) throws Exception { String pathx = Url.getPath(); long l = System.currentTimeMillis(); String path = pathx + "\\files\\" + l + ".xlsx";
File file = new File(path); if (!file.exists()) {// 判断文件是否存在 if (file.createNewFile()) {// 创建目标文件 FileOutputStream fos = new FileOutputStream(file); wb.write(fos); fos.close(); } }
InetAddress localHostLANAddress = LocalIp.getLocalHostLANAddress(); String address=localHostLANAddress.getHostAddress(); String url = "http://"+address+":" + 9091 + "/files/" + l + ".xlsx";//本地运行项目 return url; }
}