Java导出数据到Excel文件，java导出数据excel,package com.
分享于 点击 34705 次 点评：203
Java导出数据到Excel文件，java导出数据excel,package com.
package com.sais.inkaNet.reportStatistics.operationBeanavior.service;import java.io.IOException;import java.io.OutputStream;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.CreationHelper;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.PrintSetup;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.util.CellRangeAddress;import com.sais.inkaNet.base.db.VOpbDateResult;import com.sais.inkaNet.base.db.VOpbMonthResult;import com.sais.inkaNet.base.db.VOpbWeekResult;import com.sais.inkaNet.base.db.VOpbYearResult;public class SummaryHSSF { //这个是创建和书写excel文档的代码。public void outExcel(String sheetP,String[] titles,List list,int width,String address,String type,HttpServletResponse response) throws IOException { //创建Workbook对象（这一个对象代表着对应的一个Excel文件） //HSSFWorkbook表示以xls为后缀名的文件 Workbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); //获得CreationHelper对象,这个应该是一个帮助类 CreationHelper helper = wb.getCreationHelper(); //创建Sheet并给名字(表示Excel的一个Sheet) Sheet sheet = wb.createSheet(sheetP); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setDefaultColumnWidth(width); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue(sheetP); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf(address)); //header row Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(60); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } VOpbYearResult vOpbYearResult=new VOpbYearResult(); VOpbWeekResult vOpbWeekResult=new VOpbWeekResult(); VOpbDateResult vOpbDateResult=new VOpbDateResult(); VOpbMonthResult vOpbMonthResult=new VOpbMonthResult(); //表头的设置以及J列和K列的设置 int rownum = 2; for (int i = 0; i < list.size(); i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); cell.setCellStyle(styles.get("cell")); if("1".equals(type)){ vOpbYearResult=(VOpbYearResult) list.get(i); if(j==0){ cell.setCellValue(vOpbYearResult.getObName()); } if(j==1){ cell.setCellValue(vOpbYearResult.getObTotleNumber()); } }else if("2".equals(type)){ vOpbMonthResult=(VOpbMonthResult) list.get(i); if(j==0){ cell.setCellValue(vOpbMonthResult.getObName()); } if(j==1){ cell.setCellValue(vOpbMonthResult.getObTotleNumber()); } }else if("3".equals(type)){ vOpbWeekResult=(VOpbWeekResult) list.get(i); if(j==0){ cell.setCellValue(vOpbWeekResult.getObName()); } if(j==1){ cell.setCellValue(vOpbWeekResult.getObTotleNumber()); } }else if("4".equals(type)){ vOpbDateResult=(VOpbDateResult) list.get(i); if(j==0){ cell.setCellValue(vOpbDateResult.getObName()); } if(j==1){ cell.setCellValue(vOpbDateResult.getObTotleNumber()); } } } } //输出 response.setHeader("Content-disposition", "attachment; filename=test.xls");//设定输出文件头 response.setContentType("application/vnd.ms-excel");//定义输出类型 OutputStream os = response.getOutputStream(); wb.write(os); os.close();// response.flushBuffer(); //response.reset();// response.resetBuffer(); // response.getWriter().close(); } /** * 边框 * @param wb * @return */ public static CellStyle createStyleCell(Workbook wb){ CellStyle cellStyle = wb.createCellStyle(); //设置一个单元格边框颜色 //BORDER_SLANTED_DASH_DOT加粗虚线 //BORDER_DASH_DOT虚线 cellStyle.setBorderBottom(CellStyle.BORDER_DASH_DOT_DOT); cellStyle.setBorderTop(CellStyle.BORDER_DASH_DOT_DOT); cellStyle.setBorderLeft(CellStyle.BORDER_DASH_DOT_DOT); cellStyle.setBorderRight(CellStyle.BORDER_DASH_DOT_DOT); //设置一个单元格边框颜色 cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); return cellStyle; } /** * 设置文字在单元格里面的位置 * CellStyle.ALIGN_CENTER * CellStyle.VERTICAL_CENTER * @param cellStyle * @param halign * @param valign * @return */ public static CellStyle setCellStyleAlignment(CellStyle cellStyle,short halign,short valign){ //设置上下 cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //设置左右 cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); return cellStyle; } /** * 格式化单元格 * 如#,##0.00,m/d/yy去HSSFDataFormat或XSSFDataFormat里面找 * @param cellStyle * @param fmt * @return */ public static CellStyle setCellFormat(CreationHelper helper,CellStyle cellStyle,String fmt){ //还可以用其它方法创建format cellStyle.setDataFormat(helper.createDataFormat().getFormat(fmt)); return cellStyle; } /** * 前景和背景填充的着色 * @param cellStyle * @param bg IndexedColors.ORANGE.getIndex(); * @param fg IndexedColors.ORANGE.getIndex(); * @param fp CellStyle.SOLID_FOREGROUND * @return */ public static CellStyle setFillBackgroundColors(CellStyle cellStyle,short bg,short fg,short fp){ //cellStyle.setFillBackgroundColor(bg); cellStyle.setFillForegroundColor(fg); cellStyle.setFillPattern(fp); return cellStyle; } /** * 设置字体 * @param wb * @return */ public static Font createFonts(Workbook wb){ //创建Font对象 Font font = wb.createFont(); //设置字体 font.setFontName("黑体"); //着色 font.setColor(HSSFColor.BLUE.index); //斜体 font.setItalic(true); //字体大小 font.setFontHeight((short)300); return font; } private static Map<String, CellStyle> createStyles(Workbook wb){ Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short)18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short)11); monthFont.setColor(IndexedColors.WHITE.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); style.setWrapText(true); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); return styles; }}
用户点评