/** * @param SheetName excel里面的sheet的名字 (可以任意写,一般和excelname一样) * @param response 要写的流 * @param HeadName 数据的表头 即列名 * @param data 数据 二维数组 * @return * @throws IOException */ public static OutputStream creatExcel( String SheetName, File file, List<String> HeadName, String[][] data, Integer[] ColumnWidth) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(SheetName); CellStyle s = new CellStyle(); Font f12 = new Font(); HSSFFont font12 = ExcelUtil.getHSSFFont(workbook, f12); HSSFCellStyle style = ExcelUtil.getNewStyle(workbook, s, font12); // sheet.setDefaultColumnWidth(50); sheet.setDefaultRowHeight((short) 400); sheet.setColumnWidth(0, 18 * 256); OutputStream os = new FileOutputStream(file); // 设置excel的表头 for (int i = 0; i < HeadName.size(); i++) { ExcelUtil.setCells(0, i, HeadName.get(i), sheet, style); } // 设置excel数据 for (int i = 0; i < data.length; i++) { for (int j = 0; j < data[i].length; j++) { ExcelUtil.setCells(i + 1, j, data[i][j], sheet, style); } } // 设置excel 表格的列宽 for (int i = 0; i < HeadName.size(); i++) { sheet.setColumnWidth(i, ColumnWidth[i]); } workbook.write(os); return os; }
/** * 엑셀파일을 설정하고 생성한다. * * @param model * @param wb * @param request * @param response * @throws Exception */ @Override protected void buildExcelDocument( Map<String, Object> model, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response) throws Exception { String title = "코드"; HSSFSheet sheet = wb.createSheet(title); sheet.setDefaultRowHeight(CELL_HEIGHT_BASIC); doBuild(sheet, wb, model, title); }
public static OutputStream creatExcel( String SheetName, String excelName, HttpServletResponse response, List<String> HeadName, String[][] data, List<Integer> colunmWidth) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(SheetName); CellStyle s = new CellStyle(); Font f12 = new Font(); HSSFFont font12 = ExcelUtil.getHSSFFont(workbook, f12); HSSFCellStyle style = ExcelUtil.getNewStyles(workbook, s, font12); sheet.setDefaultColumnWidth(50); sheet.setDefaultRowHeight((short) 400); response.setContentType("application/ms-excel;"); response.setHeader( "Content-Disposition", "attachment;Filename=" + ExcelUtil.toUtf8String(excelName)); sheet.setColumnWidth(0, (HeadName.size()) * 256); OutputStream os = response.getOutputStream(); // 设置excel的表头 for (int i = 0; i < HeadName.size(); i++) { ExcelUtil.setCells(0, i, HeadName.get(i), sheet, style); } // 设置excel数据 for (int i = 0; i < data.length; i++) { for (int j = 0; j < data[i].length; j++) { ExcelUtil.setCells(i + 1, j, data[i][j], sheet, style); } } // 设置excel 表格的列宽 for (int i = 0; i < HeadName.size(); i++) { sheet.setColumnWidth(i, colunmWidth.get(i)); } workbook.write(os); return os; }
@Override protected void buildExcelDocument( Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // get data model which is passed by the Spring container List<Object> errorList = (List<Object>) model.get("errorList"); List<DonVi> donViError = (List<DonVi>) errorList.get(0); List<String> statusError = (List<String>) errorList.get(1); // create a new Excel sheet HSSFSheet sheet = workbook.createSheet("Bộ phận sử dụng bị lỗi import"); // create style for header cells CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontName("Times New Roman"); font.setFontHeight((short) 260); style.setFont(font); sheet.setDefaultRowHeight((short) 400); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 12000); sheet.setColumnWidth(2, 12000); sheet.setColumnWidth(3, 4000); sheet.setColumnWidth(4, 6000); sheet.setColumnWidth(5, 5000); sheet.setDefaultColumnStyle(0, style); sheet.setDefaultColumnStyle(1, style); sheet.setDefaultColumnStyle(2, style); sheet.setDefaultColumnStyle(3, style); sheet.setDefaultColumnStyle(4, style); sheet.setDefaultColumnStyle(5, style); // create header row CellStyle style2 = workbook.createCellStyle(); Font font2 = workbook.createFont(); font2.setFontName("Times New Roman"); font2.setBoldweight(Font.BOLDWEIGHT_BOLD); font2.setFontHeight((short) 260); style2.setFont(font2); style2.setAlignment(CellStyle.ALIGN_CENTER); HSSFRow row2 = sheet.createRow(0); row2.createCell(0).setCellValue("Công ty điện lực thành phố Cần Thơ"); row2.getCell(0).setCellStyle(style); row2.createCell(1).setCellValue("Kho Công ty Điện Lực Cần Thơ"); row2.getCell(1).setCellStyle(style); // create header row HSSFRow header = sheet.createRow(1); response.setHeader("Content-Disposition", "inline; filename=" + "BophansudungError.xls"); header.createCell(0).setCellValue("Mã BPSD"); header.getCell(0).setCellStyle(style2); header.createCell(1).setCellValue("Tên BPSD"); header.getCell(1).setCellStyle(style2); header.createCell(2).setCellValue("Địa chỉ"); header.getCell(2).setCellStyle(style2); header.createCell(3).setCellValue("Email"); header.getCell(3).setCellStyle(style2); header.createCell(4).setCellValue("Số điện thoại"); header.getCell(4).setCellStyle(style2); header.createCell(5).setCellValue("Lỗi"); header.getCell(5).setCellStyle(style2); // create data rows int rowCount = 2; int i = 0; for (DonVi dv : donViError) { HSSFRow aRow = sheet.createRow(rowCount++); aRow.createCell(0).setCellValue(dv.getDvMa()); aRow.createCell(1).setCellValue(dv.getDvTen()); aRow.createCell(2).setCellValue(dv.getDiaChi()); aRow.createCell(3).setCellValue(dv.getEmail()); aRow.createCell(4).setCellValue(dv.getSdt()); aRow.createCell(5).setCellValue(statusError.get(i++)); } }