/**
* @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++));
}
}
