导出数据为Excel文档
导出数据为Excel文档
工具类代码1:
package cn.xyt.util;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* 2019.05.31
* 支持大数据导出
*/
public class ExcelUtilNew {
private final static int MAX_ROW = 65535;//定义最大列数. excel 2003版最大值为MAX_ROW
public static String NO_DEFINE = "no_define";//未定义的字段
public static String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";//默认日期格式
public static int DEFAULT_COLOUMN_WIDTH = 17;
public static void exportExcel(String title, Map<String, String> headMap, JSONArray jsonArray, int colWidth, OutputStream out) {
Object o = jsonArray.get(0);
JSONObject json = (JSONObject) JSONObject.toJSON(o);
for (String key : json.keySet()) {
}
exportExcel(title, headMap, jsonArray, null, colWidth, out);
}
/**
* 导出Excel 97(.xls)格式 ，少量数据
*
* @param title 标题行
* @param headMap 属性-列名
* @param jsonArray 数据集
* @param datePattern 日期格式，null则用默认日期格式
* @param colWidth 列宽 默认 至少17个字节
* @param out 输出流
*/
public static void exportExcel(String title, Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
if (datePattern == null) datePattern = DEFAULT_DATE_PATTERN;
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
workbook.createInformationProperties();
workbook.getDocumentSummaryInformation().setCompany("*****公司");
SummaryInformation si = workbook.getSummaryInformation();
si.setAuthor("JACK"); //填加xls文件作者信息
si.setApplicationName("导出程序"); //填加xls文件创建程序信息
si.setLastAuthor("最后保存者信息"); //填加xls文件最后保存者信息
si.setComments("JACK is a programmer!"); //填加xls文件作者信息
si.setTitle("POI导出Excel"); //填加xls文件标题信息
si.setSubject("POI导出Excel");//填加文件主题信息
si.setCreateDateTime(new Date());
//表头样式
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 20);
titleFont.setBoldweight((short) 700);
titleStyle.setFont(titleFont);
// 列头样式
HSSFCellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
// 单元格样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont cellFont = workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);
// 生成一个(带标题)表格
HSSFSheet sheet = workbook.createSheet();
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
0, 0, 0, (short) 4, 2, (short) 6, 5));
// 设置注释内容
comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor("JACK");
//设置列宽
int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
int[] arrColWidth = new int[headMap.size()];
// 产生表格标题行,以及设置列宽
String[] properties = new String[headMap.size()];
String[] headers = new String[headMap.size()];
int ii = 0;
for (Iterator<String> iter = headMap.keySet().iterator(); iter
.hasNext(); ) {
String fieldName = iter.next();
properties[ii] = fieldName;
headers[ii] = fieldName;
int bytes = fieldName.getBytes().length;
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
ii++;
}
// 遍历集合数据，产生数据行
int rowIndex = 0;
for (Object obj : jsonArray) {
if (rowIndex == MAX_ROW || rowIndex == 0) {//此处定义数据最大存储量
if (rowIndex != 0) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示
HSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
titleRow.createCell(0).setCellValue(title);
titleRow.getCell(0).setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
HSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1
for (int i = 0; i < headers.length; i++) {
headerRow.createCell(i).setCellValue(headers[i]);
headerRow.getCell(i).setCellStyle(headerStyle);
}
rowIndex = 2;//数据内容从 rowIndex=2开始
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
HSSFRow dataRow = sheet.createRow(rowIndex);
for (int i = 0; i < properties.length; i++) {
HSSFCell newCell = dataRow.createCell(i);
Object o = jo.get(properties[i]);
String cellValue = "";
if (o == null) cellValue = "";
else if (o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
else cellValue = o.toString();
newCell.setCellValue(cellValue);
newCell.setCellStyle(cellStyle);
}
rowIndex++;
}
// 自动调整宽度
/*for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
}*/
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 导出Excel 2007 OOXML (.xlsx)格式
*
* @param title 标题行
* @param headMap 属性-列头
* @param jsonArray 数据集
* @param datePattern 日期格式，传null值则默认 年月日
* @param colWidth 列宽 默认 至少17个字节
* @param out 输出流
*/
public static void exportExcelX(String title, Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
if (datePattern == null) datePattern = DEFAULT_DATE_PATTERN;
// 声明一个工作薄
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
workbook.setCompressTempFiles(true);
//表头样式
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 20);
titleFont.setBoldweight((short) 700);
titleStyle.setFont(titleFont);
// 列头样式
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
// 单元格样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
Font cellFont = workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);
// 生成一个(带标题)表格
Sheet sheet = workbook.createSheet();
//设置列宽
int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
int[] arrColWidth = new int[headMap.size()];
// 产生表格标题行,以及设置列宽
String[] properties = new String[headMap.size()];
String[] headers = new String[headMap.size()];
int ii = 0;
for (Iterator<String> iter = headMap.keySet().iterator(); iter
.hasNext(); ) {
String fieldName = iter.next();
properties[ii] = fieldName;
headers[ii] = headMap.get(fieldName);
int bytes = fieldName.getBytes().length;
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
ii++;
}
// 遍历集合数据，产生数据行
int rowIndex = 0;
for (Object obj : jsonArray) {
if (rowIndex == MAX_ROW || rowIndex == 0) {
if (rowIndex != 0) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示
Row titleRow = sheet.createRow(0);//表头 rowIndex=0
titleRow.createCell(0).setCellValue(title);
titleRow.getCell(0).setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
Row headerRow = sheet.createRow(1); //列头 rowIndex =1
for (int i = 0; i < headers.length; i++) {
headerRow.createCell(i).setCellValue(headers[i]);
headerRow.getCell(i).setCellStyle(headerStyle);
}
rowIndex = 2;//数据内容从 rowIndex=2开始
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
Row dataRow = sheet.createRow(rowIndex);
for (int i = 0; i < properties.length; i++) {
Cell newCell = dataRow.createCell(i);
Object o = jo.get(properties[i]);
String cellValue = "";
if (o == null) cellValue = "";
else if (o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
else if (o instanceof Float || o instanceof Double)
cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
else cellValue = o.toString();
newCell.setCellValue(cellValue);
newCell.setCellStyle(cellStyle);
}
rowIndex++;
}
// 自动调整宽度
for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
}
try {
workbook.write(out);
workbook.dispose();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* Web 导出excel
*
* @param fileName 文件名
* @param title 首行标题
* @param headMap 行头
* @param ja 数据
* @param response
* @throws IOException
*/
public static void downloadExcelFile(String fileName, String title, Map<String, String> headMap, JSONArray ja, HttpServletResponse response) throws IOException {
try {
ByteArrayOutputStream os = new ByteArrayOutputStream();
ExcelUtilNew.exportExcelX(title, headMap, ja, null, 0, os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数，可以打开下载页面
response.reset();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
response.setContentLength(content.length);
ServletOutputStream outputStream = response.getOutputStream();
BufferedInputStream bis = new BufferedInputStream(is);
BufferedOutputStream bos = new BufferedOutputStream(outputStream);
byte[] buff = new byte[8192];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
bis.close();
bos.close();
outputStream.flush();
outputStream.close();
is.close();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* web导出excel
*
* @param fileName 文件名
* @param title 标题
* @param list 数据
* @param res
*/
public static void downloadExcelFile(String fileName, String title, List<Map<String, Object>> list, HttpServletResponse res) throws IOException {
Map<String, String> headMap = new LinkedHashMap<>(1);
for (String head : list.get(0).keySet()) {
headMap.put(head, head);
}
JSONArray jsonList = JSONArray.parseArray(JSON.toJSONString(list));
downloadExcelFile(fileName + "_" + getTodayStr(), title, headMap, jsonList, res);
}
public static String getTodayStr() {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
return df.format(new Date());
}
//测试2
public static void main(String[] args) throws IOException {
List<Map<String, Object>> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Map<String, Object> map = new LinkedHashMap<>();
map.put("1", 6);
map.put("2", 6);
map.put("3", 6);
list.add(map);
}
JSONArray objects = JSONArray.parseArray(JSON.toJSONString(list));
System.out.println(objects.toJSONString());
Map<String, String> headMap = new LinkedHashMap<String, String>();
headMap.put("1", "1");
headMap.put("2", "2");
headMap.put("3", "3");
String title = "测试2";
OutputStream outXlsx = new FileOutputStream("E://b.xlsx");
System.out.println("正在导出xlsx....");
Date d2 = new Date();
ExcelUtilNew.exportExcelX(title, headMap, objects, null, 0, outXlsx);
System.out.println("执行完毕" + (new Date().getTime() - d2.getTime()) + "ms");
outXlsx.close();
}
//测试
public static void main1(String[] args) throws IOException {
int count = 100000;
JSONArray ja = new JSONArray();
for (int i = 0; i < 100000; i++) {
Student s = new Student();
s.setName("POI" + i);
s.setAge(i);
s.setBirthday(new Date());
s.setHeight(i);
s.setWeight(i);
s.setSex(i / 2 == 0 ? false : true);
ja.add(s);
}
Map<String, String> headMap = new LinkedHashMap<String, String>();
headMap.put("name", "姓名");
headMap.put("age", "年龄");
headMap.put("birthday", "生日");
headMap.put("height", "身高");
headMap.put("weight", "体重");
headMap.put("sex", "性别");
String title = "测试";
/*
OutputStream outXls = new FileOutputStream("E://a.xls");
System.out.println("正在导出xls....");
Date d = new Date();
ExcelUtil.exportExcel(title,headMap,ja,null,outXls);
System.out.println("共"+count+"条数据,执行"+(new Date().getTime()-d.getTime())+"ms");
outXls.close();*/
//
OutputStream outXlsx = new FileOutputStream("E://b.xlsx");
System.out.println("正在导出xlsx....");
Date d2 = new Date();
ExcelUtilNew.exportExcelX(title, headMap, ja, null, 0, outXlsx);
System.out.println("共" + count + "条数据,执行" + (new Date().getTime() - d2.getTime()) + "ms");
outXlsx.close();
}
}
class Student {
private String name;
private int age;
private Date birthday;
private float height;
private double weight;
private boolean sex;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public float getHeight() {
return height;
}
public void setHeight(float height) {
this.height = height;
}
public double getWeight() {
return weight;
}
public void setWeight(double weight) {
this.weight = weight;
}
public boolean isSex() {
return sex;
}
public void setSex(boolean sex) {
this.sex = sex;
}
public void setAge(Integer age) {
this.age = age;
}
}
此代码可用,运行main方法出现的异常:java.lang.NoClassDefFoundError: javax/servlet/ServletOutputStream引入下面包即可,在启动tomcat部署运行程序时注解此包
<!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
<scope>provided</scope>
</dependency>
工具类代码2:
此代码作为参考学习,因其不再符合导出大数据的需求,以后不再使用.
package cn.xyt.util;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
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.HSSFFont;
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.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
//import org.apache.poi.xssf.usermodel.XSSFRow;
//import org.apache.poi.xssf.usermodel.XSSFSheet;
//import org.apache.poi.xssf.usermodel.XSSFWorkbook;
@SuppressWarnings("deprecation")
public class ExcelUtil{
private static final Log log = LogFactory.getLog(ExcelUtil.class);
/**
* 创建excel
* @param res
* @param list
* @param fileName
* @throws IOException
*/
public static void createExcel(HttpServletResponse res,List<Map<String, Object>> list,String fileName) throws IOException {
try {
fileName = new String(fileName.getBytes(), "ISO-8859-1");
} catch (UnsupportedEncodingException e) {
log.error(e);
}
res.setCharacterEncoding("utf-8");
res.reset(); // 清空数据
res.setContentType("applicatoin/octet-stream;charset=utf-8");
res.addHeader("Content-Disposition", "attachment; filename=\""+ fileName + "\";");
// 创建Excel的工作书册 Workbook,对应到一个excel文档
HSSFWorkbook wb = new HSSFWorkbook();
// 创建Excel的工作sheet,对应到一个excel文档的tab
HSSFSheet sheet = wb.createSheet("sheet1");
HSSFCellStyle titleStyle = wb.createCellStyle();
HSSFCellStyle contentStyle = wb.createCellStyle();
// 居中
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置字体大小
HSSFFont font = wb.createFont();
font.setFontName("楷书");
font.setFontHeightInPoints((short) 15);
font.setBoldweight((short)3);
titleStyle.setFont(font);
HSSFFont contentfont = wb.createFont();
contentfont.setFontHeightInPoints((short) 11);
contentStyle.setFont(contentfont);
//设置自动换行
// titleStyle.setWrapText(true);
// contentStyle.setWrapText(true);
// 建立新行
HSSFRow titlerow = sheet.createRow(0);
titlerow.setHeightInPoints(30);
Map<String, Object> title = list.get(0);
int i=0;
for(String key: title.keySet()){
HSSFCell cell = titlerow.createCell(i);
sheet.setColumnWidth(i, 60*100);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(key);
cell.setCellStyle(titleStyle);
i++;
}
int j = 1;
for (Map<String, Object> map: list) {
HSSFRow contentrow = sheet.createRow(j);
j++;
contentrow.setHeightInPoints(20);
int jj =0;
for(String key: map.keySet()){
HSSFCell cell = contentrow.createCell(jj);
jj++;
cell.setCellType(Cell.CELL_TYPE_STRING);
String content = ""+map.get(key);
cell.setCellValue(content);
cell.setCellStyle(contentStyle);
}
}
wb.write(res.getOutputStream());
}
/**
* 生成并下载Excel
* @param res HTTP响应
* @param list Excel内数据
* @param titleArray 标题数组
* @param fileName 文件名
*/
public static void createExcel(HttpServletResponse res,List<List<String>> list,String[] titleArray,String fileName) {
try {
fileName = new String(fileName.getBytes(), "ISO-8859-1");
} catch (UnsupportedEncodingException e) {
log.error(e);
}
res.setCharacterEncoding("utf-8");
res.reset(); // 清空数据
res.setContentType("applicatoin/octet-stream;charset=utf-8");
res.addHeader("Content-Disposition", "attachment; filename=\""+ fileName + "\";");
// 创建Excel的工作书册 Workbook,对应到一个excel文档
HSSFWorkbook wb = new HSSFWorkbook();
// 创建Excel的工作sheet,对应到一个excel文档的tab
HSSFSheet sheet = wb.createSheet("sheet1");
HSSFCellStyle titleStyle = wb.createCellStyle();
HSSFCellStyle contentStyle = wb.createCellStyle();
// 居中
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置字体大小
HSSFFont font = wb.createFont();
font.setFontName("楷书");
font.setFontHeightInPoints((short) 15);
font.setBoldweight((short)3);
titleStyle.setFont(font);
HSSFFont contentfont = wb.createFont();
contentfont.setFontHeightInPoints((short) 11);
contentStyle.setFont(contentfont);
//设置自动换行
// titleStyle.setWrapText(true);
// contentStyle.setWrapText(true);
// 建立新行
HSSFRow titlerow = sheet.createRow(0);
titlerow.setHeightInPoints(30);
for(int i=0;i<titleArray.length;i++){
HSSFCell cell = titlerow.createCell(i);
sheet.setColumnWidth(i, 60 * 256);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(titleArray[i]);
cell.setCellStyle(titleStyle);
}
for (int i=0;i<list.size();i++) {
HSSFRow contentrow = sheet.createRow(i+1);
contentrow.setHeightInPoints(20);
List<String> li=list.get(i);
for(int j=0;j<li.size();j++){
HSSFCell cell = contentrow.createCell(j);
cell.setCellType(Cell.CELL_TYPE_STRING);
String content=li.get(j);
// try {
// content = new String(li.get(j).getBytes("UTF-8"), "GBK");
// } catch (UnsupportedEncodingException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
cell.setCellValue(content);
cell.setCellStyle(contentStyle);
}
}
try {
wb.write(res.getOutputStream());
} catch (IOException e) {
log.error(e);
}
}
/**
* 读取office2007之前的Excel文件（后缀为.xls）
* @param is
* @return
*/
public static List<Map<String, Object>> getxls(InputStream is) {
List<Map<String, Object>> list=new ArrayList<Map<String, Object>>();
try {
// 构造 XSSFWorkbook 对象，strPath 传入文件路径
HSSFWorkbook xwb = new HSSFWorkbook(is);
// 读取第一章表格内容
HSSFSheet sheet = xwb.getSheetAt(0);
// 定义 row、cell
HSSFRow row;
String cell;
List<String> title=new ArrayList<String>();
//输出表头信息
row = sheet.getRow(0);
if(row==null){
return list;
}
for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
// 通过 row.getCell(j).toString() 获取单元格内容，
cell =row.getCell(j).toString();
title.add(cell);
}
Map<String, Object> titlemap=new HashMap<String, Object>();
titlemap.put("title", title);
list.add(titlemap);
// 循环输出表格中的内容
for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
Map<String, Object> map=new HashMap<String, Object>();
map.put("row", (i+1));
for (int j = row.getFirstCellNum(); j < title.size(); j++) {
// 通过 row.getCell(j).toString() 获取单元格内容，
if(row.getCell(j)==null){
continue;
}
row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
cell=row.getCell(j).toString();
map.put(title.get(j), cell);
map.put(title.get(j), cell);
}
list.add(map);
}
} catch (Exception e) {
log.error(e);
}
return list;
}
/**
* 读取office2007以后（包括）的Excel文件（后缀为.xlsx）
* @param is
* @return
*/
// public static List<Map<String, Object>> getxlsx(InputStream is) {
// List<Map<String, Object>> list=new ArrayList<Map<String, Object>>();
// try {
// // 构造 XSSFWorkbook 对象，strPath 传入文件路径
// XSSFWorkbook xwb = new XSSFWorkbook(is);
// // 读取第一章表格内容
// XSSFSheet sheet = xwb.getSheetAt(0);
// // 定义 row、cell
// XSSFRow row;
// String cell;
// List<String> title=new ArrayList<String>();
// //输出表头信息
// row = sheet.getRow(0);
// if(row==null){
// return list;
// }
// for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
// // 通过 row.getCell(j).toString() 获取单元格内容，
// cell = row.getCell(j).toString();
// title.add(cell);
// }
// Map<String, Object> titlemap=new HashMap<String, Object>();
// titlemap.put("title", title);
// list.add(titlemap);
//
// // 循环输出表格中的内容
// for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
// row = sheet.getRow(i);
// Map<String, Object> map=new HashMap<String, Object>();
// map.put("row", (i+1));
// for (int j = row.getFirstCellNum(); j < title.size(); j++) {
// // 通过 row.getCell(j).toString() 获取单元格内容，
// if(row.getCell(j)==null){
// continue;
// }
// row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
// cell=row.getCell(j).toString();
// map.put(title.get(j), cell);
// }
// list.add(map);
// }
// } catch (Exception e) {
// e.printStackTrace();
// }
// return list;
// }
/**
* 生成并下载Excel（带合并单元格）
* @param res HTTP响应
* @param list Excel内数据
* @param titleArray 标题数组
* @param fileName 文件名
*/
public static void createMergeExcel(HttpServletResponse res,List<List<String>> list,String[] titleArray,String fileName) {
try {
fileName = new String(fileName.getBytes(), "ISO-8859-1");
} catch (UnsupportedEncodingException e) {
log.error(e);
}
res.setCharacterEncoding("utf-8");
res.reset(); // 清空数据
res.setContentType("applicatoin/octet-stream;charset=utf-8");
res.addHeader("Content-Disposition", "attachment; filename=\""+ fileName + "\";");
// 创建Excel的工作书册 Workbook,对应到一个excel文档
HSSFWorkbook wb = new HSSFWorkbook();
// 创建Excel的工作sheet,对应到一个excel文档的tab
HSSFSheet sheet = wb.createSheet("sheet1");
HSSFCellStyle titleStyle = wb.createCellStyle();
HSSFCellStyle contentStyle = wb.createCellStyle();
// 居中
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置字体大小
HSSFFont font = wb.createFont();
font.setFontName("楷书");
font.setFontHeightInPoints((short) 15);
font.setBoldweight((short)3);
titleStyle.setFont(font);
HSSFFont contentfont = wb.createFont();
contentfont.setFontHeightInPoints((short) 11);
contentStyle.setFont(contentfont);
//设置自动换行
// titleStyle.setWrapText(true);
// contentStyle.setWrapText(true);
// 建立新行
HSSFRow titlerow = sheet.createRow(0);
titlerow.setHeightInPoints(30);
for(int i=0;i<titleArray.length;i++){
HSSFCell cell = titlerow.createCell(i);
sheet.setColumnWidth(i, 50 * 256);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(titleArray[i]);
cell.setCellStyle(titleStyle);
}
int begin=1;
int end=1;
for (int i=0;i<list.size();i++) {
HSSFRow contentrow = sheet.createRow(begin);
contentrow.setHeightInPoints(20);
List<String> li=list.get(i);
//TODO 优化
for(int j=0;j<=3;j++){
int cnt=Integer.parseInt(li.get(3));
end=begin+(cnt-1);
//合并单元格（new Region(从第几行,(short)第几列,到第几行,(short)第几列)）行列下标都从0开始
sheet.addMergedRegion(new Region(begin,(short)j,end,(short)j));
HSSFCell cell = contentrow.createCell(j);
cell.setCellType(Cell.CELL_TYPE_STRING);
String content=li.get(j);
cell.setCellValue(content);
cell.setCellStyle(contentStyle);
}
String[] u=li.get(4).split("\r\n");
for(int j=0;j<u.length;j++){
String[] contentarr=u[j].split(",");
for(int z=0;z<contentarr.length;z++){
HSSFCell cell = contentrow.createCell(z+4);
cell.setCellType(Cell.CELL_TYPE_STRING);
String content=contentarr[z];
cell.setCellValue(content);
cell.setCellStyle(contentStyle);
}
contentrow = sheet.createRow(begin+j+1);
contentrow.setHeightInPoints(20);
}
begin=end+1;
}
try {
wb.write(res.getOutputStream());
} catch (IOException e) {
log.error(e);
}
}
}
posted on 2019-05-31 16:58 zongJianKun 阅读(338) 评论(0) 编辑 收藏 举报
