java导出excel工具类

时间:2023-03-10 02:47:24
java导出excel工具类

java导出excel须要使用HSSFWorkbook这个类,须要导入poi-3.6-20091214.jar

工具类调用例如以下:

package com.qlwb.business.util;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List; import javax.servlet.http.HttpServletResponse; 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.util.CellRangeAddress; /**
* 导出报表的公共类,提供公共的导出报表的方法
* @Copyright: Copyright (c) 2015
* @Company:
* @author 鹿伟伟
*
*/
public class ExportExcel { static boolean tempDirflag1 = true;
static boolean tempDirflag2 = true; /**
* 依据传入的集合数据生成报表的方法
*
* @param list
* 报表标题表头及数据封装的list,格式:list的第一条数据是标题。String类型;第二条数据是表头列表,List类型;从第三条開始是数据列表
* ,List类型
* @param filename
* 报表excel的文件名称,不须要扩展名
* @param response
* HttpServletResponse
* @return 1表示成功 0表示失败
* @throws IOException
* @Create luweiwei 2015-08-18
* @change
*/
@SuppressWarnings("unchecked")
public static int createReport(List<Object> list, String filename,
HttpServletResponse response) throws IOException { // 数据list至少包含标题和表头
if (list.size() < 2) {
return 0;
} // 组装excel
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(); // 设置Excel标题字体和样式
HSSFFont headFont = workbook.createFont();
headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headFont.setFontHeightInPoints((short) 20);
HSSFCellStyle headCellStyle = workbook.createCellStyle();
headCellStyle.setFont(headFont);
// headCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// headCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
// headCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
// headCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headCellStyle.setWrapText(true);
headCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// headCellStyle.setFillForegroundColor(GREEN_COLOR); // 设置Excel表头的字体和样式
HSSFFont thfont = workbook.createFont();
thfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle thCellStyle = workbook.createCellStyle();
thCellStyle.setFont(thfont);
thCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
thCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
thCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
thCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
thCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
thCellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
thCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 设置Excel内容的字体和样式
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
HSSFCellStyle titleCellStyle = workbook.createCellStyle();
titleCellStyle.setFont(font);
titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
// titleCellStyle.setFillForegroundColor(GREEN_COLOR); // 单元格居左样式
HSSFCellStyle cellLeft = null; // 单元格居中样式
HSSFCellStyle cellCenter = null; // 单元格居右样式
HSSFCellStyle cellRight = null; // 单元格居左样式
cellLeft = workbook.createCellStyle();
cellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 单元格居中样式
cellCenter = workbook.createCellStyle();
cellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 单元格居右样式
cellRight = workbook.createCellStyle();
cellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellRight.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellRight.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 第一行
// 合并标题单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
((List<String>) list.get(1)).size() - 1));
HSSFRow row = sheet.createRow(0);
row.setHeightInPoints(40);
HSSFCell cell = null;
cell = row.createCell(0);
cell.setCellStyle(headCellStyle);
cell.setCellValue((String) (list.get(0))); // 设置表头单元格
List<String> tmpList = null;
tmpList = (List<String>) list.get(1);
int rownum = sheet.getLastRowNum();
row = sheet.createRow(rownum + 1);
for (int i = 0; i < tmpList.size(); i++) {
sheet.setColumnWidth(i, 6000);
cell = row.createCell(i);
cell.setCellStyle(thCellStyle);
cell.setCellValue(tmpList.get(i));
} // 循环设置数据的单元格
for (int i = 2; i < list.size(); i++) {
tmpList = (List<String>) list.get(i);
rownum = sheet.getLastRowNum();
row = sheet.createRow(rownum + 1);
for (int j = 0; j < tmpList.size(); j++) {
cell = row.createCell(j);
cell.setCellStyle(titleCellStyle);
cell.setCellValue(tmpList.get(j));
}
}
response.setHeader("Content-disposition", "attachment; filename="
+ URLEncoder.encode(filename, "utf-8") + ".xls");
response.setContentType("application/msexcel;charset=utf-8");
OutputStream out = null;
try {
out = response.getOutputStream();
out.flush();
workbook.write(out);
} finally {
try {
if(out!=null){
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return 1;
}
}