导出表格Util

package com.soft.share.util;


import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


import org.apache.commons.lang.StringUtils;


import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


/**
 * 导出excel文件
 * 
 * @author lq
 * @version $Id$
 */
public class ExcelUtils {


private static final int CHAR_TITLE = 14;// 标题字体大小

private static final int CHAR_COLUM = 12; // 列字体大小

private static final int CHAR_NORMAL = 12;// 字体大小

private static final int HEIGHT = 400; // 行高

private static final int WIDTH = 20; // 宽


/***
* 导出EXCEL
* 
* @param fileName
*            文件名
* @param titles
*            列名
* @param list
*            数据
* @param userName
*            制表人
* @param createTime
*            制表时间
* @param response
*            响应
* @param request
*            请求
*/
public static void exportExcel(String fileName, String titles, List<?> list, String userName, HttpServletResponse response, HttpServletRequest request) {
try {
byte[] fileNameByte = (fileName + ".xls").getBytes("GBK");
String fullFileName = new String(fileNameByte, "ISO8859-1");
byte[] bytes = createExcelDate(fileName, titles, list, userName);
response.setContentType("application/x-msdownload");
response.setContentLength(bytes.length);
response.setHeader("Content-Disposition", "attachment;filename=" + fullFileName);
response.getOutputStream().write(bytes);
response.getOutputStream().close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}


/***
* 创建excel
* @param fileName
*            文件名
* @param titles
*            列名
* @param list
*            数据源
* @param userName
*            制表人
* @param response
*            响应
* @return
*/
public static byte[] createExcelDate(String fileName, String titles, List<?> list, String userName) {
ByteArrayOutputStream out = new ByteArrayOutputStream();
try {
WritableWorkbook wwb = null;
Label label = null; // 单元格
WritableFont font = null;// 设置样式
WritableCellFormat format = null;


String[] arrTitle = titles.split(",");
int totalColum = arrTitle.length - 1; // 总列数


wwb = Workbook.createWorkbook(out/* new File("E:\\" + fileName + ".xls") */);
WritableSheet ws = wwb.createSheet(fileName, 0);


// 设置表头样式
font = new WritableFont(WritableFont.createFont("宋体"), CHAR_TITLE, WritableFont.BOLD);
format = new WritableCellFormat(font);
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
format.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
format.setAlignment(Alignment.CENTRE); // 水平对齐


// 表头
label = new Label(0, 0, fileName, format);
ws.addCell(label);
ws.mergeCells(0, 0, totalColum, 0);
ws.setRowView(0, HEIGHT * 3);// 3倍数据行的高度


// 时间段的样式
font = new WritableFont(WritableFont.createFont("宋体"), CHAR_COLUM);
format = new WritableCellFormat(font);
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
format.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
format.setAlignment(Alignment.LEFT); // 水平对齐


// 设置列名
format = new WritableCellFormat(font);
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
format.setAlignment(Alignment.CENTRE);
format.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
for (int i = 0; i < arrTitle.length; i++) {
label = new Label(i, 2, arrTitle[i], format);
ws.addCell(label);
ws.setColumnView(i, WIDTH); // 只要设置列的宽度就可以
}


WritableFont dataFont = new WritableFont(WritableFont.createFont("宋体"), CHAR_NORMAL);
// 往对应的列里面加数据
for (int j = 0; j < list.size(); j++) {
Object obj = list.get(j);
Object[] o = (Object[]) obj;
for (int index = 0; index < o.length; index++) {
format = new WritableCellFormat(dataFont);
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
format.setAlignment(Alignment.CENTRE);
label = new Label(index, j + 3, o[index]==null?"":o[index].toString(), format);
ws.addCell(label);
}
}


font = new WritableFont(WritableFont.createFont("宋体"), CHAR_COLUM);
format = new WritableCellFormat(font);
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
format.setAlignment(Alignment.RIGHT);
// 设置制表日期
ws.mergeCells(0, list.size() + 3, totalColum, list.size() + 3);// 合并
ws.mergeCells(0, list.size() + 4, totalColum, list.size() + 4);
label = new Label(0, list.size() + 3, "制表日期：" + new SimpleDateFormat("yyyy-MM-dd").format(new Date()), format);
ws.addCell(label);
// 设置制表人
label = new Label(0, list.size() + 4, "制表人：" + userName, format);
ws.addCell(label);


// 设置行高,除了表头
for (int c = 1; c < list.size() + 5; c++) {
ws.setRowView(c, HEIGHT);
}


wwb.write();
wwb.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
return out.toByteArray();
}

/**
* 读取Excel
* startRow 从第几行开始（首行为0）
* columns 一共多少列(为空或小于1时由程序读取列数)
* @param filePath
* @throws Exception 
*/
public static List<String[]> readExcel(String filePath , int startRow, Integer columns) throws Exception {
List<String[]> retList = new ArrayList<String[]>();
try {
InputStream is = new FileInputStream(filePath);
Workbook rwb = Workbook.getWorkbook(is);

Sheet st = rwb.getSheet(0);
if (columns == null || columns < 1) {
columns = st.getColumns();
}
int rows = st.getRows();

for (int k = startRow; k < rows; k++) {// 行
String[] row = new String[columns];
boolean has = false;
for (int i = 0; i < columns; i++) {// 列
Cell c00 = st.getCell(i, k);
String strc00 = c00.getContents();
if(StringUtils.isNotBlank(strc00)) {
has = true;
}
// 通用的获取cell值的方式,返回字符串
row[i] = strc00;
}
if(!has) {
break;
}
retList.add(row);
}
// 关闭
if (is != null) is.close();
if (rwb != null) rwb.close();
} catch (Exception e) {
throw new Exception("请将Excel文件另存为2003版！");
}
return retList;
}

/**
* 读取Excel
* startRow 从第几行开始（首行为0）
* @param filePath
* @throws Exception 
*/
public static List<String[]> readXLSX(String filePath , int startRow) throws Exception {
List<String[]> retList = new ArrayList<String[]>();
try {
InputStream is = new FileInputStream(filePath);
HSSFWorkbook wk = new HSSFWorkbook(is);
HSSFSheet sheet = wk.getSheetAt(0);//第一个sheet

int rows = sheet.getLastRowNum();//获取行数

for (int k = startRow; k < rows; k++) {// 行
HSSFRow hssfRow = sheet.getRow(k);
int minCell = hssfRow.getFirstCellNum();
int maxCell = hssfRow.getLastCellNum();
String[] row = new String[maxCell - minCell + 1];
for (int i = minCell; i < maxCell; i++) { // 列
HSSFCell cell = hssfRow.getCell(i);
String strc00 = ""; 
if (cell != null) {
strc00 = getStringVal(cell);
}
// 通用的获取cell值的方式,返回字符串
row[i] = strc00;
}
retList.add(row);
}
// 关闭
if (is != null) is.close();
} catch (Exception e) {
throw new Exception("解析Excel报错！");
}
return retList;
}

public static String getStringVal(HSSFCell cell) {
switch (cell.getCellType()) {
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue() ? "true" : "false";
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
return cell.getCellFormula();
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
return cell.getStringCellValue();
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
default:
return "";
}
}
}