java导出文件到excel文件怎么打开_Java导出数据到Excel文件

Java导出数据到Excel文件需要的jar包：easypoi-0.1.3.jar， poi-3.7-1029

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 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 createStyles(Workbook wb){

Map styles = new HashMap();

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;

}