jxl操作excel工具类
import java.io.File;
import java.io.IOException;
import java.util.List;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class ExcelHelper{
/**
* 分隔符
*/
private final static String SEPARATOR = "|";
/**
* 由List导出至指定的Sheet,带total行(最后一行)
* @param wb 模板的workbook
* @param sheetNum 第几个表单
* @param targetFilePath 生成文件夹路径
* @param l 内容list集合,以|分割的对象string集合
* @param headInfoRows 头信息的行数
* @param columnsLength 列数
* @param remarkRowNumber 备注所在行
* @param remark 备注
* @return
* @throws WriteException
* @throws IOException
* int
*/
public static int exportExcelFromList(jxl.Workbook wb, int sheetNum,
String targetFilePath, List<String> l, int headInfoRows,
int columnsLength,int remarkRowNumber,String remark) throws WriteException, IOException {
// 创建可写入的Excel工作薄对象
WritableWorkbook wwb = null;
int writeCount = 0;
// 单元格样式
// WritableFont bold = new
// WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
WritableCellFormat normalFormat = new WritableCellFormat(
NumberFormats.TEXT);
normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN,
jxl.format.Colour.BLACK);
//设置字体;
WritableFont font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);
WritableCellFormat normalFormat_total = new WritableCellFormat(
font);
normalFormat_total.setBorder(Border.ALL, BorderLineStyle.THIN,
jxl.format.Colour.BLACK);
try {
// 创建可写入的Excel工作薄对象
wwb = jxl.Workbook.createWorkbook(new File(targetFilePath), wb);
WritableSheet ws = wwb.getSheet(0);
Label cellRemark = new Label(0, remarkRowNumber, remark,
normalFormat);
ws.addCell(cellRemark);
int row = l.size();
int columns = columnsLength;
String[] ary = new String[120];
for (int i = 0; i < row; i++) {
ary = l.get(i).split("\\" + SEPARATOR);
for (int j = 0; j < columns; j++) {
if(i==row-1)
{
Label cell = new Label(j, i + headInfoRows, ary[j],
normalFormat_total);
ws.addCell(cell);
}else
{
Label cell = new Label(j, i + headInfoRows, ary[j],
normalFormat);
ws.addCell(cell);
}
}
writeCount++;
}
wwb.write();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (wwb != null) {
wwb.close();
}
}
return writeCount;
}
/**
* 导出不需要合计行
* @param wb
* @param sheetNum
* @param targetFilePath
* @param l
* @param headInfoRows
* @param columnsLength
* @param remarkRowNumber
* @param remark
* @return
* @throws WriteException
* @throws IOException
*/
public static int exportExcelFromListNoTotal(jxl.Workbook wb, int sheetNum,
String targetFilePath, List<String> l, int headInfoRows,
int columnsLength,int remarkRowNumber,String remark) throws WriteException, IOException {
// 创建可写入的Excel工作薄对象
WritableWorkbook wwb = null;
int writeCount = 0;
// 单元格样式
// WritableFont bold = new
// WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
WritableCellFormat normalFormat = new WritableCellFormat(
NumberFormats.TEXT);
normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN,
jxl.format.Colour.BLACK);
//设置字体;
WritableFont font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
WritableCellFormat normalFormat_total = new WritableCellFormat(
font);
normalFormat_total.setBorder(Border.ALL, BorderLineStyle.THIN,
jxl.format.Colour.BLACK);
try {
// 创建可写入的Excel工作薄对象
wwb = jxl.Workbook.createWorkbook(new File(targetFilePath), wb);
WritableSheet ws = wwb.getSheet(0);
Label cellRemark = new Label(0, remarkRowNumber, remark,
normalFormat);
ws.addCell(cellRemark);
int row = l.size();
int columns = columnsLength;
String[] ary = new String[120];
for (int i = 0; i < row; i++) {
ary = l.get(i).split("\\" + SEPARATOR);
for (int j = 0; j < columns; j++) {
Label cell = new Label(j, i + headInfoRows, ary[j],
normalFormat);
ws.addCell(cell);
}
writeCount++;
}
wwb.write();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (wwb != null) {
wwb.close();
}
}
return writeCount;
}
/**
* 生成简单Excel（head + content）
* @param path 导出路径
* @param header Excel头
* @param contentlist Excel内容
* @return 成功返回true,失败返回false
*/
public static boolean exportSimpleExcel(String path,String header[],List<List<String>> contentlist){
WritableWorkbook book = null;
try{
book = Workbook.createWorkbook(new File(path));
//生成名为eccif的工作表，参数0表示第一页
WritableSheet sheet = book.createSheet("Sheet1", 0);
//表头导航
for(int j=0;j<header.length;j++){
Label label = new Label(j, 0, header[j]);
sheet.addCell(label);
}
/*遍历List,记录集合*/
for(int i=0;i<contentlist.size();i++){
List row = contentlist.get(i);
if (row!=null){
/*遍历单条记录的数据列*/
for (int j = 0;j<row.size();j++){
sheet.addCell(new Label(j,i+1, (String) row.get(i)));
}
}
}
// 写入数据并关闭文件
book.write();
} catch (Exception e) {
e.printStackTrace();
return false; //导出失败
}finally{
if(book!=null){
try {
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return true;
}
}
无模板导出Excel
/*导出路径*/
String p = "D://测试.xls";
/*Excel头*/
String[] h = {"学号","学生姓名","系","班级"};
/*Excel内容*/
List<List<String>> list = new ArrayList<>();
List<String> row1 = new ArrayList<>();
row1.add("学号1");
row1.add("姓名1");
row1.add("系1");
row1.add("班级1");
list.add(row1);
List<String> row2 = new ArrayList<>();
row2.add("学号2");
row2.add("姓名2");
row2.add("系2");
row2.add("班级2");
list.add(row2);
/*调用函数导出Excel*/
ExcelHelper.exportSimpleExcel(p,h,list);
模板导出Excel
数据格式
exportList为List<String>，生成方式为遍历每个对象并将所有属性以|串起来
List<DetectorHistory> dfList = service.getList(); //获取对象集合
//生成的行数
int operatorCount = 0;
List<String> exportList = new ArrayList<String>();
StringBuffer sbList = new StringBuffer();
if (dfList!=null&&dfList.size()>0) {
for (DetectorHistory ele:dfList) {
sbList.delete(0, sbList.length());
//加入|
sbList.append("|");
//加入属性
...
exportList.add(sbList.toString());
operatorCount++;
}
}
导出
//模板所在文件夹路径
String tempPath = req.getSession().getServletContext()
.getRealPath(File.separator + "downLoad"
+ File.separator + "FileTemplate");
//生成文件所在文件夹路径
String exportFilePath = req.getSession().getServletContext()
.getRealPath(File.separator + "downLoad"
+ File.separator + "ExportFile");
//模板文件名,事先生成好以及头文件情况
String templateFileName = "F2TotalTmp.xls";
//生成文件名
String exportFileName = "F2Total" + CommonTool.getNowDateStr2() + "." + templateFileName.split("\\.")[1];
JSONObject jsonObject = new JSONObject();
try {
// 文件导出
if (exportList.size() > 0) {
//利用模板生成Workbook
Workbook rw = jxl.Workbook.getWorkbook(new File(tempPath + File.separator + templateFileName));
// 写入备注行
String remarkInfo = "tip:辅修学分上限为2分";
// 导出文件路径
String targetFilePath = exportFilePath + File.separator + exportFileName;
//导出文件的文件夹不存在即创建文件
File exportFile = new File(targetFilePath);
if(!exportFile.exists()) {
if (!exportFile.getParentFile().exists()) { //如果父文件夹不存在
exportFile.getParentFile().mkdirs(); //新建多层文件夹
}
}
//rw为模板workbook，0为sheetnum，其次为导出文件路径，exportList为|分割属性的string对象集合,4为头的行数,14为列数,1为备注所在行(从0开始),remarkinfo为备注所在行的信息
operatorCount = ExcelHelper.exportExcelFromListNoTotal(rw,
0, targetFilePath,
exportList, 3, 8, 1, remarkInfo);
}
jsonObject.put("operatorCount", operatorCount); //返回操作条数
jsonObject.put("exportFilePath", File.separator + "downLoad"
+ File.separator + "ExportFile" + File.separator + exportFileName); //返回生成的文件路径
if(jsonObject.get("operatorCount")!=null&&Integer.valueOf(jsonObject.get("operatorCount").toString())<=0)
{//当生成内容条数为0时
jsonObject.put("rtnCode", "404");
}else
{
jsonObject.put("rtnCode", "0");
}
} catch (BiffException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} finally{
}
resp.resetBuffer();
resp.setContentType("text/html;charset=UTF-8");
resp.getOutputStream().write(jsonObject.toString().getBytes("utf-8"));
resp.getOutputStream().flush();