用HSSFWorkbook类导出EXCEL表格
来源:互联网 发布:个人官网源码 编辑:程序博客网 时间:2024/06/07 18:46
package com.ufgov.bigdata.export.action;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.opensymphony.xwork.ActionSupport;
import com.ufgov.bigdata.export.model.Head;
import com.ufgov.bigdata.export.model.HeadStyle;
@SuppressWarnings({ "rawtypes", "unchecked", "deprecation" })
public class ExportAction extends ActionSupport {
private static final long serialVersionUID = 1L;
// 创建一个新的Excel
HSSFWorkbook workBook = null;
// 创建sheet页
HSSFSheet sheet = null;
Map map = new HashMap();
/**
* 保存查询结果至excel中,并下载Excel
*
* @param req
* @param res
* @param list
* 查询结果
*/
public void saveDataToExcel(HttpServletRequest req, HttpServletResponse resp, List list) {
String config = req.getParameter("config");
// String code = req.getParameter("code").split("\\(")[0];
JSONObject jsonMap = JSONObject.fromObject(config);
List<Head> headerList = JSONArray.toList(JSONArray.fromObject(jsonMap.getString("header")), Head.class);
JSONObject headCss = JSONObject.fromObject(JSONObject.fromObject(jsonMap.getString("config"))
.getString("headerCss").replaceAll("-", ""));
HeadStyle headStyle = (HeadStyle) JSONObject.toBean(headCss, HeadStyle.class);
req.setAttribute("list", list);// 列表
init(headerList, headStyle);
initBody(list, headerList);// 表体初始化
String name = (String) req.getAttribute("name");
outPutExcel(resp,name);// 输出Excel
}
private void init(List<Head> headerList, HeadStyle headStyle) {
// 创建一个新的Excel
workBook = new HSSFWorkbook();
// 创建sheet页
sheet = workBook.createSheet();
// 设置表名
// workBook.setSheetName(0, code);
// 第0行 列头
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 420);
for (int i = 0; i < headerList.size(); i++) {
Head head = headerList.get(i);
String cellValue = head.getText();
int width = head.getWidth();
map.put(i, head.getType());
sheet.setColumnWidth((short) i, (short) width * 35);
createHeadCell(row, (short) i, headStyle).setCellValue(cellValue);
}
}
/**
* 创建Header单元格
*
* @return headCell对象
*/
private HSSFCell createHeadCell(HSSFRow row, short colNum, HeadStyle headStyle) {
HSSFCell cell = createCell(row, colNum);
cell.setCellStyle(getHeaderStyle(headStyle));
return cell;
}
/**
* 创建单元格 param sheet : sheet页 param rowNum : 行号 param colNum : 列号
*
* @return cell对象
*/
private HSSFCell createCell(HSSFRow row, int colNum) {
HSSFCell cell = row.createCell(colNum);
return cell;
}
/**
* 设置header单元格默认格式:居中、灰色、12号字、粗体
*
* @return 格式对象
*/
private HSSFCellStyle getHeaderStyle(HeadStyle headStyle) {
HSSFCellStyle style = workBook.createCellStyle();
HSSFFont font = workBook.createFont();
setBorder(style);
style.setAlignment(getTextAlign(headStyle.getTextalign()));
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 单元格填充:使用前景颜色
style.setFillForegroundColor(getFillgroundColor(headStyle.getBackgroundcolor()));//
font.setFontHeightInPoints((short) 11);// 设置字体高度:11号
// HSSFColor.WHITE.index
font.setColor(getColor(headStyle.getColor()));
// 把字体应用到当前的样式
style.setFont(font);
return style;
}
private short getTextAlign(String textAlign) {
short textAlignIndex = 0;
if ("".equalsIgnoreCase(textAlign) || textAlign.isEmpty()) {
return HSSFCellStyle.ALIGN_CENTER;
}
if ("center".equalsIgnoreCase(textAlign)) {
textAlignIndex = HSSFCellStyle.ALIGN_CENTER;
} else if ("left".equalsIgnoreCase(textAlign)) {
textAlignIndex = HSSFCellStyle.ALIGN_LEFT;
} else if ("right".equalsIgnoreCase(textAlign)) {
textAlignIndex = HSSFCellStyle.ALIGN_RIGHT;
}
return textAlignIndex;
}
private short getFillgroundColor(String backgroundcolor) {
if (backgroundcolor.startsWith("#")) {
int color0 = 0;
int color1 = 0;
int color2 = 0;
if (backgroundcolor.length() == 7) {
color0 = Integer.parseInt(backgroundcolor.substring(1, 3), 16);
color1 = Integer.parseInt(backgroundcolor.substring(3, 5), 16);
color2 = Integer.parseInt(backgroundcolor.substring(5, 7), 16);
} else if (backgroundcolor.length() == 4) {
color0 = Integer.parseInt(backgroundcolor.substring(1, 2) + backgroundcolor.substring(1, 2), 16);
color1 = Integer.parseInt(backgroundcolor.substring(2, 3) + backgroundcolor.substring(2, 3), 16);
color2 = Integer.parseInt(backgroundcolor.substring(3, 4) + backgroundcolor.substring(3, 4), 16);
}
HSSFPalette palette = workBook.getCustomPalette();
palette.setColorAtIndex((short) 60, (byte) color0, (byte) color1, (byte) color2);
}
return 60;
}
private short getColor(String color) {
if (color.startsWith("#")) {
int color0 = 0;
int color1 = 0;
int color2 = 0;
if (color.length() == 7) {
color0 = Integer.parseInt(color.substring(1, 3), 16);
color1 = Integer.parseInt(color.substring(3, 5), 16);
color2 = Integer.parseInt(color.substring(5, 7), 16);
} else if (color.length() == 4) {
color0 = Integer.parseInt(color.substring(1, 2) + color.substring(1, 2), 16);
color1 = Integer.parseInt(color.substring(2, 3) + color.substring(2, 3), 16);
color2 = Integer.parseInt(color.substring(3, 4) + color.substring(3, 4), 16);
}
HSSFPalette palette = workBook.getCustomPalette();
palette.setColorAtIndex((short) 61, (byte) color0, (byte) color1, (byte) color2);
}
return 61;
}
/**
* 设置单元格默认格式:边框样式
*
* @return 格式对象
*/
private HSSFCellStyle setBorder(HSSFCellStyle style) {
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
return style;
}
/**
* 初始化表体
*
* @param list
* 查询结果
*/
private void initBody(List list, List<Head> headerList) {
// 定义样式style:居左、自动换行,style1:居右,style2:居中,style3:居左
HSSFCellStyle style = workBook.createCellStyle();
style.setWrapText(false);// 不自动换行
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
HSSFCellStyle style1 = workBook.createCellStyle();
// style1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
style1.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
HSSFCellStyle style2 = workBook.createCellStyle();
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCellStyle style3 = workBook.createCellStyle();
style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
for (int i = 0; i < list.size(); i++) {
HSSFRow row = sheet.createRow(i + 1);
int colNum = 0;
Map recordMap = (Map) list.get(i);
// 根据表头循环,写入对应单元格数据,循环一次写入一行数据
for (Head head : headerList) {
HSSFCell cell;
String dataIndex = head.getDataIndex();
String type = (String) map.get(colNum);
if ("int".equalsIgnoreCase(type)) {
cell = createBodyCell(style1, row, (short) colNum);
} else {
cell = createBodyCell(style3, row, (short) colNum);
}
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
row.setHeight((short) 380);
Object ssString = recordMap.get(dataIndex.toLowerCase());
if (ssString != null) {
cell.setCellValue(ssString.toString());
} else {
cell.setCellValue("");
}
colNum++;
}
}
}
/**
* 创建Body单元格
*
* @return Body对象
*/
private HSSFCell createBodyCell(HSSFCellStyle style, HSSFRow row, short colNum) {
HSSFCell cell = createCell(row, colNum);
cell.setCellStyle(getBodyStyle(style, colNum));
return cell;
}
/**
* 设置body单元格默认格式
*
* @return 格式对象
*/
private HSSFCellStyle getBodyStyle(HSSFCellStyle style, short colNum) {
setBorder(style);
return style;
}
/**
* 输出Excel
*/
private void outPutExcel(HttpServletResponse response,String name) {
// 通过Response把数据以Excel格式保存
response.reset();
response.setContentType("application/msexcel;charset=UTF-8");
try {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
String fileName = "导出" + name + df.format(new Date());
response.addHeader("Content-Disposition",
"attachment;filename=" + new String((fileName + ".xls").getBytes("GBK"), "ISO8859_1"));
// 定义输出类型
OutputStream out = response.getOutputStream();
workBook.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.opensymphony.xwork.ActionSupport;
import com.ufgov.bigdata.export.model.Head;
import com.ufgov.bigdata.export.model.HeadStyle;
@SuppressWarnings({ "rawtypes", "unchecked", "deprecation" })
public class ExportAction extends ActionSupport {
private static final long serialVersionUID = 1L;
// 创建一个新的Excel
HSSFWorkbook workBook = null;
// 创建sheet页
HSSFSheet sheet = null;
Map map = new HashMap();
/**
* 保存查询结果至excel中,并下载Excel
*
* @param req
* @param res
* @param list
* 查询结果
*/
public void saveDataToExcel(HttpServletRequest req, HttpServletResponse resp, List list) {
String config = req.getParameter("config");
// String code = req.getParameter("code").split("\\(")[0];
JSONObject jsonMap = JSONObject.fromObject(config);
List<Head> headerList = JSONArray.toList(JSONArray.fromObject(jsonMap.getString("header")), Head.class);
JSONObject headCss = JSONObject.fromObject(JSONObject.fromObject(jsonMap.getString("config"))
.getString("headerCss").replaceAll("-", ""));
HeadStyle headStyle = (HeadStyle) JSONObject.toBean(headCss, HeadStyle.class);
req.setAttribute("list", list);// 列表
init(headerList, headStyle);
initBody(list, headerList);// 表体初始化
String name = (String) req.getAttribute("name");
outPutExcel(resp,name);// 输出Excel
}
private void init(List<Head> headerList, HeadStyle headStyle) {
// 创建一个新的Excel
workBook = new HSSFWorkbook();
// 创建sheet页
sheet = workBook.createSheet();
// 设置表名
// workBook.setSheetName(0, code);
// 第0行 列头
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 420);
for (int i = 0; i < headerList.size(); i++) {
Head head = headerList.get(i);
String cellValue = head.getText();
int width = head.getWidth();
map.put(i, head.getType());
sheet.setColumnWidth((short) i, (short) width * 35);
createHeadCell(row, (short) i, headStyle).setCellValue(cellValue);
}
}
/**
* 创建Header单元格
*
* @return headCell对象
*/
private HSSFCell createHeadCell(HSSFRow row, short colNum, HeadStyle headStyle) {
HSSFCell cell = createCell(row, colNum);
cell.setCellStyle(getHeaderStyle(headStyle));
return cell;
}
/**
* 创建单元格 param sheet : sheet页 param rowNum : 行号 param colNum : 列号
*
* @return cell对象
*/
private HSSFCell createCell(HSSFRow row, int colNum) {
HSSFCell cell = row.createCell(colNum);
return cell;
}
/**
* 设置header单元格默认格式:居中、灰色、12号字、粗体
*
* @return 格式对象
*/
private HSSFCellStyle getHeaderStyle(HeadStyle headStyle) {
HSSFCellStyle style = workBook.createCellStyle();
HSSFFont font = workBook.createFont();
setBorder(style);
style.setAlignment(getTextAlign(headStyle.getTextalign()));
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 单元格填充:使用前景颜色
style.setFillForegroundColor(getFillgroundColor(headStyle.getBackgroundcolor()));//
font.setFontHeightInPoints((short) 11);// 设置字体高度:11号
// HSSFColor.WHITE.index
font.setColor(getColor(headStyle.getColor()));
// 把字体应用到当前的样式
style.setFont(font);
return style;
}
private short getTextAlign(String textAlign) {
short textAlignIndex = 0;
if ("".equalsIgnoreCase(textAlign) || textAlign.isEmpty()) {
return HSSFCellStyle.ALIGN_CENTER;
}
if ("center".equalsIgnoreCase(textAlign)) {
textAlignIndex = HSSFCellStyle.ALIGN_CENTER;
} else if ("left".equalsIgnoreCase(textAlign)) {
textAlignIndex = HSSFCellStyle.ALIGN_LEFT;
} else if ("right".equalsIgnoreCase(textAlign)) {
textAlignIndex = HSSFCellStyle.ALIGN_RIGHT;
}
return textAlignIndex;
}
private short getFillgroundColor(String backgroundcolor) {
if (backgroundcolor.startsWith("#")) {
int color0 = 0;
int color1 = 0;
int color2 = 0;
if (backgroundcolor.length() == 7) {
color0 = Integer.parseInt(backgroundcolor.substring(1, 3), 16);
color1 = Integer.parseInt(backgroundcolor.substring(3, 5), 16);
color2 = Integer.parseInt(backgroundcolor.substring(5, 7), 16);
} else if (backgroundcolor.length() == 4) {
color0 = Integer.parseInt(backgroundcolor.substring(1, 2) + backgroundcolor.substring(1, 2), 16);
color1 = Integer.parseInt(backgroundcolor.substring(2, 3) + backgroundcolor.substring(2, 3), 16);
color2 = Integer.parseInt(backgroundcolor.substring(3, 4) + backgroundcolor.substring(3, 4), 16);
}
HSSFPalette palette = workBook.getCustomPalette();
palette.setColorAtIndex((short) 60, (byte) color0, (byte) color1, (byte) color2);
}
return 60;
}
private short getColor(String color) {
if (color.startsWith("#")) {
int color0 = 0;
int color1 = 0;
int color2 = 0;
if (color.length() == 7) {
color0 = Integer.parseInt(color.substring(1, 3), 16);
color1 = Integer.parseInt(color.substring(3, 5), 16);
color2 = Integer.parseInt(color.substring(5, 7), 16);
} else if (color.length() == 4) {
color0 = Integer.parseInt(color.substring(1, 2) + color.substring(1, 2), 16);
color1 = Integer.parseInt(color.substring(2, 3) + color.substring(2, 3), 16);
color2 = Integer.parseInt(color.substring(3, 4) + color.substring(3, 4), 16);
}
HSSFPalette palette = workBook.getCustomPalette();
palette.setColorAtIndex((short) 61, (byte) color0, (byte) color1, (byte) color2);
}
return 61;
}
/**
* 设置单元格默认格式:边框样式
*
* @return 格式对象
*/
private HSSFCellStyle setBorder(HSSFCellStyle style) {
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
return style;
}
/**
* 初始化表体
*
* @param list
* 查询结果
*/
private void initBody(List list, List<Head> headerList) {
// 定义样式style:居左、自动换行,style1:居右,style2:居中,style3:居左
HSSFCellStyle style = workBook.createCellStyle();
style.setWrapText(false);// 不自动换行
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
HSSFCellStyle style1 = workBook.createCellStyle();
// style1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
style1.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
HSSFCellStyle style2 = workBook.createCellStyle();
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCellStyle style3 = workBook.createCellStyle();
style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
for (int i = 0; i < list.size(); i++) {
HSSFRow row = sheet.createRow(i + 1);
int colNum = 0;
Map recordMap = (Map) list.get(i);
// 根据表头循环,写入对应单元格数据,循环一次写入一行数据
for (Head head : headerList) {
HSSFCell cell;
String dataIndex = head.getDataIndex();
String type = (String) map.get(colNum);
if ("int".equalsIgnoreCase(type)) {
cell = createBodyCell(style1, row, (short) colNum);
} else {
cell = createBodyCell(style3, row, (short) colNum);
}
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
row.setHeight((short) 380);
Object ssString = recordMap.get(dataIndex.toLowerCase());
if (ssString != null) {
cell.setCellValue(ssString.toString());
} else {
cell.setCellValue("");
}
colNum++;
}
}
}
/**
* 创建Body单元格
*
* @return Body对象
*/
private HSSFCell createBodyCell(HSSFCellStyle style, HSSFRow row, short colNum) {
HSSFCell cell = createCell(row, colNum);
cell.setCellStyle(getBodyStyle(style, colNum));
return cell;
}
/**
* 设置body单元格默认格式
*
* @return 格式对象
*/
private HSSFCellStyle getBodyStyle(HSSFCellStyle style, short colNum) {
setBorder(style);
return style;
}
/**
* 输出Excel
*/
private void outPutExcel(HttpServletResponse response,String name) {
// 通过Response把数据以Excel格式保存
response.reset();
response.setContentType("application/msexcel;charset=UTF-8");
try {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
String fileName = "导出" + name + df.format(new Date());
response.addHeader("Content-Disposition",
"attachment;filename=" + new String((fileName + ".xls").getBytes("GBK"), "ISO8859_1"));
// 定义输出类型
OutputStream out = response.getOutputStream();
workBook.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
0 0
- 用HSSFWorkbook类导出EXCEL表格
- HSSFWorkbook 导出表格到excel
- HSSFWorkbook生成Excel表格
- 关于HSSFWorkbook的使用(导出excel表格的创建)
- JAVA POI HSSFWorkbook 导出Excel表格超大数据量解决方案
- HSSFWorkbook导出Excel
- HSSFWorkbook导出EXCEL
- Excel导出(HSSFWorkbook )
- HSSFWorkbook导出Excel
- HSSFWorkbook 导出excel应用
- HSSFWorkbook导出excel
- HSSFWorkBooK用法 Excel导出详细
- poi使用HSSFWorkbook 导出excel
- hssfworkbook导出excel的问题
- HSSFWorkBook读取excel表格获取数据。
- 用PHPEXCEL类库导出EXCEL表格
- 用PHPEXCEL类库导出EXCEL表格
- poi导出Excel,HSSFWorkbook与XSSFWorkbook区别
- 第七周项目5—排队看病模拟
- Oracle-12514错误发生的几种原因
- linux内核优化
- Android中dip、dp、sp、pt和px的区别
- 第七周 建立顺序环形队列算法库
- 用HSSFWorkbook类导出EXCEL表格
- org.apache.ibatis.builder.IncompleteElementException: Could not find result map com.gobuy.order.mapp
- 第四周-项目四 建设双链表算法库
- 详解SQL删除之truncate 、delete与drop区别
- Error:Unable to start the daemon process. This problem might be caused by incorrect configuration of
- 建立链算法库
- 网络编程(概念)自己理解
- 适用memcached的业务场景
- SQLServer学习笔记 --- sql server2008的数据类型