我们常常需要在web中导出excel。
这里给出一个方法,采用poi的框架,生成excel。用response打出到web页面。
/**
* <pre>
* 导出excel工具方法
*
* @author kanpiaoxue
* date 2011-09-02
* @param response
* @param datas
* 数据
* @param sheetName
* sheet的名字
*
* [备注] 默认带有excel导出的名字。就是当日的日期和时间的组合:2011-09-02_08_37_35.xls
* 需要 org.apache.poi 的jar包
× google的 guava.jar
* </pre>
*/
public static void exportExcelUtil(HttpServletResponse response,
List<List<String>> datas, String sheetName) {
checkNotNull(response);
checkNotNull(datas);
checkArgument(!StringUtils.isEmpty(sheetName),
"sheetName is empty or null!");
class ExcelTool {
public HSSFCellStyle getTitleStyle(HSSFWorkbook wb) {
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 标题居中对齐
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setWrapText(true);
return style;
}
private HSSFCellStyle getStringStyle(HSSFWorkbook wb) {
// create cell style
HSSFCellStyle style = wb.createCellStyle();
// set the style of cell
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 数据右对齐
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setWrapText(true);
return style;
}
}
ExcelTool excelTool = new ExcelTool();
// create the excel work book
HSSFWorkbook wb = new HSSFWorkbook();
// create the sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// get the style of title cell
HSSFCellStyle titleStyle = excelTool.getTitleStyle(wb);
HSSFCellStyle dataStyle = excelTool.getStringStyle(wb);
int i = 0;
for (List<String> innerList : datas) {
HSSFCellStyle tmpStyle = dataStyle;
HSSFRow row = sheet.createRow(i);
if (i == 0) {// table header
row.setHeight((short) 500);
tmpStyle = titleStyle;
}
int j = 0;
for (String str : innerList) {
HSSFCell cell = row.createCell(j);
cell.setCellStyle(tmpStyle);
cell.setCellValue(new HSSFRichTextString(str));
sheet.setColumnWidth(j, 6000);
j++;
}
i++;
}
response.reset();
response.setContentType("application/vnd.ms-excel; charset=utf8");
response.setHeader("Content-Disposition",
"attachment; filename="
+ XPDateUtils.formatDateSecondLongPattern(new Date())
.replaceAll("-", "_").replaceAll(":", "_")
.replaceAll(" ", "_") + ".xls");
OutputStream out = null;
try {
out = response.getOutputStream();
wb.write(out);
} catch (IOException e) {
LOGGER.error("Exception: download excel " + e.getMessage(), e);
} finally {
IOUtils.closeQuietly(out);
}
}
为了日常使用,我把web导出Excel稍微的改装了一下,形成本地File文件的excel导出。
package org.kanpiaoxue.staticClass;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.charset.Charset;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.util.IOUtils;
import com.google.common.base.Preconditions;
import com.google.common.base.Splitter;
import com.google.common.collect.Lists;
import com.google.common.io.Files;
public class ExportExcell {
private static final Logger LOGGER = Logger.getLogger(ExportExcell.class);
public static void main(String[] args) throws Exception {
testStaticClass();
}
public static void testStaticClass() throws Exception {
String dir = System.getProperty("user.dir")
+ "/src/org/kanpiaoxue/staticClass/file" + File.separator;
String inputFileName = dir + "编辑1.txt";
String outputFileName = dir + "export.xls";
System.out.println(dir);
File file = new File(inputFileName);
List<String> lines = Files.readLines(file, Charset.forName("utf-8"));
List<List<String>> rs = Lists.newArrayList();
for (String line : lines) {
List<String> lst = Splitter.on(' ').omitEmptyStrings()
.trimResults().splitToList(line);
if (CollectionUtils.isEmpty(lst)) {
continue;
}
System.out.println(lst);
rs.add(lst);
}
exportExcelUtil(new FileOutputStream(new File(outputFileName)), rs,
"hello");
}
/**
* <pre>
* 导出excel工具方法
*
* @author kanpiaoxue
* date 2011-09-02
* @param response
* @param datas
* 数据
* @param sheetName
* sheet的名字
*
* [备注] 默认带有excel导出的名字。就是当日的日期和时间的组合:2011-09-02_08_37_35.xls
* 需要 org.apache.poi 的jar包
* × google的 guava.jar
* </pre>
*/
public static void exportExcelUtil(OutputStream fileOutputStream,
List<List<String>> datas, String sheetName) {
Preconditions.checkNotNull(fileOutputStream);
Preconditions.checkNotNull(datas);
Preconditions.checkArgument(!StringUtils.isEmpty(sheetName),
"sheetName is empty or null!");
class ExcelTool {
public HSSFCellStyle getTitleStyle(HSSFWorkbook wb) {
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 标题居中对齐
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setWrapText(true);
return style;
}
private HSSFCellStyle getStringStyle(HSSFWorkbook wb) {
// create cell style
HSSFCellStyle style = wb.createCellStyle();
// set the style of cell
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 数据右对齐
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setWrapText(true);
return style;
}
}
ExcelTool excelTool = new ExcelTool();
// create the excel work book
HSSFWorkbook wb = new HSSFWorkbook();
// create the sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// get the style of title cell
HSSFCellStyle titleStyle = excelTool.getTitleStyle(wb);
HSSFCellStyle dataStyle = excelTool.getStringStyle(wb);
int i = 0;
for (List<String> innerList : datas) {
HSSFCellStyle tmpStyle = dataStyle;
HSSFRow row = sheet.createRow(i);
if (i == 0) {// table header
row.setHeight((short) 500);
tmpStyle = titleStyle;
}
int j = 0;
for (String str : innerList) {
HSSFCell cell = row.createCell(j);
cell.setCellStyle(tmpStyle);
cell.setCellValue(new HSSFRichTextString(str));
sheet.setColumnWidth(j, 6000);
j++;
}
i++;
}
try {
wb.write(fileOutputStream);
} catch (IOException e) {
LOGGER.error("Exception: download excel " + e.getMessage(), e);
} finally {
IOUtils.closeQuietly(fileOutputStream);
}
}
public static String formatDateSecondLongPattern(Date date) {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return format.format(date);
}
}
读取Excel
/**
* <pre>
* @param fileName
* @return 读取Excel的内容
* </pre>
*/
public static List<List<String>> readExcel(String fileName)
throws Exception {
// EXCEL_SUFFIX
checkArgument(!Strings.isNullOrEmpty(fileName),
"fileName is null or empty!");
fileName = fileName.trim();
checkArgument(fileName.endsWith(EXCEL_SUFFIX), "fileName not endsWith "
+ EXCEL_SUFFIX);
InputStream inputStream = new FileInputStream(new File(fileName));
return readExcel(inputStream);
}
/**
* <pre>
* @param fileName
* @return 读取Excel的内容
* </pre>
*/
public static List<List<String>> readExcel(InputStream inputStream)
throws Exception {
List<List<String>> rsList = newArrayList();
try {
POIFSFileSystem fs = new POIFSFileSystem(inputStream);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
for (int i = 0; i <= rowNum; i++) {
List<String> rowDatas = newArrayList();
HSSFRow row = sheet.getRow(i);
int colNum = row.getPhysicalNumberOfCells();
for (int j = 0; j < colNum; j++) {
rowDatas.add(getCellFormatValue(row.getCell(j)));
}
rsList.add(rowDatas);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
IOUtils.closeQuietly(inputStream);
}
return rsList;
}
/**
* 根据HSSFCell类型设置数据
*
* @param cell
* @return
*/
private static String getCellFormatValue(HSSFCell cell) {
String cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则,转化为Data格式
Date date = cell.getDateCellValue();
cellvalue = XPDateUtils.formatDateSecondLongPattern(date);
}
// 如果是纯数字
else {
// 取得当前Cell的数值
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
}
// 如果当前Cell的Type为String
case HSSFCell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默认的Cell值
default:
cellvalue = "";
}
}
return cellvalue;
}