public void testConvertNumColColString() {
assertEquals("A", CellReference.convertNumToColString(0));
assertEquals("AV", CellReference.convertNumToColString(47));
assertEquals("AW", CellReference.convertNumToColString(48));
assertEquals("BF", CellReference.convertNumToColString(57));
assertEquals("", CellReference.convertNumToColString(-1));
assertEquals("", CellReference.convertNumToColString(Integer.MIN_VALUE));
assertEquals("", CellReference.convertNumToColString(Integer.MAX_VALUE));
assertEquals("FXSHRXW", CellReference.convertNumToColString(Integer.MAX_VALUE - 1));
}
/**
* 资产明细表
*
* @param response
* @param customer_2
* @param customer_0or1
* @throws IOException
*/
@RequestMapping("/report/frontequip/exportFrontEquipListReport_assetclean.do")
public void exportFrontEquipListReport_assetclean(
HttpServletResponse response, String customer_2, String customer_0or1) throws IOException {
String customer_2_name = customerService.get(customer_2).getName();
String customer_0or1_name = customerService.get(customer_0or1).getName();
List<FrontEquipListReport> list =
frontEquipReportRepository.queryFrontEquipListReport(customer_2, customer_0or1);
List<FrontEquipListReport_subtype> list_subtype_prod =
frontEquipReportRepository.queryFrontEquipListReport_header(customer_2, customer_0or1);
XSSFWorkbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet();
int rownum = 0;
// 标题
Row title = sheet.createRow(rownum++);
Cell title_cell = title.createCell(0);
title_cell.setCellValue(customer_2_name + customer_0or1_name + "前端设备明细表");
CellStyle title_style = wb.createCellStyle();
Font title_font = wb.createFont();
title_font.setFontHeightInPoints((short) 16);
// f.setColor(IndexedColors.RED.getIndex());
title_font.setBoldweight(Font.BOLDWEIGHT_BOLD);
title_style.setFont(title_font);
title_style.setAlignment(CellStyle.ALIGN_CENTER);
title_style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
title_cell.setCellStyle(title_style);
// 和并单元格
// sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) 15));
// ===========================================================================================
rownum = exportFrontEquipListReport_header_assetclean(list_subtype_prod, rownum, wb, sheet);
// =============================================================================================
// 开始构建整个excel的文件
// 处理对应的品名在excel的哪一列
Map<String, Integer> prod_col_index_map = new HashMap<String, Integer>();
int cellIndex = 3;
for (FrontEquipListReport_subtype subtype : list_subtype_prod) {
for (FrontEquipListReport_prod prod : subtype.getProds()) {
prod_col_index_map.put(prod.getProd_id(), cellIndex);
cellIndex = cellIndex + 2;
}
}
if (list != null && list.size() > 0) {
int i = 1;
for (FrontEquipListReport customer : list) {
Row row = sheet.createRow(rownum++);
Cell cell_prod_0 = row.createCell(0);
cell_prod_0.setCellValue(i);
// cell_prod_0.setCellStyle(style);
Cell cell_prod_1 = row.createCell(1);
cell_prod_1.setCellValue(customer.getPole_code());
Cell cell_prod_2 = row.createCell(2);
cell_prod_2.setCellValue(customer.getPole_name());
for (FrontEquipListReport_prod prod : customer.getProdes()) {
Cell cell_prod = row.createCell(prod_col_index_map.get(prod.getProd_id()));
cell_prod.setCellValue(prod.getNum());
// cell_prod.setCellStyle(style);
cell_prod = row.createCell(prod_col_index_map.get(prod.getProd_id()) + 1);
cell_prod.setCellValue(prod.getValue_net().doubleValue());
}
i++;
}
// 对标题行 进行单元格合并
sheet.addMergedRegion(
new CellRangeAddress(0, (short) 0, 0, (short) prod_col_index_map.size()));
}
sheet.createFreezePane(3, 3);
// 添加总计一行
CellStyle style_sum = wb.createCellStyle();
Font style_sum_font = wb.createFont();
style_sum_font.setFontHeightInPoints((short) 12);
// f.setColor(IndexedColors.RED.getIndex());
style_sum_font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style_sum.setFont(style_sum_font);
style_sum.setAlignment(CellStyle.ALIGN_RIGHT);
style_sum.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
Row row_sum = sheet.createRow(rownum++);
Cell cell_sum_2 = row_sum.createCell(2);
cell_sum_2.setCellValue("小计:");
cell_sum_2.setCellStyle(style_sum);
for (int i = 3; i < cellIndex; i++) {
String col = CellReference.convertNumToColString(i);
Cell cell_sum = row_sum.createCell(i);
// =SUM(C4:C29) 从第4行开始到最后一样
// cell_sum.setCellValue("SUM("+col+"4:"+col+(rownum-1)+")");
cell_sum.setCellFormula("SUM(" + col + "5:" + col + (rownum - 1) + ")");
cell_sum.setCellStyle(style_sum);
Cell cell_net = row_sum.createCell(i);
cell_net.setCellFormula("SUM(" + col + "5:" + col + (rownum - 1) + ")");
cell_net.setCellStyle(style_sum);
}
String filename = customer_2_name + customer_0or1_name + "前端设备明细表-净资产.xlsx";
// FileOutputStream out = new FileOutputStream(filename);
response.setHeader(
"content-disposition",
"attachment; filename=" + new String(filename.getBytes("UTF-8"), "ISO8859-1"));
// response.setContentType("application/vnd.ms-excel;charset=uft-8");
response.setContentType(
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=uft-8");
OutputStream out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}