Java tutorial
/* * Copyright (c) 2013 ????www.hrbesd.com. All rights reserved. * * HRBESD PROPRIETARY/CONFIDENTIAL. Use is subject to license terms. */ package com.esd.cs.common; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.util.CellRangeAddress; import com.esd.cs.statistics.ReportModel; import com.esd.hesf.model.Company; import com.esd.hesf.model.Worker; import com.esd.hesf.model.WorkerTemp; import com.esd.hesf.viewmodels.ReportViewModel; public class PoiCreateExcel { /** * ? * * @param FilePath * @param workerList * @return */ public static boolean createExcel(String FilePath, List<WorkerTemp> workerList) { // Excel Workbook,excel HSSFWorkbook wb = new HSSFWorkbook(); // Excelsheet,exceltab HSSFSheet sheet = wb.createSheet("sheet1"); // excel? sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 3500); // Excel? HSSFRow headRow = sheet.createRow(0); HSSFCell headell = headRow.createCell(0); // ??? headell = headRow.createCell(0); headell.setCellValue("??"); headell = headRow.createCell(1); headell.setCellValue("??"); sheet.setColumnWidth(1, 8000); headell = headRow.createCell(2); headell.setCellValue(""); sheet.setColumnWidth(2, 13000); for (int i = 1; i <= workerList.size(); i++) { WorkerTemp worker = workerList.get(i - 1); // Excel? HSSFRow row = sheet.createRow(i); HSSFCell cell = row.createCell(0); // ??? cell = row.createCell(0); cell.setCellValue(worker.getWorkerName()); cell = row.createCell(1); cell.setCellValue(worker.getWorkerHandicapCode()); cell = row.createCell(2); cell.setCellValue(worker.getRemark()); } try { FileOutputStream os = new FileOutputStream(FilePath); wb.write(os); os.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return true; } /** * ??? * * @param FilePath * @param companyList * @return */ public static boolean createComapnyExcel(String FilePath, List<Company> companyList) { // Excel Workbook,excel HSSFWorkbook wb = new HSSFWorkbook(); // Excelsheet,exceltab HSSFSheet sheet = wb.createSheet("sheet1"); // excel? sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 3500); // Excel? HSSFRow headRow = sheet.createRow(0); HSSFCell headell = headRow.createCell(0); // ??? headell = headRow.createCell(0); headell.setCellValue("?"); headell = headRow.createCell(1); headell.setCellValue("?"); headell = headRow.createCell(2); headell.setCellValue("????"); sheet.setColumnWidth(2, 12000); // headell = headRow.createCell(3); headell.setCellValue(""); headell = headRow.createCell(4); headell.setCellValue("?"); headell = headRow.createCell(5); headell.setCellValue("???"); headell = headRow.createCell(6); headell.setCellValue("??"); headell = headRow.createCell(7); headell.setCellValue(""); headell = headRow.createCell(8); headell.setCellValue("???"); sheet.setColumnWidth(8, 12000); for (int i = 1; i <= companyList.size(); i++) { Company company = companyList.get(i - 1); // Excel? HSSFRow row = sheet.createRow(i); HSSFCell cell = row.createCell(0); // ??? // ? cell = row.createCell(0); cell.setCellValue(company.getCompanyCode()); // ? cell = row.createCell(1); cell.setCellValue(company.getCompanyTaxCode()); // ??? cell = row.createCell(2); cell.setCellValue(company.getCompanyName()); // cell = row.createCell(3); cell.setCellValue(company.getCompanyLegal()); // ? cell = row.createCell(4); cell.setCellValue(company.getCompanyContactPerson()); // ??? cell = row.createCell(5); cell.setCellValue(company.getCompanyPhone()); // ?? cell = row.createCell(6); cell.setCellValue(company.getCompanyMobile()); // cell = row.createCell(7); cell.setCellValue(company.getCompanyZipCode()); // ??? cell = row.createCell(8); cell.setCellValue(company.getCompanyAddress()); } try { FileOutputStream os = new FileOutputStream(FilePath); wb.write(os); os.flush(); os.close(); companyList.clear(); companyList = null; os = null; wb = null; System.gc(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return true; } /** * * * @param FilePath * @param companyList * @return */ public static boolean createRepeaExcel(String FilePath, List<ReportViewModel> companyList, ReportModel model) { // Excel Workbook,excel HSSFWorkbook wb = new HSSFWorkbook(); // Excelsheet,exceltab HSSFSheet sheet = wb.createSheet("sheet1"); // excel? sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 3500); // Excel? HSSFRow headRow0 = sheet.createRow(0); HSSFCell headCell = headRow0.createCell(0); // ?? sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 13));// ??? headCell = headRow0.createCell(0); // headCell.setCellValue(model.getTitle()); // ? HSSFCellStyle style = wb.createCellStyle(); style.setFillBackgroundColor(HSSFColor.GREEN.index); style.setAlignment(CellStyle.ALIGN_CENTER);// style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// // HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // style.setFont(font); headCell.setCellStyle(style); // ? ?? HSSFRow RowTow = sheet.createRow(1); HSSFCell CellTow = headRow0.createCell(1); // ?? sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5));// ??? CellTow = RowTow.createCell(0); // CellTow.setCellValue(model.getCreateCompany()); // ? // ?? sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 13));// ??? CellTow = RowTow.createCell(6); HSSFCellStyle style1 = wb.createCellStyle(); style1.setFillBackgroundColor(HSSFColor.GREEN.index); style1.setAlignment(CellStyle.ALIGN_RIGHT);// ? CellTow.setCellStyle(style1); // CellTow.setCellValue(model.getCreateData()); // ? HSSFRow headRow = sheet.createRow(2); HSSFCell headell = headRow.createCell(2); // ??? headell = headRow.createCell(0); headell.setCellValue(model.getType()); headell = headRow.createCell(1); headell.setCellValue("??"); headell = headRow.createCell(2); headell.setCellValue("??"); sheet.setColumnWidth(2, 3000); // headell = headRow.createCell(3); headell.setCellValue("???"); sheet.setColumnWidth(3, 3000); // headell = headRow.createCell(4); headell.setCellValue("???"); sheet.setColumnWidth(4, 4000); // headell = headRow.createCell(5); headell.setCellValue("???"); sheet.setColumnWidth(5, 4000); // headell = headRow.createCell(6); headell.setCellValue("???"); sheet.setColumnWidth(6, 4500); // headell = headRow.createCell(7); headell.setCellValue(""); sheet.setColumnWidth(8, 4000); headell = headRow.createCell(8); headell.setCellValue(""); sheet.setColumnWidth(8, 4000); headell = headRow.createCell(9); headell.setCellValue(""); sheet.setColumnWidth(9, 4000); headell = headRow.createCell(10); headell.setCellValue("?"); sheet.setColumnWidth(10, 4000); headell = headRow.createCell(11); headell.setCellValue("???"); headell = headRow.createCell(12); headell.setCellValue("?"); headell = headRow.createCell(13); headell.setCellValue("?"); for (int i = 0; i < companyList.size(); i++) { ReportViewModel company = companyList.get(i); // Excel? HSSFRow row = sheet.createRow(i + 3); HSSFCell cell = row.createCell(i + 3); // ??? // ???? cell = row.createCell(0); cell.setCellValue(company.getReportName()); // ?? cell = row.createCell(1); cell.setCellValue(company.getUnitNum()); // ?? cell = row.createCell(2); cell.setCellValue(company.getEmpTotal()); // ??? cell = row.createCell(3); cell.setCellValue(company.getUnAudit()); // ?, ??? cell = row.createCell(4); cell.setCellValue(company.getUnReAudit()); // ?, ?? cell = row.createCell(5); cell.setCellValue(company.getAuditOk()); // ?, ?? cell = row.createCell(6); cell.setCellValue(company.getUnauditOk()); // cell = row.createCell(7); cell.setCellValue(company.getShouldTotal().toString()); // ? cell = row.createCell(8); cell.setCellValue(company.getAlreadyTotal().toString()); // cell = row.createCell(9); cell.setCellValue(company.getLessTotal().toString()); // ? cell = row.createCell(10); cell.setCellValue(company.getAmountPayable().toString()); // ??? cell = row.createCell(11); cell.setCellValue(company.getReductionAmount().toString()); // ? cell = row.createCell(12); cell.setCellValue(company.getActualAmount().toString()); // ? cell = row.createCell(13); cell.setCellValue(company.getAlreadyAmount().toString()); } // ? HSSFRow row = sheet.createRow(companyList.size() + 3); HSSFCell cell = row.createCell(companyList.size() + 3); // ??? // ???? sheet.addMergedRegion(new CellRangeAddress(companyList.size() + 3, companyList.size() + 3, 0, 13));// ??? cell = row.createCell(0); // ? HSSFCellStyle styleFoot = wb.createCellStyle(); styleFoot.setAlignment(CellStyle.ALIGN_RIGHT);// ? cell.setCellStyle(styleFoot); // cell.setCellValue(model.getCreatePeople()); try { FileOutputStream os = new FileOutputStream(FilePath); wb.write(os); os.flush(); os.close(); companyList.clear(); companyList = null; os = null; wb = null; System.gc(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return true; } }