ExcelUtil

package com.ics.pm.util;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;


import javax.servlet.http.HttpServletResponse;


import org.apache.commons.lang.ObjectUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.poifs.filesystem.POIFSFileSystem;


/**
 * 
 * 封装分析查询的Excel导出相关的一些方法
 * @author lzt104
 *
 */
public class ExcelUtil {
private static POIFSFileSystem poifile = null;
public static HSSFWorkbook workbook = new HSSFWorkbook();
public static HSSFSheet sheet;
/**
* 将HSSFWorkbook对象写出去
* @param response HttpServletResponse对象
* @param book HSSFWorkbook对象
* @param name 文件名
* @throws Exception
* @author HuKaiXuan 2013-6-24 下午6:29:34
*/
public static void writeOutWorkbook(HttpServletResponse response,HSSFWorkbook book,String name) throws Exception{
if (book != null) {
response.reset();// 清空输出流
response.setHeader("Location", name);
response.setContentType("text/plain");
response.setContentType("application/octet-stream;charset=UTF-8");
response.setContentType("application/ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(name, "UTF-8"));
book.write(response.getOutputStream());
}
}

/**
* 创建指定行数、列数的单元格
* @param workbook HSSFWorkbook对象 工作薄
* @param sheet HSSFSheet对象 标签页
* @param rowNum 要创建多少行
* @param colNum 要创建多少列
* @param startRow 起始行数（从0开始算）
* @param startCol 起始列数（从0开始算）
* @author HuKaiXuan 2013-8-10 下午5:27:01
*/
public static void createCell(HSSFWorkbook workbook,HSSFSheet sheet,int rowNum, int colNum, int startRow, int startCol) {
HSSFCellStyle style = getCommonStyle(workbook);
HSSFRow row = null;
HSSFCell cell = null;
for (int i = startRow; i < rowNum + startRow; i++) {
row = sheet.createRow(i);
row.setHeightInPoints((float) 13.5); // 设置行高为13.5
for (int j = startCol; j < colNum + startCol; j++) {
cell = row.createCell(j);
cell.setCellStyle(style);
}
}
}

/**
* 创建指定行数、列数的单元格
* @param sheet HSSFSheet对象 标签页
* @param rowNum 要创建多少行
* @param colNum 要创建多少列
* @param startRow 起始行数（从0开始算）
* @param startCol 起始列数（从0开始算）
* @author HuKaiXuan 2013-8-10 下午5:27:01
*/
public static void createCell(HSSFSheet sheet,int rowNum, int colNum, int startRow, int startCol) {
HSSFCellStyle style = getCommonStyle(sheet.getWorkbook());
HSSFRow row = null;
HSSFCell cell = null;
for (int i = startRow; i < rowNum + startRow; i++) {
row = sheet.createRow(i);
row.setHeightInPoints((float) 13.5); // 设置行高为13.5
for (int j = startCol; j < colNum + startCol; j++) {
cell = row.createCell(j);
cell.setCellStyle(style);
}
}
}

/***
* 获取一个通用的样式：宋体、文本类型
* @param workbook HSSFWorkbook对象
* @return 一个通用的HSSFCellStyle对象
* @author HuKaiXuan 2013-8-3 下午4:25:28
*/
public static HSSFCellStyle getCommonStyle(HSSFWorkbook workbook) {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);
style.setWrapText(true); 

// 设置单元格字体
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11);
style.setFont(font); 

HSSFDataFormat format = workbook.createDataFormat();
style.setDataFormat(format.getFormat("@")); // 设置为文本类型
return style;
}
public static boolean loadFile(String pa) {
InputStream input = null;
File file = new File(pa);
try {
input = new FileInputStream(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
try {
poifile = new POIFSFileSystem(input);
workbook = new HSSFWorkbook(poifile);
} catch (IOException e) {
e.printStackTrace();
return false;
} finally {
try {
input.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return true;
}
public static boolean loadFile(InputStream input) {
try {
poifile = new POIFSFileSystem(input);
workbook = new HSSFWorkbook(poifile);
} catch (IOException e) {
e.printStackTrace();
return false;
} finally {
try {
input.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return true;
}
/**
* 该方法适合根据模版导出数据
* @param dataList  数据
* @param filePath  模板路径   
* @param startRow  开始行
* @param startCol 开始列
* @param columnSize 所需创建列数
* @return
*/
public static HSSFWorkbook exportToExport(List<Object[]> dataList,InputStream input,int startRow, int startCol,int columnSize) {

int dataize = dataList.size();
ExcelUtil.loadFile(input);
sheet = workbook.getSheetAt(0);
createCell(sheet,dataize, columnSize+1, startRow,startCol);
createStyle(dataize,columnSize+1, startRow);
for(int i=startRow;i<dataList.size()+startRow;i++){
setCellValue(String.valueOf(i-startRow+1), i, 0);
Object[] objs = (Object[])dataList.get(i-startRow);
for(int j =0; j<columnSize;j++){
setCellValue(objs[j]!=null?objs[j].toString():"", i, j+1);
}
}
return workbook;
}


/**
 * 不生成序号
* @param dataList  数据
* @param filePath  模板路径   
* @param startRow  开始行
* @param startCol 开始列
* @param columnSize 所需创建列数
 * @return
 */
public static HSSFWorkbook exportToExport_xh(List<Object[]> dataList,InputStream input,int startRow, int startCol,int columnSize) {

int dataize = dataList.size();
ExcelUtil.loadFile(input);
sheet = workbook.getSheetAt(0);
//createCell(sheet,dataize, columnSize, startRow,startCol);
//createStyle(dataize,columnSize, startRow);
for(int i=startRow;i<dataList.size()+startRow;i++){
setCellValue(String.valueOf(i-startRow+1), i, 0);
Object[] objs = (Object[])dataList.get(i-startRow);
for(int j =0; j<columnSize;j++){
setCellValue(objs[j]!=null?objs[j].toString():"", i, j);
}
}
return workbook;
}


/**
* 不生成序号
* @param dataList  数据
* @param filePath  模板路径   
* @param startRow  开始行
* @param startCol 开始列
* @param columnSize 所需创建列数
* @param starthead 从第X行开始改变样式
* @return
*/
public static HSSFWorkbook exportToExport_head(List<Object[]> dataList,InputStream input,int startRow, int startCol,int columnSize,int starthead) {

int dataize = dataList.size();
ExcelUtil.loadFile(input);
sheet = workbook.getSheetAt(0);
createCell(sheet,dataize, columnSize, starthead,startCol);
createStyle(dataize,columnSize, starthead);
for(int i=startRow;i<dataList.size()+startRow;i++){
setCellValue(String.valueOf(i-startRow+1), i, 0);
Object[] objs = (Object[])dataList.get(i-startRow);
for(int j =0; j<columnSize;j++){
setCellValue(objs[j]!=null?objs[j].toString():"", i, j);
}
}
return workbook;
}




/**
* 该方法适合根据模版导出数据
* @param dataList 数据
* @param input 导出模板
* @param map 包含开始行，开始列，结束列
* @param fields 导出列
* @param displayRowNumber 是否导出序号
* @return
* @throws JSONException
*/
public static HSSFWorkbook exportToExport(List dataList, InputStream input,
Map<String, Integer> map, String[] fields,boolean displayRowNumber) {
int startRow = null == map.get("startRow")?0:map.get("startRow"); 
int startCol= null == map.get("startCol")?0: map.get("startCol");
int columnSize= null == map.get("columnSize")?0: map.get("columnSize");

int dataize = dataList.size();
ExcelUtil.loadFile(input);
sheet = workbook.getSheetAt(0);
Map obj = null;
String value = "";
if(displayRowNumber){//显示序号
createCell(sheet,dataize, columnSize+1, startRow,startCol);
createStyle(dataize,columnSize+1, startRow);

for(int i=startRow;i<dataList.size()+startRow;i++){
obj = (Map) dataList.get(i - startRow);
setCellValue(String.valueOf(i-startRow+1), i, 0);
for(int j =0; j<fields.length + startCol;j++){
value = ObjectUtils.toString(obj.get(fields[j - startCol]));
setCellValue(value, i, j+1);
}
}
}else{//不显示序号
createCell(sheet,dataize, columnSize, startRow,startCol);
createStyle(dataize,columnSize, startRow);

for(int i=startRow;i<dataList.size()+startRow;i++){
obj = (Map) dataList.get(i - startRow);
for(int j =0; j<fields.length + startCol;j++){
value = ObjectUtils.toString(obj.get(fields[j - startCol]));
setCellValue(value, i, j);
}
}
}
return workbook;
}

/**
* 该方法适合在传入的HSSFWorkbook对象上增加数据
* @param dataList  数据
* @param wbook  HSSFWorkbook对象   
* @param startRow  开始行
* @param startCol 开始列
* @param columnSize 所需创建列数
* @return
*/
public static HSSFWorkbook exportToExport2(List<Object[]> dataList,HSSFWorkbook wbook,int startRow, int startCol,int columnSize) {

int dataize = dataList.size();
workbook = wbook;
sheet = workbook.getSheetAt(0);
//createCell(sheet,dataize, columnSize+1, startRow,startCol);
//createStyle(dataize,columnSize+1, startRow);
for(int i=startRow;i<dataList.size()+startRow;i++){
setCellValue(String.valueOf(i-startRow+1), i, 0);
Object[] objs = (Object[])dataList.get(i-startRow);
for(int j =0; j<columnSize;j++){
setCellValue(objs[j]!=null?objs[j].toString():"", i, j+1);
}
}
return workbook;
}


public static HSSFSheet setCellValue(String value,int rownum,int cellnum){
sheet.getRow(rownum).getCell(cellnum).setCellValue(value);
return sheet;
}
public static void createStyle(int rownum,int cellnum,int ksrow){
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);
style.setWrapText(true);
for(int i=ksrow;i<rownum+ksrow;i++){
HSSFRow row = sheet.createRow(i);
for(int j=0;j<cellnum;j++){
HSSFCell cell = row.createCell((short)j);  
cell.setCellStyle(style);
}
}
}
public static HSSFCellStyle getHssfFont(){
HSSFFont font  = workbook.createFont();
font.setFontHeightInPoints((short) 11);//字号
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
font.setColor(HSSFFont.COLOR_NORMAL);
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFont(font);
return style;
}
}