public Resultmodel getexpenseMessagx(HttpServletResponse response, String date1, String date2) {
long startTime = System.currentTimeMillis(); Log.info("开始:费用表" + "///类.方法:" + this.getClass().getSimpleName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName() + "///开始时间:" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())); Log.info("入参date1:" + date1 + "///date2=" + date2); // 定义两个date型变量 d1为3月2日d2是2月1日 //先求出两个日期之间的天数(d2.getTime() - d1.getTime()) / (3600L * 1000 * 24) Date Date1 = null; Date Date2 = null; try { Date1 = DateTimeUtil.stringToDate(date1); Date2 = DateTimeUtil.stringToDate(date2); } catch (ParseException e) { Log.info("日期转换错误"); e.printStackTrace(); } long days = (Date2.getTime() - Date1.getTime()) / (3600L * 1000 * 24); SXSSFWorkbook wb = new SXSSFWorkbook(); String[] listTitle = getlistTitle();//标题 String[] listTitle2 = getlistTitle2();//标题 String excelName = date1 + "至" + date2 + "费用表";//Excel名
String sheet1 = "xxx"; creatSheet1(wb, sheet1, listTitle, Date1, days); try { SXSSExcelUtil.outExcel(response, wb, excelName); } catch (Exception e) { Log.error("输出异常" + e.getMessage()); return new Resultmodel(400, "返回异常"); } //结束 long endTime = System.currentTimeMillis(); //获取结束时间 Log.info("结束:费用表" + "程序运行时间：" + (endTime - startTime) + "ms"); return null;}
public void creatSheet1(SXSSFWorkbook wb, String sheetx, String[] listTitle, Date Date1, Long days) { //1.创建Excel //整体 //整体 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);
int rowSize = 0; for (int x = 0; x < days; x++) {
Date dateSubDay = DateTimeUtil.getDateSubDay(Date1, x); String date = DateTimeUtil.getyyyyMMddTime(dateSubDay); //查询数据库 List<workerMessage> workerMessageList1 = WkOrderWorkerFeeMapper.selectworkerMessageByDay(date);//数据 ArrayList<Map<Integer, String>> list = getlist(workerMessageList1); rowSize += list.size();//总行数 int y = rowSize - list.size();
// 从第二行输入 或者说输入数据的第一行 先创建再放值 for (int i = y; i < rowSize; i++) { // 几行 Map<Integer, String> map = list.get(i - y); //存在合并 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 - y); 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 - y); 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;
//无合并行 /* 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); }*/
}
} System.out.println("===============正常====================="); setSheet(sheet, listSize);}
public String[] getlistTitle() { String[] listTitle = new String[]{"工单号", "旺旺ID", "创建日期", "金额", "类型", "是否支付(1:支付;0:未支付)", "商家", "店铺", "客户姓名", "客户电话", "地址", "地址", "地址", "地址", "销售单号", "工人名称", "工人电话", "商品类名", "商品名称", "高度", "长度", "数量", "工人商品单价", "工人商品价格", "商家商品单价", "商家商品价格", "完成时间"}; return listTitle;
}
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); }}