创建万能的工具类,已备用
ExcelData.java 存放文件数据
package com.ulic.gis.dataCenter.util; import java.io.Serializable; import java.util.List; public class ExcelData implements Serializable { private static final long serialVersionUID = 6133772627258154184L; /** * 表头 */ private List<String> titles; /** * 数据 */ private List<List<Object>> rows; /** * 页签名称 */ private String name; public List<String> getTitles() { return titles; } public void setTitles(List<String> titles) { this.titles = titles; } public List<List<Object>> getRows() { return rows; } public void setRows(List<List<Object>> rows) { this.rows = rows; } public String getName() { return name; } public void setName(String name) { this.name = name; } }
ExcelConstant.java 存放文件路径
package com.ulic.gis.dataCenter.util; public class ExcelConstant { /** * 生成文件存放路径 */ public static final String FILE_PATH = "C:\Users\Administrator\Desktop\"; /** * 表格默认名称 */ public static final String FILE_NAME = "TEST.xls"; }
ExcelUtils.java 设置文件
package com.ulic.gis.dataCenter.util; import java.awt.Color; import java.io.File; import java.io.FileOutputStream; import java.io.OutputStream; import java.net.URLEncoder; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.Cell; 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.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide; public class ExcelUtils { /** * 使用浏览器选择路径下载 * @param response * @param fileName * @param data * @throws Exception */ public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception { // 告诉浏览器用什么软件可以打开此文件 response.setHeader("content-Type", "application/vnd.ms-excel"); // 下载文件的默认名称 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "utf-8")); exportExcel(data, response.getOutputStream()); } public static int generateExcel(ExcelData excelData, String path) throws Exception { File f = new File(path); FileOutputStream out = new FileOutputStream(f); return exportExcel(excelData, out); } private static int exportExcel(ExcelData data, OutputStream out) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); int rowIndex = 0; try { String sheetName = data.getName(); if (null == sheetName) { sheetName = "Sheet1"; } XSSFSheet sheet = wb.createSheet(sheetName); rowIndex = writeExcel(wb, sheet, data); wb.write(out); } catch (Exception e) { e.printStackTrace(); } finally { //此处需要关闭 wb 变量 out.close(); } return rowIndex; } /** * 表不显示字段 * @param wb * @param sheet * @param data * @return */ // private static int writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) { // int rowIndex = 0; // writeTitlesToExcel(wb, sheet, data.getTitles()); // rowIndex = writeRowsToExcel(wb, sheet, data.getRows(), rowIndex); // autoSizeColumns(sheet, data.getTitles().size() + 1); // return rowIndex; // } /** * 表显示字段 * @param wb * @param sheet * @param data * @return */ private static int writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) { int rowIndex = 0; rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles()); rowIndex = writeRowsToExcel(wb, sheet, data.getRows(), rowIndex); autoSizeColumns(sheet, data.getTitles().size() + 1); return rowIndex; } /** * 设置表头 * * @param wb * @param sheet * @param titles * @return */ private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) { int rowIndex = 0; int colIndex = 0; Font titleFont = wb.createFont(); //设置字体 titleFont.setFontName("simsun"); //设置粗体 titleFont.setBoldweight(Short.MAX_VALUE); //设置字号 titleFont.setFontHeightInPoints((short) 14); //设置颜色 titleFont.setColor(IndexedColors.BLACK.index); XSSFCellStyle titleStyle = wb.createCellStyle(); //水平居中 titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); //垂直居中 titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); //设置图案颜色 titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192))); //设置图案样式 titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); titleStyle.setFont(titleFont); setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0))); Row titleRow = sheet.createRow(rowIndex); titleRow.setHeightInPoints(25); colIndex = 0; for (String field : titles) { Cell cell = titleRow.createCell(colIndex); cell.setCellValue(field); cell.setCellStyle(titleStyle); colIndex++; } rowIndex++; return rowIndex; } /** * 设置内容 * * @param wb * @param sheet * @param rows * @param rowIndex * @return */ private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) { int colIndex; Font dataFont = wb.createFont(); dataFont.setFontName("simsun"); dataFont.setFontHeightInPoints((short) 14); dataFont.setColor(IndexedColors.BLACK.index); XSSFCellStyle dataStyle = wb.createCellStyle(); dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); dataStyle.setFont(dataFont); setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0))); for (List<Object> rowData : rows) { Row dataRow = sheet.createRow(rowIndex); dataRow.setHeightInPoints(25); colIndex = 0; for (Object cellData : rowData) { Cell cell = dataRow.createCell(colIndex); if (cellData != null) { cell.setCellValue(cellData.toString()); } else { cell.setCellValue(""); } cell.setCellStyle(dataStyle); colIndex++; } rowIndex++; } return rowIndex; } /** * 自动调整列宽 * * @param sheet * @param columnNumber */ private static void autoSizeColumns(Sheet sheet, int columnNumber) { for (int i = 0; i < columnNumber; i++) { int orgWidth = sheet.getColumnWidth(i); sheet.autoSizeColumn(i, true); int newWidth = (int) (sheet.getColumnWidth(i) + 100); if (newWidth > orgWidth) { sheet.setColumnWidth(i, newWidth); } else { sheet.setColumnWidth(i, orgWidth); } } } /** * 设置边框 * * @param style * @param border * @param color */ private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) { style.setBorderTop(border); style.setBorderLeft(border); style.setBorderRight(border); style.setBorderBottom(border); style.setBorderColor(BorderSide.TOP, color); style.setBorderColor(BorderSide.LEFT, color); style.setBorderColor(BorderSide.RIGHT, color); style.setBorderColor(BorderSide.BOTTOM, color); } }
工具到此创建完毕,开始使用
栗子:
public void downloadFile(){ List<PerformanceStatisticsVo> list = dataCenterService.performanceStatistics(conditionVo); ExcelData data = new ExcelData(); data.setName("业绩统计"); List<String> titles = new ArrayList(); titles.add("姓名"); titles.add("产品名"); titles.add("购买数量"); titles.add("保费"); data.setTitles(titles); List<List<Object>> rows = new ArrayList(); for(int i = 0, length = list.size();i<length;i++){ PerformanceStatisticsVo performanceStatisticsVo = list.get(i); List<Object> row = new ArrayList(); row.add(performanceStatisticsVo.getName()); row.add(performanceStatisticsVo.getProductName()); row.add(performanceStatisticsVo.getPolicyNum()); row.add(performanceStatisticsVo.getPolicyPrem()); rows.add(row); } data.setRows(rows); try{ ExcelUtils.exportExcel(response,"业绩统计",data); }catch (Exception e){ e.printStackTrace(); } }