導(dǎo)出后的樣式
使用方法（Strust2中的一個(gè)方法）
public String daoChu() throws Exception { // 獲取數(shù)據(jù)列表 List<CustCustomer> custCustomerList = getList(); ByteArrayOutputStream baos = new ByteArrayOutputStream(); // 表頭行 List<String[]> headNames = new ArrayList<String[]>(); headNames.add(new String[] { "客戶登錄名", "客戶名稱", "聯(lián)系人", "聯(lián)系人電話", "授權(quán)碼總數(shù)", "授權(quán)碼余額", "購(gòu)買(mǎi)時(shí)間", "是否有效" }); // 導(dǎo)出字段 List<String[]> fieldNames = new ArrayList<String[]>(); fieldNames.add(new String[] { "custNum", "custName", "custAttnName", "custAttnTel", "custAuthcodeNum", "shengYuShouQuanShu", "custBuyTimeString", "isValid" }); ExportSetInfo setInfo = new ExportSetInfo(); // （有很多Sheet表）每個(gè)Sheet表的名稱（key）和要導(dǎo)出的數(shù)據(jù)集合（value（對(duì)象的集合）） LinkedHashMap<String, List> userMap = new LinkedHashMap<String, List>(); userMap.put("客戶信息", custCustomerList); setInfo.setObjsMap(userMap); setInfo.setFieldNames(fieldNames); setInfo.setHeadNames(headNames); setInfo.setTitles(new String[] { "客戶信息" }); setInfo.setOut(baos); // 將需要導(dǎo)出的數(shù)據(jù)輸出到baos ExcelUtil.export2Excel(setInfo); SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd"); String dateStr = sdf.format(new Date()); fileName = new String(("客戶信息" + dateStr + ".xls").getBytes("GBK"), "ISO-8859-1"); excelStream = new ByteArrayInputStream(baos.toByteArray()); return "EXP_EXCEL"; }工具類
import java.io.OutputStream;import java.text.SimpleDateFormat;import java.util.Date;import java.util.LinkedHashMap;import java.util.List;import java.util.Map.Entry;import java.util.Set;import org.apache.poi.hssf.usermodel.HSSFCell;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.CellRangeAddress;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;@Sup字體 private static CellStyle dateStyle; // 日期行樣式 private static Font dateFont; // 日期行字體 private static CellStyle headStyle; // 表頭行樣式 private static Font headFont; // 表頭行字體 private static CellStyle contentStyle ; // 內(nèi)容行樣式 private static Font contentFont; // 內(nèi)容行字體 /** * @Description: 將Map里的集合對(duì)象數(shù)據(jù)輸出Excel數(shù)據(jù)流 */ public static void export2Excel(ExportSetInfo setInfo) throws Exception{ init(); //每個(gè)Sheet表的名稱和數(shù)據(jù)集合的集合 Set<Entry<String, List>> set = setInfo.getObjsMap().entrySet(); //處理Sheet表的名稱（集合） String[] sheetNames = new String[setInfo.getObjsMap().size()]; int sheetNameNum = 0; for (Entry<String, List> entry : set){ sheetNames[sheetNameNum] = entry.getKey(); sheetNameNum++; } //使用處理好的Sheet表的名稱創(chuàng)建出所有的Sheet表 HSSFSheet[] sheets = getSheets(setInfo.getObjsMap().size(), sheetNames); int sheetNum = 0; for (Entry<String, List> entry : set){ // Sheet（一張Sheet表的所有數(shù)據(jù)） List objs = entry.getValue(); // 標(biāo)題行 createTableTitleRow(setInfo, sheets, sheetNum); // 日期行 createTableDateRow(setInfo, sheets, sheetNum); // 表頭 creatTableHeadRow(setInfo, sheets, sheetNum); // 表體 String[] fieldNames = setInfo.getFieldNames().get(sheetNum); int rowNum = 3; for (Object obj : objs){ HSSFRow contentRow = sheets[sheetNum].createRow(rowNum); contentRow.setHeight((short) 300); HSSFCell[] cells = getCells(contentRow, setInfo.getFieldNames().get(sheetNum).length); int cellNum = 1; // 去掉一列序號(hào)，因此從1開(kāi)始 if(fieldNames != null){ for (int num = 0; num < fieldNames.length; num++){ Object value = ReflectionUtils.invokeGetterMethod(obj, fieldNames[num]); cells[cellNum].setCellValue(value == null ? "" : value.toString()); cellNum++; } } rowNum++; } adjustColumnSize(sheets, sheetNum, fieldNames); // 自動(dòng)調(diào)整列寬 sheetNum++; } wb.write(setInfo.getOut()); } /** * @Description: 初始化標(biāo)題、列名稱、表格內(nèi)容等的字體、樣式 */ private static void init(){ wb = new HSSFWorkbook(); titleFont = wb.createFont(); titleStyle = wb.createCellStyle(); dateFont = wb.createFont(); dateStyle = wb.createCellStyle(); headFont = wb.createFont(); headStyle = wb.createCellStyle(); contentFont = wb.createFont(); contentStyle = wb.createCellStyle(); initTitleCellStyle(); initTitleFont(); initDateCellStyle(); initDateFont(); initHeadCellStyle(); initHeadFont(); initContentCellStyle(); initContentFont(); } /** * @Description: 自動(dòng)調(diào)整列寬 */ private static void adjustColumnSize(HSSFSheet[] sheets, int sheetNum, String[] fieldNames) { for(int i = 0; i < fieldNames.length + 1; i++){ sheets[sheetNum].autoSizeColumn(i, true); } } /** * @Description: 創(chuàng)建標(biāo)題行(需合并單元格) */ private static void createTableTitleRow(ExportSetInfo setInfo, HSSFSheet[] sheets, int sheetNum){ CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, setInfo.getFieldNames().get(sheetNum).length); sheets[sheetNum].addMergedRegion(titleRange); HSSFRow titleRow = sheets[sheetNum].createRow(0); titleRow.setHeight((short) 800); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellStyle(titleStyle); titleCell.setCellValue(setInfo.getTitles()[sheetNum]); } /** * @Description: 創(chuàng)建日期行(需合并單元格) */ private static void createTableDateRow(ExportSetInfo setInfo, HSSFSheet[] sheets, int sheetNum){ CellRangeAddress dateRange = new CellRangeAddress(1, 1, 0, setInfo.getFieldNames().get(sheetNum).length); sheets[sheetNum].addMergedRegion(dateRange); HSSFRow dateRow = sheets[sheetNum].createRow(1); dateRow.setHeight((short) 350); HSSFCell dateCell = dateRow.createCell(0); dateCell.setCellStyle(dateStyle); dateCell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(new Date())); } /** * @Description: 創(chuàng)建表頭行(需合并單元格) */ private static void creatTableHeadRow(ExportSetInfo setInfo, HSSFSheet[] sheets, int sheetNum){ // 表頭 HSSFRow headRow = sheets[sheetNum].createRow(2); headRow.setHeight((short) 350); // 序號(hào)列 HSSFCell snCell = headRow.createCell(0); snCell.setCellStyle(headStyle); snCell.setCellValue("序號(hào)"); // 列頭名稱 for(int num = 1, len = setInfo.getHeadNames().get(sheetNum).length; num <= len; num++){ HSSFCell headCell = headRow.createCell(num); headCell.setCellStyle(headStyle); headCell.setCellValue(setInfo.getHeadNames().get(sheetNum)[num - 1]); } } /** * @Description: 創(chuàng)建所有的Sheet */ private static HSSFSheet[] getSheets(int num, String[] names){ HSSFSheet[] sheets = new HSSFSheet[num]; for (int i = 0; i < num; i++){ sheets[i] = wb.createSheet(names[i]); } return sheets; } /** * @Description: 創(chuàng)建內(nèi)容行的每一列(附加一列序號(hào)) */ private static HSSFCell[] getCells(HSSFRow contentRow, int num){ HSSFCell[] cells = new HSSFCell[num + 1]; for (int i = 0,len = cells.length; i < len; i++){ cells[i] = contentRow.createCell(i); cells[i].setCellStyle(contentStyle); } // 設(shè)置序號(hào)列值，因?yàn)槌鋈?biāo)題行和日期行，所有-2 cells[0].setCellValue(contentRow.getRowNum() - 2); return cells; } /** * @Description: 初始化標(biāo)題行樣式 */ private static void initTitleCellStyle(){ titleStyle.setAlignment(CellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); titleStyle.setFont(titleFont); titleStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.index); } /** * @Description: 初始化日期行樣式 */ private static void initDateCellStyle(){ dateStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION); dateStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); dateStyle.setFont(dateFont); dateStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.index); } /** * @Description: 初始化表頭行樣式 */ private static void initHeadCellStyle(){ headStyle.setAlignment(CellStyle.ALIGN_CENTER); headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); headStyle.setFont(headFont); headStyle.setFillBackgroundColor(IndexedColors.YELLOW.index); headStyle.setBorderTop(CellStyle.BORDER_MEDIUM); headStyle.setBorderBottom(CellStyle.BORDER_THIN); headStyle.setBorderLeft(CellStyle.BORDER_THIN); headStyle.setBorderRight(CellStyle.BORDER_THIN); headStyle.setTopBorderColor(IndexedColors.BLUE.index); headStyle.setBottomBorderColor(IndexedColors.BLUE.index); headStyle.setLeftBorderColor(IndexedColors.BLUE.index); headStyle.setRightBorderColor(IndexedColors.BLUE.index); } /** * @Description: 初始化內(nèi)容行樣式 */ private static void initContentCellStyle(){ contentStyle.setAlignment(CellStyle.ALIGN_CENTER); contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); contentStyle.setFont(contentFont); contentStyle.setBorderTop(CellStyle.BORDER_THIN); contentStyle.setBorderBottom(CellStyle.BORDER_THIN); contentStyle.setBorderLeft(CellStyle.BORDER_THIN); contentStyle.setBorderRight(CellStyle.BORDER_THIN); contentStyle.setTopBorderColor(IndexedColors.BLUE.index); contentStyle.setBottomBorderColor(IndexedColors.BLUE.index); contentStyle.setLeftBorderColor(IndexedColors.BLUE.index); contentStyle.setRightBorderColor(IndexedColors.BLUE.index); contentStyle.setWrapText(true); // 字段換行 } /** * @Description: 初始化標(biāo)題行字體 */ private static void initTitleFont(){ titleFont.setFontName("華文楷體"); titleFont.setFontHeightInPoints((short) 20); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); titleFont.setCharSet(Font.DEFAULT_CHARSET); titleFont.setColor(IndexedColors.BLUE_GREY.index); } /** * @Description: 初始化日期行字體 */ private static void initDateFont() { dateFont.setFontName("隸書(shū)"); dateFont.setFontHeightInPoints((short) 10); dateFont.setBoldweight(Font.BOLDWEIGHT_BOLD); dateFont.setCharSet(Font.DEFAULT_CHARSET); dateFont.setColor(IndexedColors.BLUE_GREY.index); } /** * @Description: 初始化表頭行字體 */ private static void initHeadFont() { headFont.setFontName("宋體"); headFont.setFontHeightInPoints((short) 10); headFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headFont.setCharSet(Font.DEFAULT_CHARSET); headFont.setColor(IndexedColors.BLUE_GREY.index); } /** * @Description: 初始化內(nèi)容行字體 */ private static void initContentFont() { contentFont.setFontName("宋體"); contentFont.setFontHeightInPoints((short) 10); contentFont.setBoldweight(Font.BOLDWEIGHT_NORMAL); contentFont.setCharSet(Font.DEFAULT_CHARSET); contentFont.setColor(IndexedColors.BLUE_GREY.index); } /** * @Description: 封裝Excel導(dǎo)出的設(shè)置信息 */ public static class ExportSetInfo { private LinkedHashMap<String, List> objsMap; private String[] titles; private List<String[]> headNames; private List<String[]> fieldNames; private OutputStream out; public LinkedHashMap<String, List> getObjsMap(){ return objsMap; } /** * @param objMap 導(dǎo)出數(shù)據(jù) * * 泛型 * String : 代表sheet名稱 * List : 代表單個(gè)sheet里的所有行數(shù)據(jù) */ public void setObjsMap(LinkedHashMap<String, List> objsMap) { this.objsMap = objsMap; } public List<String[]> getFieldNames() { return fieldNames; } /** * @param clazz 對(duì)應(yīng)每個(gè)sheet里的每行數(shù)據(jù)的對(duì)象的屬性名稱 */ public void setFieldNames(List<String[]> fieldNames) { this.fieldNames = fieldNames; } public String[] getTitles() { return titles; } /** * @param titles 對(duì)應(yīng)每個(gè)sheet里的標(biāo)題，即頂部大字 */ public void setTitles(String[] titles) { this.titles = titles; } public List<String[]> getHeadNames() { return headNames; } /** * @param headNames 對(duì)應(yīng)每個(gè)頁(yè)簽的表頭的每一列的名稱 */ public void setHeadNames(List<String[]> headNames) { this.headNames = headNames; } public OutputStream getOut() { return out; } /** * @param out Excel數(shù)據(jù)將輸出到該輸出流 */ public void setOut(OutputStream out) { this.out = out; } }}新聞熱點(diǎn)
疑難解答
圖片精選
網(wǎng)友關(guān)注
