导出Excel(简易好用,附源码)
-
导出效果
-
用到的jar包(2003版每个sheet只能存65536行)
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
- 用到的jar包(2007以上版每个sheet只能存bai1048576行)
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
- 前台
<html>
<head>
<title>Title</title>
</head>
<body>
<form id ="form1" method="post">
<input type = "hidden" name= 'name'/>
</form>
<a href="###" onclick="exportExcel()">导出Excel</a>
</body>
<script>
//导出Excel
function exportExcel() {
form1.action = "<%=basePath%>/stu/exportExcel";
form1.submit();
}
</script>
</html>
- 1.导出controller
//导出excel
@RequestMapping("/exportExcel")
public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
try {
String excelName="导出的EXCEL名字";
String sheetName = "导出的SHEET名字";
String[][] headers;//导出的表格的表头
String[][] data = new String[2][4];//需要导出的数据
headers = new String[1][6];//表头数据
headers[0] = new String[]{"序号","姓名", "年龄","职业"};
data[0][0]="1";
data[0][1]="小明";
data[0][2]="22";
data[0][3]="游戏主播";
data[1][0]="2";
data[1][1]="小虎";
data[1][2]="19";
data[1][3]="吃货主播";
//导出 Excel
ExcelUtils.export(excelName, sheetName,headers, null, null, null, data,false, request, response);
}catch (Exception e){
e.printStackTrace();
}
}
- 2.ExcelUtils工具类(2003)
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtils {
//excel默认宽度;
private static int width = 256*14;
//默认字体
private static String excelfont = "微软雅黑";
/**
*
* @param excelName 导出的EXCEL名字
* @param sheetName 导出的SHEET名字 当前sheet数目只为1
* @param headers 导出的表格的表头 String[row][column]
* 如果有需要合并单元格的,合并范围内左上角单元格赋值,其他设为""
* @param header_format 表头中需要合并单元格的起始终止坐标 ArrayList<int[4]>
* int[a,b,c,d]
* a 从哪一行
* b 到哪一行
* c 从那一列
* d 到哪一列
* @param data_format 导出数据的样式
* 1:String left;
* 2:String center
* 3:String right
* 4 int right
* 5:float ###,###.## right
* 6:number: #.00% 百分比 right
* @param widths 表格的列宽度 默认为 256*14
* @param data 数据集 String[row][column]
* @param sftjxh 是否在表头下的第一列增加序号
* @param response
* @throws IOException
*/
public static void export(String excelName, String sheetName, String[][] headers, ArrayList<int[]> header_format,
int[] data_format, int[] widths, String[][] data, boolean sftjxh,
HttpServletRequest request, HttpServletResponse response) throws IOException {
HttpSession session = request.getSession();
session.setAttribute("state", null);
//总共的列数
int count = sftjxh ? data[0].length+1 : data[0].length;
if(widths==null){
widths = new int[count];
for(int i=0;i<count;i++){
widths[i]=width;
}
}
if(data_format==null){
data_format = new int[count];
for(int i=0;i<count;i++){
data_format[i]=2;//默认居中
}
}
//设置文件名
String fileName = "";
if(StringUtils.isNotEmpty(excelName)){
fileName = excelName;
}
//创建一个工作薄
HSSFWorkbook wb = new HSSFWorkbook();
//创建一个sheet
HSSFSheet sheet = wb.createSheet(StringUtils.isNotEmpty(sheetName)?sheetName:"excel");
//创建表头,如果没有跳过
int headerrow = 0;
if(headers!=null){
//表头样式
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName(excelfont);
font.setFontHeightInPoints((short) 11);
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//填充表头数据
for(int i=0;i<headers.length;i++){
HSSFRow row = sheet.createRow(i);
for (int j = 0; j < count; j++) {
sheet.setColumnWidth(j,widths[j]);
HSSFCell cell = row.createCell(j);
cell.setCellValue(headers[i][j]);
cell.setCellStyle(style);
}
headerrow++;
}
//合并单元格
if(header_format != null){
int[] format;
for(int i=0;i<header_format.size();i++){
format = header_format.get(i);
sheet.addMergedRegion(new CellRangeAddress(format[0],format[1],format[2],format[3]));
}
}
}
//表格主体 解析list
if(data != null){
List styleList = new ArrayList();
for (int i = 0; i <count; i++) { //列数
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setFontName(excelfont);
font.setFontHeightInPoints((short) 10);
style.setFont(font);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
if(data_format[i]==1){
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
}else if(data_format[i]==2){
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
}else if(data_format[i]==3){
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
//int类型
}else if(data_format[i]==4){
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
//int类型
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
}else if(data_format[i]==5){
//float类型
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
}else if(data_format[i]==6){
//百分比类型
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
}
styleList.add(style);
}
String[] temp = null;//存放每行临时数据
for (int i = 0; i < data.length ; i++) { //行数
HSSFRow row = sheet.createRow(headerrow);
temp= data[i];
//如果需要添加序号列,第一列加序号,循环从1开始
if(sftjxh){
HSSFCell cell = row.createCell(0);
cell.setCellValue(i+1+"");
cell.setCellStyle((HSSFCellStyle)styleList.get(0));
}
for (int j = 0; j <temp.length; j++) { //列数
HSSFCell cell = null;
if(sftjxh){
cell = row.createCell(j+1);
}else{
cell = row.createCell(j);
}
if(StringUtils.isBlank(temp[j])){
cell.setCellValue("0");
}else if(data_format[j]==4){
//int
cell.setCellValue(Long.valueOf(temp[j]+"").longValue());
}else if(data_format[j]==5|| data_format[j]==6){
//float
cell.setCellValue((Double.valueOf(temp[j]+"")).doubleValue());
}else {
cell.setCellValue(temp[j]+"");
}
if(sftjxh){
cell.setCellStyle((HSSFCellStyle)styleList.get(j+1));
}else{
cell.setCellStyle((HSSFCellStyle)styleList.get(j));
}
}
headerrow++;
}
}
fileName=fileName+".xls";
String filename = "";
try{
filename =encodeChineseDownloadFileName(request,fileName);
}catch(Exception e){
e.printStackTrace();
}
response.setHeader("Content-disposition", filename);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename="+filename);
response.setHeader("Pragma", "No-cache");
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
session.setAttribute("state", "open");
}
/**
* 对文件流输出下载的中文文件名进行编码 屏蔽各种浏览器版本的差异性
* @throws java.io.UnsupportedEncodingException
*/
public static String encodeChineseDownloadFileName(
HttpServletRequest request, String pFileName) throws Exception {
String filename = null;
String agent = request.getHeader("USER-AGENT");
if (null != agent){
if (-1 != agent.indexOf("Firefox")) {//Firefox
filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8"))))+ "?=";
}else if (-1 != agent.indexOf("Chrome")) {//Chrome
filename = new String(pFileName.getBytes(), "ISO8859-1");
} else {//IE7+
filename = java.net.URLEncoder.encode(pFileName, "UTF-8");
filename = filename.replace("+", "%20");
}
} else {
filename = pFileName;
}
return filename;
}
}
- 3.ExcelUtils工具类(2003以上)
import org.apache.commons.lang3.StringUtils;
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.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtils {
//excel默认宽度;
private static int width = 256*14;
//默认字体
private static String excelfont = "微软雅黑";
/**
*
* @param excelName 导出的EXCEL名字
* @param sheetName 导出的SHEET名字 当前sheet数目只为1
* @param headers 导出的表格的表头 String[row][column]
* 如果有需要合并单元格的,合并范围内左上角单元格赋值,其他设为""
* @param header_format 表头中需要合并单元格的起始终止坐标 ArrayList<int[4]>
* int[a,b,c,d]
* a 从哪一行
* b 到哪一行
* c 从那一列
* d 到哪一列
* @param data_format 导出数据的样式
* 1:String left;
* 2:String center
* 3:String right
* 4 int right
* 5:float ###,###.## right
* 6:number: #.00% 百分比 right
* @param widths 表格的列宽度 默认为 256*14
* @param data 数据集 String[row][column]
* @param sftjxh 是否在表头下的第一列增加序号
* @param response
* @throws IOException
*/
public static void export(String excelName, String sheetName, String[][] headers, ArrayList<int[]> header_format,
int[] data_format, int[] widths, String[][] data, boolean sftjxh,
HttpServletRequest request, HttpServletResponse response) throws IOException {
HttpSession session = request.getSession();
session.setAttribute("state", null);
//总共的列数
int count =0;
if(data == null){
count = 0;
} else {
count = sftjxh ? data[0].length+1 : data[0].length;
}
if(widths==null){
widths = new int[count];
for(int i=0;i<count;i++){
widths[i]=width;
}
}
if(data_format==null){
data_format = new int[count];
for(int i=0;i<count;i++){
data_format[i]=2;//默认居中
}
}
//设置文件名
String fileName = "";
if(StringUtils.isNotEmpty(excelName)){
fileName = excelName;
}
//计算有几个sheet
int sheetNum = 1;
if(data!=null){
sheetNum = (int)Math.ceil((double)data.length/1000000);
}
//创建一个工作薄
SXSSFWorkbook wb = new SXSSFWorkbook(500);
for(int x=0;x<sheetNum;x++) {
//创建一个sheet
Sheet sheet = wb.createSheet(StringUtils.isNotEmpty(sheetName)?(sheetNum>1?sheetName+(x+1):sheetName):"excel");
//创建表头,如果没有跳过
int headerrow = 0;
if(headers!=null){
//表头样式
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setBold(true);
font.setFontName(excelfont);
font.setFontHeightInPoints((short) 11);
style.setFont(font);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
//填充表头数据
for(int i=0;i<headers.length;i++){
Row row = sheet.createRow(i);
for (int j = 0; j < count; j++) {
sheet.setColumnWidth(j,widths[j]);
Cell cell = row.createCell(j);
cell.setCellValue(headers[i][j]);
cell.setCellStyle(style);
}
headerrow++;
}
//合并单元格
if(header_format != null){
int[] format;
for(int i=0;i<header_format.size();i++){
format = header_format.get(i);
sheet.addMergedRegion(new CellRangeAddress(format[0],format[1],format[2],format[3]));
}
}
}
//表格主体 解析list
if(data != null){
List styleList = new ArrayList();
for (int i = 0; i <count; i++) { //列数
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontName(excelfont);
font.setFontHeightInPoints((short) 10);
style.setFont(font);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
if(data_format[i]==1){
style.setAlignment(HorizontalAlignment.LEFT);
}else if(data_format[i]==2){
style.setAlignment(HorizontalAlignment.CENTER);
}else if(data_format[i]==3){
style.setAlignment(HorizontalAlignment.RIGHT);
//int类型
}else if(data_format[i]==4){
style.setAlignment(HorizontalAlignment.RIGHT);
//int类型
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
}else if(data_format[i]==5){
//float类型
style.setAlignment(HorizontalAlignment.RIGHT);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
}else if(data_format[i]==6){
//百分比类型
style.setAlignment(HorizontalAlignment.RIGHT);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
}
styleList.add(style);
}
int begin = x*1000000;
int end = (data.length-(begin+1000000))>0?(begin+1000000):data.length;
String[] temp = null;//存放每行临时数据
for (int i = begin; i < end ; i++) { //行数
Row row = sheet.createRow(headerrow);
temp= data[i];
//如果需要添加序号列,第一列加序号,循环从1开始
if(sftjxh){
Cell cell = row.createCell(0);
cell.setCellValue(i+1+"");
cell.setCellStyle((CellStyle)styleList.get(0));
}
for (int j = 0; j <temp.length; j++) { //列数
Cell cell = null;
if(sftjxh){
cell = row.createCell(j+1);
}else{
cell = row.createCell(j);
}
if(StringUtils.isBlank(temp[j])){
cell.setCellValue("");
}else if(data_format[j]==4){
//int
cell.setCellValue(Long.valueOf(temp[j]+"").longValue());
}else if(data_format[j]==5|| data_format[j]==6){
//float
cell.setCellValue((Double.valueOf(temp[j]+"")).doubleValue());
}else {
cell.setCellValue(temp[j]+"");
}
if(sftjxh){
cell.setCellStyle((CellStyle)styleList.get(j+1));
}else{
cell.setCellStyle((CellStyle)styleList.get(j));
}
}
headerrow++;
}
}
}
fileName=fileName+".xlsx";
String filename = "";
try{
filename =encodeChineseDownloadFileName(request,fileName);
}catch(Exception e){
e.printStackTrace();
}
// final String userAgent = request.getHeader("USER-AGENT");
// if(userAgent.indexOf( "MSIE")!=-1){//IE浏览器
// filename = URLEncoder.encode(fileName,"UTF8");
// }else if(userAgent.indexOf( "Mozilla")!=-1){//google,火狐浏览器
// filename = new String(fileName.getBytes(), "ISO8859-1");
// }else{
// filename = URLEncoder.encode(fileName,"UTF8");//其他浏览器
// }
response.setHeader("Content-disposition", filename);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename="+filename);
response.setHeader("Pragma", "No-cache");
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
session.setAttribute("state", "open");
}
/**
* 对文件流输出下载的中文文件名进行编码 屏蔽各种浏览器版本的差异性
* @throws java.io.UnsupportedEncodingException
*/
public static String encodeChineseDownloadFileName(
HttpServletRequest request, String pFileName) throws Exception {
String filename = null;
String agent = request.getHeader("USER-AGENT");
if (null != agent){
if (-1 != agent.indexOf("Firefox")) {//Firefox
filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8"))))+ "?=";
}else if (-1 != agent.indexOf("Chrome")) {//Chrome
filename = new String(pFileName.getBytes(), "ISO8859-1");
} else {//IE7+
filename = java.net.URLEncoder.encode(pFileName, "UTF-8");
filename = filename.replace("+", "%20");
}
} else {
filename = pFileName;
}
return filename;
}
}