java生成excel并下载（poi）

/**   该方法需导入poi相关jar包，实现了页面触发、java生成excel并弹出下载框选择下载路径的进行保存的功能*/

import java.io.File;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.usermodel.Cell;
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 org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * Excel工具类
 * @date 2014-4-17 上午10:55:51
 */
public class ExcelUtils {


/**
* 日志
*/
private static Log log = LogFactory.getLog(ExcelUtils.class);

/**
* sheet名称
*/
private String sheetName;


/**
* 写入excel2003
* @param datas 写入的数据
* @param path 文件路径
* @param flag 是否续写
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public Workbook writeExcel2003(List<Object[]> datas, String path, boolean flag) throws FileNotFoundException, IOException {
if(flag) {
FileInputStream fis = new FileInputStream(path);
Workbook workbook = writeExistsExcel(new HSSFWorkbook(fis), datas);
fis.close();
return workbook;
} else {
return writeExcel(new HSSFWorkbook(), datas);
}
}


/**
* 写入excel2003
* @param datas 写入的数据
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public Workbook writeExcel2003(List<Object[]> datas) throws FileNotFoundException, IOException {
return writeExcel(new HSSFWorkbook(), datas);
}


/**
* 写入excel2007
* @param datas 写入的数据
* @param path 文件路径
* @param flag 是否续写
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public Workbook writeExcel2007(List<Object[]> datas, String path, boolean flag) throws FileNotFoundException, IOException {
if(flag) {
FileInputStream fis = new FileInputStream(path);
Workbook workbook = writeExistsExcel(new XSSFWorkbook(fis), datas);
fis.close();
return workbook;
} else {
return writeExcel(new XSSFWorkbook(), datas);
}
}


/**
* 写入excel2007
* @param datas 写入的数据
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public Workbook writeExcel2007(List<Object[]> datas) throws FileNotFoundException, IOException {
return writeExcel(new XSSFWorkbook(), datas);
}


/**
* 写入新的excel,设置样式的代码已经注释掉，太浪费资源
* @param workbook
* @param datas
* @return
*/
private Workbook writeExcel(Workbook workbook, List<Object[]> datas) {
// 设置样式
/*
* CellStyle cellStyle = wb.createCellStyle();// 创建样式
* cellStyle.setDataFormat
* (wb.createDataFormat().getFormat(dataFormat));// 日期格式
* cellStyle.setAlignment(CellStyle.ALIGN_CENTER);// 居中
*/
Sheet sheet = null == sheetName ? sheet = workbook.createSheet() : workbook.createSheet(sheetName);
for(int i = 0; i < datas.size(); i++) { // 遍历数据
Object[] objList = datas.get(i);
Row row = sheet.createRow(i);
int objListSize = objList.length;
for (int x = 0; x < objListSize; x++) {
Cell cell = row.createCell(x);
Object value = objList[x];
if (value instanceof String) {
cell.setCellValue(value.toString());
} else if (value instanceof Double) {
cell.setCellValue((Double)value);
} else if (value instanceof Integer) {
cell.setCellValue((Integer)value);
} else if (value instanceof Long) {
cell.setCellValue((Long)value);
} else if (value instanceof Float) {
cell.setCellValue((Float)value);
} else if (value instanceof Boolean) {
cell.setCellValue((Boolean)value);
} else if (value instanceof java.util.Date || value instanceof java.sql.Date) {
// cell.setCellValue(DateUtils.getDateString((Date)value));
} else if (value instanceof BigDecimal) {
cell.setCellValue(((BigDecimal)value).doubleValue());
}
// 设置内容样式
// cell.setCellStyle(cellStyle);
// 自动调整列宽
// sheet.autoSizeColumn(x);
}
}
return workbook;
}


/**
* 续写excel,设置样式的代码已经注释掉，太浪费资源
* @param workbook
* @param datas
* @return
*/
private Workbook writeExistsExcel(Workbook workbook, List<Object[]> datas) {
// 设置样式
/*
* CellStyle cellStyle = wb.createCellStyle();// 创建样式
* cellStyle.setDataFormat
* (wb.createDataFormat().getFormat(dataFormat));// 日期格式
* cellStyle.setAlignment(CellStyle.ALIGN_CENTER);// 居中
*/
Sheet sheet = workbook.getSheetAt(0);
int totalRows = sheet.getLastRowNum();
for(int i = totalRows; i < datas.size() + totalRows; i++) { // 遍历数据
Object[] objList = datas.get(i - totalRows);
Row row = sheet.createRow(i + 1);
int objListSize = objList.length;
for (int x = 0; x < objListSize; x++) {
Cell cell = row.createCell(x);
Object value = objList[x];
if (value instanceof String) {
cell.setCellValue(value.toString());
} else if (value instanceof Double) {
cell.setCellValue((Double)value);
} else if (value instanceof Integer) {
cell.setCellValue((Integer)value);
} else if (value instanceof Long) {
cell.setCellValue((Long)value);
} else if (value instanceof Float) {
cell.setCellValue((Float)value);
} else if (value instanceof Boolean) {
cell.setCellValue((Boolean)value);
} else if (value instanceof java.util.Date || value instanceof java.sql.Date) {
// cell.setCellValue(DateUtils.getDateString((Date)value));
} else if (value instanceof BigDecimal) {
cell.setCellValue(((BigDecimal)value).doubleValue());
}
// 设置内容样式
// cell.setCellStyle(cellStyle);
// 自动调整列宽，樣式很浪費時間
// sheet.autoSizeColumn(x);
}
}
return workbook;
}

/**
* 读取excel2003
* @param path 文件路径
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public List<List<Object>> readExcel2003(String path) throws FileNotFoundException, IOException {
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(path));
List<List<Object>> resultList = new ArrayList<List<Object>>();
//获得该工作区的第sheet
HSSFSheet sheet = workbook.getSheetAt(0);
//总共有多少行
int totalRows = sheet.getLastRowNum();
for(int i = 1; i <= totalRows; i++) {// 行数循环,第一行头部去
List<Object> objList = new ArrayList<Object>();
HSSFRow row = sheet.getRow(i);
if(row == null) {
continue;
}
int totleCells = row.getLastCellNum();
for(int x = 0; x < totleCells; x++) {
HSSFCell cell = row.getCell(x);
if(cell != null) {
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
objList.add(cell.getStringCellValue());
} else if (cell.getCellType()  == Cell.CELL_TYPE_NUMERIC) {
objList.add(cell.getNumericCellValue());
} else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
objList.add(cell.getBooleanCellValue());
}
}
}
resultList.add(objList);
}
return resultList;
}


/**
* 读取excel2007
* @param path 文件路径
* @return
*/
public List<List<Object>> readExcel2007(String path) {
XSSFWorkbook workbook = null;
try {
workbook = new XSSFWorkbook(new FileInputStream(path));
} catch (FileNotFoundException e) {
log.error(e.getMessage(), e);
return null;
} catch (IOException e) {
log.error(e.getMessage(), e);
return null;
} catch (Exception e) {
log.error(e.getMessage(), e);
return null;
}
List<List<Object>> resultList = new ArrayList<List<Object>>();
//获得该工作区的第几个sheet
XSSFSheet sheet = workbook.getSheetAt(0);
//总共有多少行
int totalRows = sheet.getLastRowNum();
for(int i = 1; i <= totalRows; i++) {// 行数循环,第一行头部去掉
List<Object> objList = new ArrayList<Object>();
XSSFRow row = sheet.getRow(i);
if(row == null) {
continue;
}
int totleCells = row.getLastCellNum();
for(int x = 0; x < totleCells; x++) {
XSSFCell cell = row.getCell(x);
if(cell != null) {
if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
objList.add(cell.getStringCellValue());
} else if(cell.getCellType()  == Cell.CELL_TYPE_NUMERIC) {
if(HSSFDateUtil.isCellDateFormatted(cell)) {// 是否是日期格式
// objList.add(DateUtils.getDateString(cell.getDateCellValue()));
} else {
objList.add((long)cell.getNumericCellValue());
}
}
}
}
resultList.add(objList);
}
return resultList;
}

public String getSheetName() {
return sheetName;
}


public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}


public static void main(String[] args) throws FileNotFoundException, IOException {
/*List<Object[]> datas = new ArrayList<Object[]>();
Object[] list1 = new Object[3];
list1[0] = "用户";
list1[1] = "密码";
list1[2] = "创建日期";
Object[] list2 = new Object[3];
list2[0] = "xiaomaha";
list2[1] = new BigDecimal(1);
list2[2] = new Date();
datas.add(list1);
for(int i = 0; i < 5; i++) {
datas.add(list2);
}
ExcelUtils et = new ExcelUtils();
et.setSheetName("测试");
String filename = "e://workbook2.xlsx";
File file = new File(filename);
boolean flag = false;
if(file.exists()) {
flag = true;
}
Workbook workbook = et.writeExcel2007(datas, filename, flag);
try {
FileOutputStream out = new FileOutputStream(filename);
workbook.write(out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}*/
String path = "D://workspace//.metadata//.plugins//org.eclipse.wst.server.core//tmp2//wtpwebapps//zplay_man//data/advertiser/2015/03/27/20150327103443_3275.xls";
ExcelUtils excelUtil = new ExcelUtils();
List<List<Object>> resultList = excelUtil.readExcel2003(path);
for(List<Object> objList : resultList) {
for(Object obj : objList) {
System.out.println(obj);
}
}
}

/**
* 读取Excel，返回List
* @param fileName 文件路径
* @param rowBein 从第几行开始读取，首行算作第一行
* @param cols JavaBean中的属性和Excel列的对应关系，如果木有对应的设置成空字符串""
* @param clazz 对应的JavaBean
* @return
* @throws Exception
*/
@SuppressWarnings("resource")
public static <T> List<T> read(String fileName, int rowBein, String[] cols, Class<T> clazz) throws Exception {
List<T> list = new ArrayList<T>();
FileInputStream is = new FileInputStream(new File(fileName));
Workbook wb = null;
if (fileName.endsWith(".xls")) {
wb = new HSSFWorkbook(is);
} else if (fileName.endsWith(".xlsx")) {
wb = new XSSFWorkbook(is);
} else {
throw new Exception("无效的Excel文件");
}


int sheetNum = wb.getNumberOfSheets();
for (int i = 0; i < sheetNum; i++) {
Sheet childSheet = wb.getSheetAt(i);
int rowNum = childSheet.getLastRowNum();
for (int j = rowBein - 1; j <= rowNum; j++) {
Row row = childSheet.getRow(j);
if (row != null) {
int cellNum = row.getLastCellNum();
T obj = clazz.newInstance();
int low = cellNum > cols.length ? cols.length : cellNum;
int count = 0;
for (int k = 0; k < low; k++) {
Cell cell = row.getCell(k);
if (cell != null) {
DecimalFormat dfd = new DecimalFormat("0.00");
DecimalFormat dfi = new DecimalFormat("0");
String value = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// value = DateUtils.format(cell.getDateCellValue());
} else {
String str = String.valueOf(cell.getNumericCellValue());
if (str.substring(str.indexOf(".")).length()>=3) {
value = dfd.format(cell.getNumericCellValue());
} else {
value = dfi.format(cell.getNumericCellValue());
}
}
break;
case Cell.CELL_TYPE_FORMULA:
DecimalFormat df2 = new DecimalFormat("0.0000000000");
value = df2.format(cell.getNumericCellValue());
break;
default:
break;
}
String colName = cols[k];
if (value == null || value.trim().equals("")) {
count++;
value = "";
}
if (!colName.trim().equals("")) {
Field field = clazz.getDeclaredField(colName);
String setMethodName = "set" + colName.substring(0, 1).toUpperCase() + colName.substring(1);
Method setMethod = clazz.getMethod(setMethodName, field.getType());
setMethod.invoke(obj, value);
}
}
}
if (count < low) {
list.add(obj);
}
}
}
}
return list;
}


/**
* @param os
* @param header 表头
* @param title 列标题
* @param cols 列字段名
* @param dataList 数据集
* @param workbookName sheet名称
* @param serialNo 序号
* @param sum 需要汇总的列,索引从0开始
* @return
* @throws Exception
*/
public static <T> void writeExcel(OutputStream os, String header, String[] title, String[] cols, List<T> dataList, String workbookName, String serialNo,int[] sum) throws Exception {
Map<Integer, BigDecimal> sumMap = new HashMap<Integer, BigDecimal>();
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(workbookName);
HSSFRow row = sheet.createRow(0);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cols.length));
Cell cell = row.createCell(0);
cell.setCellValue(header);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
cell.setCellStyle(style);
row = sheet.createRow(1);
int cellIndex = 0;
if (serialNo != null&&!serialNo.equals("")) {
cell = row.createCell(0);
cell.setCellValue(serialNo);
cellIndex++;
}
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i+cellIndex);
cell.setCellValue(title[i]);
}
for (int i = 0; i < dataList.size(); i++) {
row = sheet.createRow(i + 2);
row.createCell(0).setCellValue(i+1);
T t = dataList.get(i);
@SuppressWarnings("rawtypes")
Class tCls = t.getClass();
for (int j = 0; j < cols.length; j++) {
String getMethodName = "get" + cols[j].substring(0, 1).toUpperCase() + cols[j].substring(1);
@SuppressWarnings("unchecked")
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
if(sum!=null){
for(int sm:sum){
if(j==sm){
Object temp = sumMap.get(sm);
BigDecimal result = new BigDecimal(0);
if(temp!=null){
if(value!=null&&value instanceof BigDecimal){
BigDecimal b = new BigDecimal(0);
b = (BigDecimal)temp;
result = b.add((BigDecimal)value);
}else{
result =(BigDecimal) temp;
}
}else{
if(value!=null&&value instanceof BigDecimal){
result = (BigDecimal)value;
}
}
sumMap.put(sm, result);
}
}
}
setCellValue(row.createCell(j+cellIndex), value);
}
}
row = sheet.createRow(dataList.size() + 2+1);
for(Map.Entry<Integer,BigDecimal> entry:sumMap.entrySet()){
System.out.println(entry.getKey()+" = "+entry.getValue());
setCell(wb, row.createCell(cellIndex+entry.getKey()), entry.getValue());
}
// row.createCell(1);
wb.write(os);
os.flush();
os.close();
}


private static void setCell(HSSFWorkbook wb,HSSFCell cell,Object value){
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cell.setCellStyle(cellStyle);
cell.setCellValue(value.toString());
}
private static void setCellValue(HSSFCell cell, Object value) {
String textValue = null;
if(value==null){
textValue="";
}else if (value instanceof Boolean) {
boolean bValue = (Boolean) value;
cell.setCellValue(bValue);


}else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
textValue = sdf.format(date);
}else{
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
}
if(textValue!=null){
            Pattern p = Pattern.compile("^//d+(//.//d+)?$");  
            Matcher matcher = p.matcher(textValue);
            if(matcher.matches()){
               //是数字当作double处理
               cell.setCellValue(Double.parseDouble(textValue));
            }else{
               HSSFRichTextString richString = new HSSFRichTextString(textValue);
               cell.setCellValue(richString);
            }
         }
}


}