首页 > 代码库 > Extjs4.0+HSSFWorkbook+SpringMVC实现将数据库中的记录导出到本地Excel格式
Extjs4.0+HSSFWorkbook+SpringMVC实现将数据库中的记录导出到本地Excel格式
Extjs4.0+HSSFWorkbook+SpringMVC实现将数据库中的记录导出到本地Excel格式
自己封装的JS类Share.js
var Share = {};
Share.ExportByExcel = function(url) {
var appWindow = Share.getWin();
appWindow.open(url);
appWindow.focus();
};
调用的前端js
buttons : [{
text : ‘查询‘,
handler : querySettle
}/**, {
text : ‘转账‘,
handler : settleTransfer
}*/, {
text : ‘对账查询‘,
handler : settleQuery
}/**, {
text : ‘失败批量重转账‘,
handler : settleBatch
}*/, {
text : ‘重置查询条件‘,
handler : resetSettle
}, {
text : ‘导出Excel‘,
handler : function() {
var count = settleGrid.getStore().getCount();
var form = settleTopPanel.getForm();
var json = Ext.JSON.encode(form.getValues());
if (count > 0) {
var ids = getSelectedSettle();
if (ids == null) {
ids = "";
}
Share.ExportByExcel(ctx
+ "/exportSettleByExcel.htm?ids=" + ids
+ "&json=" + json);
} else {
Ext.MessageBox.alert("提示", ‘数据为空！‘);
}
}
}]
SpringMVC 的Controller类中
@RequestMapping("/exportSettleByExcel.htm")
public void exportSettleByExcel(@RequestParam String ids, @RequestParam String json, ModelMap map,HttpServletRequest request,
HttpServletResponse response) throws Exception {
logger.info("-------1------- exportSettleByExcel start!");
SettlementModel settlementModel = (SettlementModel) JSONCommonUtils.jsonToObject(json, SettlementModel.class);
List<SettleWaste> lisw = null;
if (settlementModel != null) {
String filename = "";
filename = "结算记录-" + settlementModel.getStartTime() + "到"
+ settlementModel.getEndTime();
settlementModel.setIds(ids);
lisw = settlementWasteService.querySettleWaste(settlementModel, ids);
if (lisw != null && lisw.size() > 0) {
HSSFWorkbook wb = SettleExcel.getHSSFWorkbook(lisw, filename);
PoiUtil.saveExcel(filename, request, response, wb);
}
}
}
SettleExcel.java封装的Excel定义类如下
package com.iboxpay.settlement.core.poi;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.ss.usermodel.Font;
import com.iboxpay.settlement.core.constant.SettleConstants;
import com.iboxpay.settlement.core.domain.SettleWaste;
import com.iboxpay.settlement.util.CommonUtil;
import com.iboxpay.settlement.util.PoiUtil;
import com.iboxpay.settlement.util.TimeUtil;
public class SettleExcel {
public static HSSFWorkbook getHSSFWorkbook(List<SettleWaste> lisw, String title) {
HSSFWorkbook wb = new HSSFWorkbook(); // 创建工作簿
HSSFSheet sheet = wb.createSheet(); // 创建工作表
sheet.setDefaultColumnWidth(20); // 设置每列默认宽度
PoiUtil.createTitle(wb, sheet, title, 14); // 创建标题
HSSFRow row = sheet.createRow(1);
PoiUtil.createTitleCell(wb, row, 0, "实体账号");
PoiUtil.createTitleCell(wb, row, 1, "户名");
PoiUtil.createTitleCell(wb, row, 2, "交易金额(单位为：元)");
PoiUtil.createTitleCell(wb, row, 3, "手续费");
PoiUtil.createTitleCell(wb, row, 4, "应付金额（转账:单位元）");
PoiUtil.createTitleCell(wb, row, 5, "交易笔数");
PoiUtil.createTitleCell(wb, row, 6, "支付状态");
PoiUtil.createTitleCell(wb, row, 7, "网银支付行名");
PoiUtil.createTitleCell(wb, row, 8, "出账银行");
PoiUtil.createTitleCell(wb, row, 9, "结算类型");
PoiUtil.createTitleCell(wb, row, 10, "商户号");
PoiUtil.createTitleCell(wb, row, 11, "商户名称");
PoiUtil.createTitleCell(wb, row, 12, "出账日期");
PoiUtil.createTitleCell(wb, row, 13, "复核状态");
PoiUtil.createTitleCell(wb, row, 14, "交易日期");
PoiUtil.createTitleCell(wb, row, 15, "操作日期");
PoiUtil.createTitleCell(wb, row, 16, "操作员ID");
PoiUtil.createTitleCell(wb, row, 17, "民生指令ID");
PoiUtil.createTitleCell(wb, row, 18, "账号类型");
PoiUtil.createTitleCell(wb, row, 19, "备注");
PoiUtil.createTitleCell(wb, row, 20, "转账状态码");
PoiUtil.createTitleCell(wb, row, 21, "转账类型");
PoiUtil.createTitleCell(wb, row, 22, "汇路");
PoiUtil.createTitleCell(wb, row, 23, "网银支付行号");
PoiUtil.createTitleCell(wb, row, 24, "联行行号");
PoiUtil.createTitleCell(wb, row, 25, "联行行名");
Font font = wb.createFont();
font.setFontName("楷体");
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
if (lisw != null) {
int j = 2;
for (int i = 0; i < lisw.size(); i++) {
SettleWaste settleWaste = lisw.get(i);
row = sheet.createRow(j);
PoiUtil.createStringCell(wb,style, row, 0, settleWaste.getAccntNo());
PoiUtil.createStringCell(wb,style, row, 1, settleWaste.getAccntName());
PoiUtil.createDataCell(wb,style, row, 2, CommonUtil.FormatAmount(settleWaste.getTrnAmount()));
PoiUtil.createDataCell(wb,style, row, 3, CommonUtil.FormatAmount(settleWaste.getCharge()));
PoiUtil.createDataCell(wb,style, row, 4, CommonUtil.FormatAmount(settleWaste.getAccntAmount()));
PoiUtil.createDataCell(wb,style, row, 5, settleWaste.getTrncnt()!=null?settleWaste.getTrncnt():0.0);
if (SettleConstants.SETTLE_STATUS_UNPAY.equals(settleWaste.getXferFlag())) {
PoiUtil.createStringCell(wb,style, row, 6, "成功");
} else if (SettleConstants.SETTLE_STATUS_SUCCESS.equals(settleWaste.getXferFlag())) {
PoiUtil.createStringCell(wb,style, row, 6, "支付成功");
} else if(SettleConstants.SETTLE_STATUS_FAIL.equals(settleWaste.getXferFlag())) {
PoiUtil.createStringCell(wb,style, row, 6, "失败");
}
else if(SettleConstants.SETTLE_STATUS_NOT_SURE.equals(settleWaste.getXferFlag())){
PoiUtil.createStringCell(wb,style, row, 6, "支付未确定");
}
else {
PoiUtil.createStringCell(wb,style, row, 6, settleWaste.getXferFlag());
}
PoiUtil.createStringCell(wb,style, row, 7, settleWaste.getNetPayName());
PoiUtil.createStringCell(wb,style, row, 8, settleWaste.getBankName());
PoiUtil.createStringCell(wb,style, row, 9, "T+" + settleWaste.getStlexday());
PoiUtil.createStringCell(wb,style, row, 10, settleWaste.getClearMerchNo());
PoiUtil.createStringCell(wb,style, row, 11, settleWaste.getClearMerchant());
PoiUtil.createStringCell(wb,style, row, 12, settleWaste.getSettleDateFn());
if("0".equals(settleWaste.getChckFlag())){
PoiUtil.createStringCell(wb,style, row, 13, "未经办");
}
else if("1".equals(settleWaste.getChckFlag())){
PoiUtil.createStringCell(wb,style, row, 13, "经办通过");
}
else if("2".equals(settleWaste.getChckFlag())){
PoiUtil.createStringCell(wb,style, row, 13, "复核不通过");
}
else {
PoiUtil.createStringCell(wb,style, row, 13, settleWaste.getChckFlag());
}
PoiUtil.createStringCell(wb,style, row, 14, settleWaste.getSettleDate());
PoiUtil.createStringCell(wb,style, row, 15, TimeUtil.dateToString(settleWaste.getXferDate(), "yyyy-MM-dd HH:mm:ss"));
PoiUtil.createStringCell(wb,style, row, 16, settleWaste.getUserId());
PoiUtil.createStringCell(wb,style, row, 17, settleWaste.getInstId());
if("1".equals(settleWaste.getAccntType())){
PoiUtil.createStringCell(wb,style, row, 18, "对公");
}
else if("1".equals(settleWaste.getAccntType())){
PoiUtil.createStringCell(wb,style, row, 18, "对私");
}
else if("1".equals(settleWaste.getAccntType())){
PoiUtil.createStringCell(wb,style, row, 18, "对私存折");
}
else {
PoiUtil.createStringCell(wb,style, row, 18, settleWaste.getAccntType());
}
PoiUtil.createStringCell(wb,style, row, 19, settleWaste.getReserved());
PoiUtil.createStringCell(wb,style, row, 20, settleWaste.getRspCode());
if("1".equals(settleWaste.getXferType())){
PoiUtil.createStringCell(wb,style, row, 21, "跨行转账");
}
else if("2".equals(settleWaste.getXferType())){
PoiUtil.createStringCell(wb,style, row, 21, "同行转账");
}
else {
PoiUtil.createStringCell(wb,style, row, 21, settleWaste.getXferType());
}
if("0".equals(settleWaste.getLocalFlag())){
PoiUtil.createStringCell(wb,style, row, 22, "同行本地");
}
else if("1".equals(settleWaste.getLocalFlag())){
PoiUtil.createStringCell(wb,style, row, 22, "同行异地");
}
else if("2".equals(settleWaste.getLocalFlag())){
PoiUtil.createStringCell(wb,style, row, 22, "小额");
}
else if("3".equals(settleWaste.getLocalFlag())){
PoiUtil.createStringCell(wb,style, row, 22, "大额");
}
else if("4".equals(settleWaste.getLocalFlag())){
PoiUtil.createStringCell(wb,style, row, 22, "上海同城");
}
else if("5".equals(settleWaste.getLocalFlag())){
PoiUtil.createStringCell(wb,style, row, 22, "网银互联");
}
else {
PoiUtil.createStringCell(wb,style, row, 22, settleWaste.getLocalFlag());
}
PoiUtil.createStringCell(wb,style, row, 23, settleWaste.getNetPayNo());
PoiUtil.createStringCell(wb,style, row, 24, settleWaste.getUnionNo());
PoiUtil.createStringCell(wb,style, row, 25, settleWaste.getUnionName());
j++;
}
}
return wb;
}
}
POUtil.java类如下(将HSSFWorkbook封装后的对象写到客户端)
package com.iboxpay.settlement.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.HashMap;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
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.HSSFDataFormat;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
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.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class PoiUtil {
private static final Logger logger = LoggerFactory
.getLogger(PoiUtil.class);
private static HashMap<Integer, CellStyle> numberCellStyles = null;
private static CellStyle textCellStyle = null;
private static int DEFAULT_SHEET_INDEX = 0;
// 创建标题行
public static void createTitle(HSSFWorkbook wb, HSSFSheet sheet, String title, int margeCount) {
// HSSFRow titleRow = sheet.createRow(0); // 行和列索引都是从0开始
// HSSFCell titleCell = titleRow.createCell(0);
// // 设置样式
// HSSFCellStyle style = wb.createCellStyle();
// Font f = wb.createFont();
// f.setFontName("黑体"); // 字体
// f.setFontHeight((short) 300); // 字高
// style.setFont(f);
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置居中
// titleCell.setCellStyle(style); // 关联样式
// titleCell.setCellValue(title); // 列内容
// // 合并单元格
// sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, margeCount));
createTitle(wb, sheet, title, 0, 0, 0, margeCount);
}
// 创建标题行
public static void createTitle(HSSFWorkbook wb, HSSFSheet sheet, String title, int firstRow, int lastRow,
int firstCol, int margeCount) {
HSSFRow titleRow = sheet.createRow(firstRow); // 行和列索引都是从0开始
HSSFCell titleCell = titleRow.createCell(firstCol);
// 设置样式
HSSFCellStyle style = wb.createCellStyle();
Font f = wb.createFont();
f.setFontName("黑体"); // 字体
f.setFontHeight((short) 300); // 字高
style.setFont(f);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置居中
titleCell.setCellStyle(style); // 关联样式
titleCell.setCellValue(title); // 列内容
// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, margeCount));
}
// 创建标题行
public static void createSecondTitle(HSSFWorkbook wb, HSSFSheet sheet, String title, int firstRow, int lastRow,
int firstCol, int margeCount) {
HSSFRow titleRow = sheet.createRow(firstRow); // 行和列索引都是从0开始
HSSFCell titleCell = titleRow.createCell(firstCol);
// 设置样式
HSSFCellStyle style = wb.createCellStyle();
Font f = wb.createFont();
f.setFontName("楷体");
style.setFont(f);
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 设置居中
titleCell.setCellStyle(style); // 关联样式
titleCell.setCellValue(title); // 列内容
// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, margeCount));
}
// 创建列
public static void createTitleCell(HSSFWorkbook wb, HSSFRow row, int column, String cellValue) {
HSSFCell cell = row.createCell(column);
HSSFCellStyle style = wb.createCellStyle();
Font f = wb.createFont();
// if (row.getRowNum() == 1) {
// f.setFontName("宋体");
// f.setFontHeight((short) 260);
// f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 设置粗体
// } else {
f.setFontName("楷体");
// }
style.setFont(f);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置居中
cell.setCellStyle(style);
cell.setCellValue(cellValue);
}
public static void createTitleCell(HSSFWorkbook wb,HSSFCellStyle style, HSSFRow row, int column, String cellValue) {
HSSFCell cell = row.createCell(column);
cell.setCellStyle(style);
cell.setCellValue(cellValue);
}
// 创建字符串列
public static void createStringCell(HSSFWorkbook wb, HSSFRow row, int column, String cellValue) {
HSSFCell cell = row.createCell(column);
HSSFCellStyle style = wb.createCellStyle();
Font f = wb.createFont();
f.setFontName("楷体");
style.setFont(f);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style);
cell.setCellValue(cellValue);
}
public static void createStringCell(HSSFWorkbook wb,HSSFCellStyle style, HSSFRow row, int column, String cellValue) {
HSSFCell cell = row.createCell(column);
cell.setCellStyle(style);
cell.setCellValue(cellValue);
}
// 创建数字列
public static void createDataCell(HSSFWorkbook wb, HSSFRow row, int column, int cellValue) {
HSSFCell cell = row.createCell(column);
HSSFCellStyle style = wb.createCellStyle();
Font f = wb.createFont();
f.setFontName("楷体");
style.setFont(f);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style);
cell.setCellValue((double) cellValue);
}
public static void createDataCell(HSSFWorkbook wb,HSSFCellStyle style, HSSFRow row, int column, int cellValue) {
HSSFCell cell = row.createCell(column);
// HSSFCellStyle style = wb.createCellStyle();
// Font f = wb.createFont();
//
// f.setFontName("楷体");
// style.setFont(f);
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style);
cell.setCellValue((double) cellValue);
}
// 创建数字列
public static void createDataCell(HSSFWorkbook wb, HSSFRow row, int column, String cellValue) {
HSSFCell cell = row.createCell(column);
HSSFCellStyle style = wb.createCellStyle();
Font f = wb.createFont();
f.setFontName("楷体");
style.setFont(f);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFDataFormat format = wb.createDataFormat();
style.setDataFormat(format.getFormat("@"));
cell.setCellStyle(style);
// double d = Double.valueOf(cellValue);
cell.setCellValue(new HSSFRichTextString(cellValue));
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
}
public static void createDataCell(HSSFWorkbook wb,HSSFCellStyle style, HSSFRow row, int column, String cellValue) {
HSSFCell cell = row.createCell(column);
// HSSFCellStyle style = wb.createCellStyle();
// Font f = wb.createFont();
// f.setFontName("楷体");
// style.setFont(f);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFDataFormat format = wb.createDataFormat();
style.setDataFormat(format.getFormat("@"));
cell.setCellStyle(style);
// double d = Double.valueOf(cellValue);
cell.setCellValue(new HSSFRichTextString(cellValue));
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
}
// 创建数字列
public static void createDataCell(HSSFWorkbook wb, HSSFRow row, int column, double cellValue) {
HSSFCell cell = row.createCell(column);
HSSFCellStyle style = wb.createCellStyle();
Font f = wb.createFont();
f.setFontName("楷体");
style.setFont(f);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style);
double d = Double.valueOf(cellValue);
cell.setCellValue(d);
}
public static void createDataCell(HSSFWorkbook wb,HSSFCellStyle style, HSSFRow row, int column, double cellValue) {
HSSFCell cell = row.createCell(column);
// HSSFCellStyle style = wb.createCellStyle();
// Font f = wb.createFont();
// f.setFontName("楷体");
// style.setFont(f);
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style);
double d = Double.valueOf(cellValue);
cell.setCellValue(d);
}
public static void writeNumber(Workbook workBook, int sheetIndex, int rowIndex, int colIndex, double value,
int scale) {
Cell cell = getCell(workBook, sheetIndex, rowIndex, colIndex);
HSSFCellStyle cellStyle = (HSSFCellStyle) getNumberCellStyle(workBook, scale);
cell.setCellStyle(cellStyle);
cell.setCellValue(value);
}
public void writeText(Workbook workBook, int sheetIndex, int rowIndex, int colIndex, String value) {
Cell cell = getCell(workBook, sheetIndex, rowIndex, colIndex);
HSSFCellStyle cellStyle = (HSSFCellStyle) getTextCellStyle(workBook);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
cell.setCellStyle(cellStyle);
cell.setCellValue(new HSSFRichTextString(value));
}
public void writeText(Workbook workBook, int rowIndex, int colIndex, String value) {
writeText(workBook, DEFAULT_SHEET_INDEX, rowIndex, colIndex, value);
}
public void writeTextByNo(Workbook workBook, int rowIndex, int colIndex, String value) {
writeText(workBook, DEFAULT_SHEET_INDEX, rowIndex - 1, colIndex - 1, value);
}
public void writeNumber(Workbook workBook, int rowIndex, int colIndex, int value, int scale) {
writeNumber(workBook, DEFAULT_SHEET_INDEX, rowIndex, colIndex, value, scale);
}
public void writeNumberByNo(Workbook workBook, int rowIndex, int colIndex, int value, int scale) {
writeNumber(workBook, DEFAULT_SHEET_INDEX, rowIndex - 1, colIndex - 1, value, scale);
}
private static Cell getCell(Workbook workBook, int sheetIndex, int rowIndex, int colIndex) {
// Sheet
Sheet sheet = null;
try {
sheet = workBook.getSheetAt(sheetIndex);
} catch (IllegalArgumentException ex) {
sheet = workBook.createSheet();
}
// Row
Row row = null;
row = sheet.getRow(rowIndex);
if (row == null) {
row = sheet.createRow(rowIndex);
}
// Cell
Cell cell = null;
cell = row.getCell(colIndex);
if (cell == null) {
cell = row.createCell(colIndex);
}
return cell;
}
private static CellStyle getNumberCellStyle(Workbook workBook, int scale) {
if (numberCellStyles == null) {
numberCellStyles = new HashMap<Integer, CellStyle>();
}
if (numberCellStyles.get(Integer.valueOf(scale)) == null) {
CellStyle numberCellStyle = workBook.createCellStyle();
StringBuilder zeroBd = new StringBuilder();
DataFormat format = workBook.createDataFormat();
zeroBd.append("0");
if (scale > 0) {
zeroBd.append(".");
for (int zCount = 0; zCount < scale; zCount++) {
zeroBd.append("0");
}
}
short doubleFormat = format.getFormat(zeroBd.toString());
numberCellStyle.setDataFormat(doubleFormat);
numberCellStyles.put(Integer.valueOf(scale), numberCellStyle);
return numberCellStyle;
} else {
return numberCellStyles.get(Integer.valueOf(scale));
}
}
private static CellStyle getTextCellStyle(Workbook workBook) {
if (textCellStyle != null) {
return textCellStyle;
} else {
return workBook.createCellStyle();
}
}
public static Workbook getWorkbook(File file) throws FileNotFoundException, IOException {
Workbook workBook = null;
try {
// 读取office 2007版本以上
workBook = new XSSFWorkbook(new FileInputStream(file));
} catch (Exception ex) {
// 读取office 2003版本以下
workBook = new HSSFWorkbook(new FileInputStream(file));
}
return workBook;
}
public static void saveExcel(String filename, HttpServletRequest request, HttpServletResponse response,
HSSFWorkbook wb) {
ServletOutputStream out = null;
try {
out = response.getOutputStream();
// response.setHeader("Content-Disposition", "inline; filename="
// + URLEncoder.encode(filename, "UTF-8"));
// response.setCharacterEncoding("UTF-8");
// response.setContentType("application/vnd.ms-excel");
if (request.getHeader("user-agent").indexOf("MSIE") != -1) {
filename = java.net.URLEncoder.encode(filename, "utf-8") + ".xls";
} else {
filename = new String(filename.getBytes("utf-8"), "iso-8859-1") + ".xls";
}
response.setHeader("Content-Disposition", "attachment;filename=" + filename);
response.setContentType("application/msexcel");
wb.write(out);
out.flush();// 清除缓存
out.close();
} catch (Exception e) {
logger.error("Eeception:saveExcel");
}
}
public static void saveExcel(Workbook wb,String filename, String version, HttpServletRequest request, HttpServletResponse response) {
ServletOutputStream out = null;
try {
out = response.getOutputStream();
// response.setHeader("Content-Disposition", "inline; filename="
// + URLEncoder.encode(filename, "UTF-8"));
// response.setCharacterEncoding("UTF-8");
// response.setContentType("application/vnd.ms-excel");
// if(Constants.EXCEL_2007.equals(version)){
// filename = filename + ".xlsx";
// }
// else{
// filename = filename + ".xls";
// }
//
// if (request.getHeader("user-agent").indexOf("MSIE") != -1) {
// filename = java.net.URLEncoder.encode(filename, "utf-8");// + ".xls";
// } else {
// filename = new String(filename.getBytes("utf-8"), "iso-8859-1");// + ".xls";
// }
//
// response.setHeader("Content-Disposition", "attachment;filename=" + filename);
// response.setContentType("application/msexcel");
//
// wb.write(out);
// out.flush();// 清除缓存
// out.close();
} catch (Exception e) {
logger.error("Eeception:saveExcel");
}
}
public static String readExcelContent(InputStream is) {
POIFSFileSystem fs = null;
XSSFWorkbook wb = null;
XSSFSheet sheet = null;
XSSFRow row = null;
String str = "";
try {
// fs = new POIFSFileSystem(is);
wb = new XSSFWorkbook(is);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
// 正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
// while (j < colNum) {
// // 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据
// // 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean
// // str += getStringCellValue(row.getCell((short) j)).trim() +
// // "-";
// str += getStringCellValue(row.getCell((short) j)).trim() + "";
// j++;
// }
// content.put(i, str);
// str = "";
// double d = row.getCell(0).getNumericCellValue();
// System.out.println("double=" + d);
DecimalFormat df = new DecimalFormat("#");// 转换成整型
// if (i == rowNum) {
// str += df.format(row.getCell(0).getNumericCellValue());
// } else {
// str += df.format(row.getCell(0).getNumericCellValue()) + ",";
// }
System.out.println(df.format(row.getCell(2).getNumericCellValue()));
if(i == 100){
break;
}
}
return str;
}
}
Extjs4.0+HSSFWorkbook+SpringMVC实现将数据库中的记录导出到本地Excel格式