导出EXcel工具类如下：
package com.yyw.utils;
import jxl.Sheet;
import jxl.Workbook;
import jxl.biff.DisplayFormat;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* Excel处理工具类
*/
public class ExcelUtils {
private static SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
/**
* Excel模板下载
* @param filePath
* @param response
* @param fileName
* @return
* @throws Exception
*/
public static boolean downLoadFile(String filePath,HttpServletResponse response,
String fileName)throws Exception {
File file = new File(filePath);
response.setContentType("application/msexcel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=\""
+ new String(fileName.getBytes("GB2312"), "ISO8859-1") + "\"");
response.setContentLength((int)file.length());
byte[] buffer = new byte[4096];//缓冲区
BufferedOutputStream output = null;
BufferedInputStream input = null;
try {
output = new BufferedOutputStream(response.getOutputStream());
input = new BufferedInputStream(new FileInputStream(file));
int n = -1;
//遍历，开始下载
while ((n = input.read(buffer, 0, 4096)) > -1) {
output.write(buffer, 0, n);
}
output.flush();
response.flushBuffer();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
throw new Exception("Excel模板下载异常："+e.getMessage());
}finally{
//关闭流
if (input != null){
input.close();
}
if (output != null){
output.close();
}
}
return true;
}
//控制Excel里面每个sheet单元最多有多少行
private static int MAX_ROWS = 50000;
/**
* 填充Excel各单元格的值
* @param <T>
* @param list 数据list
* @param os 输出流
* @param colNames 第一行列字段的中文名称
* @param colParams 各列对应对象的属性
* @param sheetName sheet的名称
*/
public static <T> void setExcel(List<T> list,OutputStream os,String[] colNames,String[] colParams,
String sheetName,Integer col)throws Exception{
try {
WritableWorkbook workbook = Workbook.createWorkbook(os);
//设定一个sheet里面只能放50000条数据
int num = list.size()/MAX_ROWS + 1;
for(int i=0;i<num;i++){
WritableSheet sheet = null;
if(StringUtil.isEmpty(sheetName)){
sheet = workbook.createSheet("sheet"+i, 0);
}else{
sheet = workbook.createSheet(sheetName+i, 0);
}
jxl.write.WritableFont wfc = new jxl.write.WritableFont(
WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
jxl.format.UnderlineStyle.NO_UNDERLINE
);
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(
wfc,NumberFormats.TEXT);
wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcfFC.setAlignment(Alignment.CENTRE); // 文字水平对齐
jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(
WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
jxl.format.UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.RED);
jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat(
wfc1,NumberFormats.TEXT);
wcfFC1.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcfFC1.setAlignment(Alignment.CENTRE); // 文字水平对齐
//设置单元格值的样式
jxl.write.WritableCellFormat wcf_cell = new jxl.write.WritableCellFormat(NumberFormats.TEXT);
wcf_cell.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_cell.setAlignment(Alignment.CENTRE); // 文字水平对齐
//给Excel第一行的列名赋予中文名称
for(int j=0;j<colNames.length;j++){
if(col != null && j < col){
jxl.write.Label labelCFC = new jxl.write.Label(j, 0, colNames[j],
wcfFC1);
sheet.addCell(labelCFC);
}else{
jxl.write.Label labelCFC = new jxl.write.Label(j, 0, colNames[j],
wcfFC);
sheet.addCell(labelCFC);
}
sheet.setColumnView(j, 25); // 设置列的宽度
}
//参数list集合遍历循环的起始值和结束值
int num_start = i*MAX_ROWS;
int num_end = (i+1)*MAX_ROWS;
int rowNum = 0;
if(num_end > list.size()){
num_end = list.size();
}
for(int k=num_start;k<num_end;k++){
Map obj = ObjectUtil.beanToMap(list.get(k));
Label label = null;
int colNum = 0;
//循环遍历各列对应对象的属性，并取出值，填充单元格
for(String temp:colParams){
String value = String.valueOf(obj.get(temp));
if(StringUtil.isEmpty(value) || "null".equals(value)){
label = new jxl.write.Label(colNum++,rowNum+1,"",wcf_cell);
}else{
label = new jxl.write.Label(colNum++,rowNum+1,value,wcf_cell);
}
sheet.addCell(label);
}
rowNum++;
}
}
workbook.write();
workbook.close();
os.close();
} catch (Exception e) {
e.printStackTrace();
throw new Exception("填充Excel各单元格异常:"+e.getMessage());
}
}
/**
* 填充Excel各单元格的值
* @param <T>
* @param list 需要导出的数据
* @param os 输出流
* @param colNames 第一行列字段的中文名称
* @param colParams 各列对应对象的属性
* @param sheetName sheet的名称
* @param remakeMap 每一列对应的的数字代表什么意思，例如 sex 1，男；2，女；，导出时希望显示汉字而不是显示数字
*/
public static <T> void setExcelWithExpectNum(List<T> list,OutputStream os,String[] colNames,String[] colParams,
String sheetName,Integer col,Map<String,Map<String,String>> remakeMap)throws Exception{
try {
WritableWorkbook workbook = Workbook.createWorkbook(os);
//设定一个sheet里面只能放50000条数据
int num = list.size()/MAX_ROWS + 1;
for(int i=0;i<num;i++){
WritableSheet sheet = null;
if(StringUtil.isEmpty(sheetName)){
sheet = workbook.createSheet("sheet"+i, 0);
}else{
sheet = workbook.createSheet(sheetName+i, 0);
}
jxl.write.WritableFont wfc = new jxl.write.WritableFont(
WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
jxl.format.UnderlineStyle.NO_UNDERLINE
);
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(
wfc,NumberFormats.TEXT);
wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcfFC.setAlignment(Alignment.CENTRE); // 文字水平对齐
jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(
WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
jxl.format.UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.RED);
jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat(
wfc1,NumberFormats.TEXT);
wcfFC1.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcfFC1.setAlignment(Alignment.CENTRE); // 文字水平对齐
//设置单元格值的样式
jxl.write.WritableCellFormat wcf_cell = new jxl.write.WritableCellFormat(NumberFormats.TEXT);
wcf_cell.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_cell.setAlignment(Alignment.CENTRE); // 文字水平对齐
//给Excel第一行的列名赋予中文名称
for(int j=0;j<colNames.length;j++){
if(col != null && j < col){
jxl.write.Label labelCFC = new jxl.write.Label(j, 0, colNames[j],
wcfFC1);
sheet.addCell(labelCFC);
}else{
jxl.write.Label labelCFC = new jxl.write.Label(j, 0, colNames[j],
wcfFC);
sheet.addCell(labelCFC);
}
sheet.setColumnView(j, 25); // 设置列的宽度
}
//参数list集合遍历循环的起始值和结束值
int num_start = i*MAX_ROWS;
int num_end = (i+1)*MAX_ROWS;
int rowNum = 0;
if(num_end > list.size()){
num_end = list.size();
}
for(int k=num_start;k<num_end;k++){
Map obj = ObjectUtil.beanToMap(list.get(k));
Label label = null;
int colNum = 0;
//循环遍历各列对应对象的属性，并取出值，填充单元格
for(String temp:colParams){
String value = "";
Object tempObj = obj.get(temp);
if(tempObj instanceof Date){
value = sf.format((Date)tempObj);
}else{
value = String.valueOf(tempObj);
}
for (Map.Entry<String,Map<String,String>> entry : remakeMap.entrySet()){
if (temp.equals(entry.getKey())){
value = entry.getValue().get(value);
}
}
if(StringUtil.isEmpty(value) || "null".equals(value)){
label = new jxl.write.Label(colNum++,rowNum+1,"",wcf_cell);
}else{
label = new jxl.write.Label(colNum++,rowNum+1,value,wcf_cell);
}
sheet.addCell(label);
}
rowNum++;
}
}
workbook.write();
workbook.close();
os.close();
} catch (Exception e) {
e.printStackTrace();
throw new Exception("填充Excel各单元格异常:"+e.getMessage());
}
}
/**
* 填充Excel各单元格的值
* @param <T>
* @param list
* @param os
* @param colNames 第一行列字段的中文名称
* @param colParams 各列对应对象的属性
* @param sheetName sheet的名称
*/
public static <T> void setToExcel(List<T> list,OutputStream os,String[] colNames,String[] colParams,
String sheetName)throws Exception{
try {
WritableWorkbook workbook = Workbook.createWorkbook(os);
//设定一个sheet里面只能放50000条数据
int num = list.size()/MAX_ROWS + 1;
for(int i=0;i<num;i++){
WritableSheet sheet = null;
if(StringUtil.isEmpty(sheetName)){
sheet = workbook.createSheet("sheet"+i, 0);
}else{
sheet = workbook.createSheet(sheetName+i, 0);
}
jxl.write.WritableFont wfc = new jxl.write.WritableFont(
WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
jxl.format.UnderlineStyle.NO_UNDERLINE
);
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(
wfc,NumberFormats.TEXT);
wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcfFC.setAlignment(Alignment.CENTRE); // 文字水平对齐
jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(
WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
jxl.format.UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.RED);
jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat(
wfc1,NumberFormats.TEXT);
wcfFC1.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcfFC1.setAlignment(Alignment.CENTRE); // 文字水平对齐
//设置单元格值的样式
jxl.write.WritableCellFormat wcf_cell = new jxl.write.WritableCellFormat();
wcf_cell.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_cell.setAlignment(Alignment.CENTRE); // 文字水平对齐
//给Excel第一行的列名赋予中文名称
for(int j=0;j<colNames.length;j++){
jxl.write.Label labelCFC = new jxl.write.Label(j, 0, colNames[j],
wcfFC);
sheet.addCell(labelCFC);
sheet.setColumnView(j, 25); // 设置列的宽度
}
//参数list集合遍历循环的起始值和结束值
int num_start = i*MAX_ROWS;
int num_end = (i+1)*MAX_ROWS;
int rowNum = 0;
if(num_end > list.size()){
num_end = list.size();
}
for(int k=num_start;k<num_end;k++){
Map obj = ObjectUtil.beanToMap(list.get(k));
Label label = null;
int colNum = 0;
//循环遍历各列对应对象的属性，并取出值，填充单元格
for(String temp:colParams){
String value = "";
try {
//Object tempObj = MethodUtils.invokeMethod(obj, "get"+temp.substring(0,1).toUpperCase()+temp.substring(1, temp.length()), null);
Object tempObj = obj.get(temp);
if(tempObj instanceof Date){
value = sf.format((Date)tempObj);
}else{
value = String.valueOf(tempObj);
}
} catch (Exception e) {
}finally{
if(StringUtil.isEmpty(value) || "null".equals(value)){
label = new jxl.write.Label(colNum++,rowNum+1,"",wcf_cell);
}else{
label = new jxl.write.Label(colNum++,rowNum+1,value,wcf_cell);
}
sheet.addCell(label);
}
}
rowNum++;
}
}
workbook.write();
workbook.close();
os.close();
} catch (Exception e) {
e.printStackTrace();
throw new Exception("填充Excel各单元格异常:"+e.getMessage());
}
}
/**
* 填充Excel，并对某一列设置其格式样式和宽度
* @param list
* @param os
* @param colNames
* @param colParams
* @param sheetName
* @param colStyle 数组的含义//0:取该列的值对应的属性，1：表示该列的样式，2：第几列，3：该列宽度
*/
public static <T> void setExcelByCol(List<T> list,OutputStream os,String[] colNames,String[] colParams,
String sheetName,Object[] colStyle)throws Exception{
try {
WritableWorkbook workbook = Workbook.createWorkbook(os);
//设定一个sheet里面只能放50000条数据
int num = list.size()/MAX_ROWS + 1;
for(int i=0;i<num;i++){
WritableSheet sheet = null;
if(StringUtil.isEmpty(sheetName)){
sheet = workbook.createSheet("sheet"+i, 0);
}else{
sheet = workbook.createSheet(sheetName+i, 0);
}
jxl.write.WritableFont wfc = new jxl.write.WritableFont(
WritableFont.ARIAL, 10, WritableFont.BOLD, false,
jxl.format.UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.RED);
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(
wfc,NumberFormats.TEXT);
wcfFC.setBackground(jxl.format.Colour.YELLOW);
wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcfFC.setAlignment(Alignment.CENTRE); // 文字水平对齐
//设置单元格值的样式
jxl.write.WritableCellFormat wcf_cell = new jxl.write.WritableCellFormat(NumberFormats.TEXT);
wcf_cell.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_cell.setAlignment(Alignment.CENTRE); // 文字水平对齐
//设置某一列宽度
if(colStyle.length == 4){
sheet.setColumnView(Integer.valueOf(colStyle[2].toString()),Integer.valueOf(colStyle[3].toString()));
}
//给单独某一列设置样式
jxl.write.WritableCellFormat wcfCol = new jxl.write.WritableCellFormat((DisplayFormat)colStyle[1]); //定义一个单元格样式 NumberFormats.FORMAT7
wcfCol.setAlignment(Alignment.CENTRE);
wcfCol.setVerticalAlignment(VerticalAlignment.CENTRE);
jxl.write.Number number = null;
//CellView cv = new CellView(); //定义一个列显示样式
//cv.setFormat(wcfCol);//把定义的单元格格式初始化进去
//cv.setSize(20);//设置列宽度（不设置的话是0，不会显示）
//sheet.setColumnView(Integer.valueOf(colStyle[0].toString()), cv);
//给Excel第一行的列名赋予中文名称
for(int j=0;j<colNames.length;j++){
jxl.write.Label labelCFC = new jxl.write.Label(j, 0, colNames[j],
wcfFC);
sheet.addCell(labelCFC);
}
//参数list集合遍历循环的起始值和结束值
int num_start = i*MAX_ROWS;
int num_end = (i+1)*MAX_ROWS;
int rowNum = 0;
if(num_end > list.size()){
num_end = list.size();
}
for(int k=num_start;k<num_end;k++){
Map obj = ObjectUtil.beanToMap(list.get(k));
Label label = null;
int colNum = 0;
//循环遍历各列对应对象的属性，并取出值，填充单元格
for(String temp:colParams){
String value = String.valueOf(obj.get(temp));
if(temp.equals(colStyle[0].toString()) && isNum(value)){
number = new jxl.write.Number(colNum++,rowNum+1,Double.parseDouble(value),wcfCol);
sheet.addCell(number);
}else{
if(StringUtil.isEmpty(value) || "null".equals(value)){
label = new jxl.write.Label(colNum++,rowNum+1,"",wcf_cell);
}else{
label = new jxl.write.Label(colNum++,rowNum+1,value,wcf_cell);
}
sheet.addCell(label);
}
}
rowNum++;
}
}
workbook.write();
workbook.close();
os.close();
} catch (Exception e) {
e.printStackTrace();
throw new Exception("填充Excel各单元格异常:"+e.getMessage());
}
}
/**
* 判断其是否为数字
* @param str
* @return
*/
public static boolean isNum(String str){
return str.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$");
}
/**
* 解析Excel数据
* @param inputStream
* @param colProperty Excel每一列对应的属性名称
* @param colName Excel的列名称
* @return
* @throws Exception
*/
public static List<Map<String, String>> parseExcelData(InputStream inputStream,String[] colProperty,String[] colName) throws Exception{
if(colProperty.length == 0){
throw new Exception("每一列对应的属性值不能为空！");
}
List<Map<String,String>> list = new ArrayList<Map<String,String>>();
Workbook workbook = Workbook.getWorkbook(inputStream); //处理输入流
Sheet[] sheet = workbook.getSheets();
if (sheet != null) {
// for(int i=0;i<sheet.length;i++){
int rows = sheet[0].getRows();//获取sheet的总行号
int cols = sheet[0].getColumns();//获取sheet的总列号
if(cols != colProperty.length){
throw new Exception("列对应的属性与列名不匹配！");
}
//根据列名称判断模板是否正确
for(int m=0;m<colName.length;m++){
String tempName = sheet[0].getCell(m, 0).getContents().trim();
if(!tempName.equals(colName[m])){
throw new Exception("Excel模板不匹配！");
}
}
//遍历获得每-行每一列信息
for(int j=1;j<rows;j++){
Map<String, String> map = new LinkedHashMap<String, String>();
for(int k=0;k<cols;k++){
String temp = sheet[0].getCell(k, j).getContents().trim();
if(StringUtil.isNotEmpty(temp)){
map.put(colProperty[k], temp);
}
}
if(!map.isEmpty()){
list.add(map);
}
}
}
return list;
}
}引用如下：
//存放信息的数组
ArrayList<HashMap<String,Object>> acountSelf_data1 = (ArrayList<HashMap<String,Object>>)acountSelf_data.get("acountSelf_data");
//excel表头
String[] colNames = { "公司名称", "账期起始日期","账期停用日期","信用额度","省份","客户类型","门店数","MTD账期GMV","MTD自营现金GMV","MTD活动现金GMV","MTD合计GMV","剩余额度","MTD额度使用率","额度占用率","招商","运营","2017-12GMV","2018-01GMV","2018-02GMV","2018-03GMV","2018-04GMV","2018-05GMV","2018-06GMV","2018-07GMV","GMV"};
//表头对应的字段名称
String[] colParams = { "buyer_name", "start_date", "end_date", "credit_limit", "RCV_REG_PROV", "param_name", "CUST_Count","gmv_mtd_zq","gmv_mtd_xj1","gmv_mtd_xj2","gmv_mtd_all","avl_limit","rate_mtd_use","rate_use","op","bd","2017-12GMV","2018-01GMV","2018-02GMV","2018-03GMV","2018-04GMV","2018-05GMV","2018-06GMV","2018-07GMV","GMV"};
//25为一共几列，sheetname是sheet名称
ExcelUtils.setExcel(acountSelf_data1,out,colNames,colParams,null,25);
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
