- 读取Excel数据读取
依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
接口类:
import org.apache.poi.ss.usermodel.Row;
/**
* @Title: ExcelRowDataToVO
* @Description: 该接口由vo来实现
* @Remark:
* @Version: 1.0.0
*/
public interface ExcelRowDataToVO {
/**
* @Methodname readExcelData
* @Description 将excel中的行记录信息转换为VO
* @Param row
* @Return java.lang.Object
* @Since 1.0.0
*/
public Object readExcelData(Row row);
}
VO类:
import java.io.Serializable;
import java.util.Date;
import com.iflytek.atp.web.util.ExcelUtil;
import org.apache.poi.ss.usermodel.Row;
/**
* @Title: ImportMemoryVO
* @Description:
* @Remark:
* @Version: 1.0.0
*/
public class ImportMemoryVO extends ExcelUtil implements ExcelRowDataToVO, Serializable {
private static final long serialVersionUID = 5065753711703049861L;
/**
* 句对ID
*/
private String id;
/**
* 记忆库ID
*/
private String memoryId;
/**
* 删除状态
*/
private String delState;
/**
* 创建人
*/
private String createUser;
/**
* 创建时间
*/
private Date createTime;
/**
* 最后修改人
*/
private String lastEditUser;
/**
* 最后修改时间
*/
private Date lastEditTime;
/**
* 原文
*/
private String originalText;
/**
* 译文
*/
private String targetText;
/**
* 当前行数
*/
private Integer rowNumber;
/**
* 导入失败原因
*/
private String wrongInfo;
/**
* Excel row对象转换为VO
* @param row
* @return
*/
@Override
public ImportMemoryVO readExcelData(Row row) {
if (row == null) {
return null;
}
this.setRowNumber(row.getRowNum() + 1);
int first = row.getFirstCellNum();
int last = row.getLastCellNum();
for (int i = first; i < last; i++) {
switch (i) {
case 0:
this.setOriginalText(getValue(row.getCell(i)));
break;
case 1:
this.setTargetText(getValue(row.getCell(i)));
break;
default:
break;
}
}
return this;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getMemoryId() {
return memoryId;
}
public void setMemoryId(String memoryId) {
this.memoryId = memoryId;
}
public String getDelState() {
return delState;
}
public void setDelState(String delState) {
this.delState = delState;
}
public String getCreateUser() {
return createUser;
}
public void setCreateUser(String createUser) {
this.createUser = createUser;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public String getLastEditUser() {
return lastEditUser;
}
public void setLastEditUser(String lastEditUser) {
this.lastEditUser = lastEditUser;
}
public Date getLastEditTime() {
return lastEditTime;
}
public void setLastEditTime(Date lastEditTime) {
this.lastEditTime = lastEditTime;
}
public String getOriginalText() {
return originalText;
}
public void setOriginalText(String originalText) {
this.originalText = originalText == null ? null : originalText.trim();
}
public String getTargetText() {
return targetText;
}
public void setTargetText(String targetText) {
this.targetText = targetText == null ? null : targetText.trim();
}
public Integer getRowNumber() {
return rowNumber;
}
public void setRowNumber(Integer rowNumber) {
this.rowNumber = rowNumber;
}
public String getWrongInfo() {
return wrongInfo;
}
public void setWrongInfo(String wrongInfo) {
this.wrongInfo = wrongInfo;
}
@Override
public String toString() {
return "ImportMemoryVO{" +
"id='" + id + '\'' +
", memoryId='" + memoryId + '\'' +
", delState='" + delState + '\'' +
", createUser='" + createUser + '\'' +
", createTime=" + createTime +
", lastEditUser='" + lastEditUser + '\'' +
", lastEditTime=" + lastEditTime +
", originalText='" + originalText + '\'' +
", targetText='" + targetText + '\'' +
", rowNumber=" + rowNumber +
", wrongInfo='" + wrongInfo + '\'' +
'}';
}
}
读取Excel工具类:
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @Title: ExcelUtil
* @Description: excel工具类
* @Remark:
* @Version: 1.0.0
*/
public class ExcelUtil {
/**
* @Methodname readExcelData
* @Description 读取Excel数据
* @Param inputStream
* @Param _class
* @Return List<T>
* @Since 1.0.0
*/
public static <T> List<T> readExcelData(InputStream inputStream, Class<T> _class) throws Exception {
List<T> voList = new ArrayList<T>();
if (!inputStream.markSupported()) {
inputStream = new PushbackInputStream(inputStream, 8);
}
Workbook workbook = null;
if (POIFSFileSystem.hasPOIFSHeader(inputStream)) {
workbook = new HSSFWorkbook(inputStream);
} else if (POIXMLDocument.hasOOXMLHeader(inputStream)) {
workbook = new XSSFWorkbook(OPCPackage.open(inputStream));
} else {
return voList; //格式不支持
}
//获取第一个sheet
Sheet sheet = workbook.getSheetAt(0);
Row rowData;
for (int i = 0; i < sheet.getLastRowNum() + 1; i++) {
rowData = sheet.getRow(i);
// if (i == sheet.getTopRow()) {
// //标题不读入
// continue;
// }
voList.add((T) _class.getMethod("readExcelData", Row.class).invoke(_class.newInstance(), rowData));
}
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (inputStream != null) {
inputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
workbook.close();
return voList;
}
/**
* @Methodname getValue
* @Description 获取cell值
* @Param xssfRow
* @Return String
* @Since 1.0.0
*/
protected static String getValue(Cell xssfRow) {
if (null == xssfRow) {
return "";
}
if (xssfRow.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == Cell.CELL_TYPE_NUMERIC) {
DecimalFormat df = new DecimalFormat("0");
return df.format(xssfRow.getNumericCellValue());
} else {
return String.valueOf(xssfRow.getStringCellValue());
}
}
}
Client类:
public ResultMap importMemorySentence(MultipartFile file) {
//将Excel数据转化为对象
List<ImportMemoryVO> excelList = ExcelUtil.readExcelData(file.getInputStream(), ImportMemoryVO.class);
}
- Excel生成并下载
依赖:
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-jexcel</artifactId>
<version>1.0.3</version>
</dependency>
Controller方法:
/**
* @Methodname errorImport
* @Description 导出上传的错误信息表格
* @Param response
* @Param data
* @Return void
* @Since 1.0.0
*/
@ResponseBody
@RequestMapping(value = "/errorImport", method = RequestMethod.POST)
public void errorImport(HttpServletResponse response, String data) {
List<ImportMemoryVO> list = JSON.parseArray(data, ImportMemoryVO.class);
try {
response.setContentType("application/ms-excel");
response.addHeader("Content-Disposition", "attachment;filename="
+ URLEncoder.encode("导入错误反馈", "UTF-8") + ".xls");
DownloadExcelUtil.generateMemoryErrorExcel(list, response);
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
logger.error("下载失败,原因是:" + e.getMessage());
}
}
下载工具类:
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
* @Title: DownloadExcelUtil
* @Description: 导出记忆库句对、术语库句对
* @Remark:
* @Version: 1.0.0
*/
public class DownloadExcelUtil {
/**
* @Methodname generateMemoryErrorExcel
* @Description 生成导入Memory失败的excel
* @Param list
* @Param response
* @Return void
* @Since 1.0.0
*/
public static void generateMemoryErrorExcel(List<ImportMemoryVO> list, HttpServletResponse response) {
WritableWorkbook workBook = null;
BufferedOutputStream os = null;
try {
os = new BufferedOutputStream(response.getOutputStream());
//创建workbook对象
workBook = jxl.Workbook.createWorkbook(os);
//创建单元格样式
WritableCellFormat wcf = new WritableCellFormat();
WritableCellFormat _wcf = new WritableCellFormat();
//背景颜色设置为黄色
wcf.setBackground(Colour.YELLOW);
wcf.setAlignment(Alignment.CENTRE);
_wcf.setAlignment(Alignment.CENTRE);
//创建一个名为sheet1的sheet
WritableSheet sheet = workBook.createSheet("sheet1", 0);
sheet.getSettings().setDefaultColumnWidth(20);
//给sheet添加一系列的cell,注意cell的坐标是从(0,0)开始的,其代表着excel中第一行,第一列的方格
//这里主要是在sheet中加入一行字段
sheet.addCell(new Label(0, 0, "原文", wcf));
sheet.addCell(new Label(1, 0, "译文", wcf));
sheet.addCell(new Label(2, 0, "导入失败原因", wcf));
//下面添加其它行数据
for (int i = 0; i < list.size(); i++) {
sheet.addCell(new Label(0, i + 1, list.get(i).getOriginalText(), _wcf));
sheet.addCell(new Label(1, i + 1, list.get(i).getTargetText(), _wcf));
//导入错误信息
sheet.addCell(new Label(2, i + 1, list.get(i).getWrongInfo().substring(list.get(i).getWrongInfo().indexOf(":") + 1), _wcf));
}
workBook.write();
workBook.close();
} catch (IOException e) {
e.printStackTrace();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} finally {
try {
if(nulll != os){
os.flush();
os.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
ExcelUtil类(全):
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map.Entry;
import javax.servlet.http.HttpServletResponse;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
/**
* {Excel工具类}
*
* @lastModified
* @history
*/
public class ExcelUtil {
/**
* 导出excel表
*
* @param list
* 数据集合
* @param fieldMap
* 类的英文属性和Excel中的中文列名的对应关系
* @param sheetName
* 工作表的名称
* @param response
* 导出流
* @lastModified
* @history
*/
public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName,
String fileName, HttpServletResponse response) throws Exception {
OutputStream out = null;
// 设置默认文件名为当前时间:年月日时分秒
Date d = new Date();
SimpleDateFormat dateformat = new SimpleDateFormat("yyyyMMddHHmmss");
String date = dateformat.format(d);
fileName = fileName + "-" + date;
response.reset();
response.setContentType("application/binary;charset=ISO8859_1");
// response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件
response.addHeader("Content-disposition",
"attachment; filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xls");
if (list.size() == 0 || list == null) {
throw new Exception("数据源中没有任何数据");
}
int sheetSize = list.size();
if (sheetSize > 65535 || sheetSize < 1) {
sheetSize = 65535;
}
// 创建工作簿并发送到OutputStream指定的地方
WritableWorkbook wwb = null;
try {
out = response.getOutputStream();
wwb = Workbook.createWorkbook(out);
// 1.计算一共有多少个工作表
int sheetNum = list.size() % sheetSize == 0 ? list.size() / sheetSize : (list.size() / sheetSize + 1);
// 2.创建相应的工作表,并向其中填充数据
for (int i = 0; i < sheetNum; i++) {
// 如果只有一个工作表的情况
if (sheetNum == 1) {
WritableSheet sheet = wwb.createSheet(sheetName, i);
fillSheet(sheet, list, fieldMap, 0, list.size() - 1);
} else {
// 有多个工作表的情况
WritableSheet sheet = wwb.createSheet(sheetName + (i + 1), i);
// 获取开始索引和结束索引
int firstIndex = i * sheetSize;
int lastIndex = (i + 1) * sheetSize - 1 > list.size() - 1 ? list.size() - 1
: (i + 1) * sheetSize - 1;
// 填充工作表
fillSheet(sheet, list, fieldMap, firstIndex, lastIndex);
}
}
wwb.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
if(wwb != null) {
wwb.close();
}
if(out != null) {
out.close();
}
}
}
/**
* 将Excel转化为List
*
* @param io
* 输入流
* @param entityClass
* 实体class
* @param fieldMap
* Excel中的中文列头和类的英文属性的对应关系Map
* @return
* @lastModified
* @history
*/
public static <T> List<T> excelToList(InputStream io, Class<T> entityClass,
LinkedHashMap<String, String> fieldMap) {
// 定义要返回的list
List<T> resultList = new ArrayList<T>();
Workbook wb = null;
try {
// 获取工作簿实例
wb = Workbook.getWorkbook(io);
// 获取sheet表,sheet下标从0开始
Sheet sheet = wb.getSheet(0);
// 获取工作表的有效行数
int realRows = 0;
for (int i = 0; i < sheet.getRows(); i++) {
int nullCols = 0;
for (int j = 0; j < sheet.getColumns(); j++) {
Cell currentCell = sheet.getCell(j, i);
if (currentCell == null || "".equals(currentCell.getContents().toString())) {
nullCols++;
}
}
if (nullCols == sheet.getColumns()) {
break;
} else {
realRows++;
}
}
// 如果Excel中没有数据则提示错误
if (realRows <= 1) {
throw new Exception("Excel文件中没有任何数据");
}
Cell[] firstRow = sheet.getRow(0);
String[] excelFieldNames = new String[firstRow.length];
// 获取Excel表中的表头名
for (int i = 0; i < firstRow.length; i++) {
excelFieldNames[i] = firstRow[i].getContents().toString().trim();
}
// 判断需要的字段在Excel中是否都存在
boolean isExist = true;
List<String> excelFieldList = Arrays.asList(excelFieldNames);
for (String cnName : fieldMap.keySet()) {
if (!excelFieldList.contains(cnName)) {
isExist = false;
break;
}
}
// 如果有列名不存在,则抛出异常,提示错误
if (!isExist) {
throw new Exception("Excel中缺少必要的字段,或字段名称有误");
}
// 将列名和列号放入Map中,这样通过列名就可以拿到列号
LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
for (int i = 0; i < excelFieldNames.length; i++) {
colMap.put(excelFieldNames[i], firstRow[i].getColumn());
}
// 将sheet转换为list
for (int i = 1; i < realRows; i++) {
// 新建要转换的对象
T entity = entityClass.newInstance();
// 给对象中的字段赋值
for (Entry<String, String> entry : fieldMap.entrySet()) {
// 获取中文字段名
String cnNormalName = entry.getKey();
// 获取英文字段名
String enNormalName = entry.getValue();
// 根据中文字段名获取列号
int col = colMap.get(cnNormalName);
Cell cell = sheet.getCell(col, i);
String content = "";
// 获取当前单元格中的内容
if (cell != null) {
if (cell.getType() == CellType.DATE) {
DateCell dc = (DateCell) cell;
Date date = dc.getDate();
// 暂时统一格式
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
content = sdf.format(date);
} else {
content = cell.getContents().toString().trim();
}
}
ReflectUtil.setProperty(entity, enNormalName, content);
}
resultList.add(entity);
}
} catch (Exception e) {
e.printStackTrace();
}
return resultList;
}
/**
* excel多sheet导出
*
* @param fileName
* 附件名
* @param sheet
* 列表数据
* @param list
* 数据列表
* @param fieldMap
* 数据对应字段
* @param firstIndex
* 开始数据行
* @param lastIndex
* 截止数据行
* @throws Exception
*/
public static <T> void tableTwoSheet(String fileName, WritableSheet sheet, List<T> list,
LinkedHashMap<String, String> fieldMap, int firstIndex, int lastIndex) throws Exception {
// 定义存放英文字段名和中文字段名的数组
String[] enFields = new String[fieldMap.size()];// 英文字段名数组
String[] cnFields = new String[fieldMap.size()];// 中文字段名数据
setCnEn(fieldMap, enFields, cnFields);// 填充数组数据
// 设计表头样式
WritableCellFormat titleFormat = setHeaderStyle();
// 设计表单一样式
WritableCellFormat tableFormat = setTableStyle();
// 内容样式
WritableCellFormat contentFormat = setContentStyle();
// 设计表头标题样式
WritableCellFormat titleHeaderFormat = setTitleHeaderStyle();
// 填充表头标题 titleHeaderFormat
sheet.addCell(new Label(0, 0, "sheet1", titleHeaderFormat));
sheet.mergeCells(0, 0, enFields.length - 1, 0); // 合并单元格
sheet.addCell(new Label(0, 1, "sheet2", tableFormat));
sheet.mergeCells(0, 1, enFields.length - 1, 1); // 合并单元格
// 填充表头列
for (int i = 0; i < cnFields.length; i++) {
sheet.addCell(new Label(i, 2, cnFields[i], titleFormat));
}
// 填充内容
int rowNo = 3;
setCommonListContent(firstIndex, lastIndex, list, sheet, enFields, rowNo, contentFormat);
// 设置自动列宽
setColumnAutoSize(sheet, 5);
}
/**
* 填充工作表
*
* @param sheet
* @param list
* @param fieldMap
* @param firstIndex
* @param lastIndex
* @throws Exception
* @lastModified
* @history
*/
private static <T> void fillSheet(WritableSheet sheet, List<T> list, LinkedHashMap<String, String> fieldMap,
int firstIndex, int lastIndex) throws Exception {
// 定义存放英文字段名和中文字段名的数组
String[] enFields = new String[fieldMap.size()];
String[] cnFields = new String[fieldMap.size()];
// 填充数组
int count = 0;
for (Entry<String, String> entry : fieldMap.entrySet()) {
enFields[count] = entry.getKey();
cnFields[count] = entry.getValue();
count++;
}
// 设计表头样式
WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 13, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
// 内容样式
WritableFont contentFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREY_80_PERCENT);
WritableCellFormat contentFormat = new WritableCellFormat(contentFont);
contentFormat.setAlignment(jxl.format.Alignment.CENTRE);
contentFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
contentFormat.setWrap(true);
// 标题水平居中对齐
titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
for (int i = 0; i < cnFields.length; i++) {
Label label = new Label(i, 0, cnFields[i], titleFormat);
sheet.addCell(label);
}
// 填充内容
int rowNo = 1;
setContent(firstIndex, lastIndex, list, sheet, enFields, rowNo, contentFormat);
// 设置自动列宽
setColumnAutoSize(sheet, 10);
}
/**
* 设置工作表自动列宽和首行加粗
*
* @param ws
* @param extraWith
* @lastModified
* @history
*/
private static void setColumnAutoSize(WritableSheet ws, int extraWith) {
// 获取本列的最宽单元格的宽度
for (int i = 0; i < ws.getColumns(); i++) {
int colWith = 0;
for (int j = 0; j < ws.getRows(); j++) {
String content = ws.getCell(i, j).getContents().toString();
int cellWith = content.length();
if (colWith < cellWith) {
colWith = cellWith;
}
}
// 设置单元格的宽度为最宽宽度+额外宽度
ws.setColumnView(i, colWith + extraWith);
}
}
/**
*
* 功能描述: 定义存放英文字段名和中文字段名的数组
*
* @param:
* @return:
*/
private static void setCnEn(LinkedHashMap<String, String> fieldMap, String[] enFields, String[] cnFields) {
// 填充数组
int count = 0;
for (Entry<String, String> entry : fieldMap.entrySet()) {
enFields[count] = entry.getKey();
cnFields[count] = entry.getValue();
count++;
}
}
/**
*
* 功能描述: 设计表头标题的样式
*
* @param:
* @return:
*/
private static WritableCellFormat setTitleHeaderStyle() throws WriteException {
WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 20, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
// 标题水平居中对齐
titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
titleFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
return titleFormat;
}
/**
*
* 功能描述: 设计表头样式
*
* @param:
* @return:
*/
private static WritableCellFormat setHeaderStyle() throws WriteException {
WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
// 标题水平居中对齐
titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
titleFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
titleFormat.setWrap(true);
return titleFormat;
}
/**
*
* 功能描述: 设计内容样式
*
* @param:
* @return:
*/
private static WritableCellFormat setContentStyle() throws WriteException {
WritableFont contentFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREY_80_PERCENT);
WritableCellFormat contentFormat = new WritableCellFormat(contentFont);
contentFormat.setAlignment(jxl.format.Alignment.CENTRE);
contentFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
return contentFormat;
}
/**
*
* 功能描述: 填充内容
*
* @param:
* @return:
*/
private static <T> void setContent(int firstIndex, int lastIndex, List<T> list, WritableSheet sheet,
String[] enFields, int rowNo, WritableCellFormat contentFormat) throws WriteException {
for (int index = firstIndex; index <= lastIndex; index++) {
// 获取单个对象
T item = list.get(index);
for (int i = 0; i <= enFields.length-1; i++) {
String fieldValue;
Object objValue = ReflectUtil.getNestedProperty(item, enFields[i]);
fieldValue = objValue == null ? "" : objValue.toString();
Label label = new Label(i, rowNo, fieldValue, contentFormat);
sheet.addCell(label);
}
rowNo++;
}
}
/**
*
* 功能描述: 设计表单一样式
*
* @param:
* @return:
*/
private static WritableCellFormat setTableStyle() throws WriteException {
WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
// 标题水平居左对齐
titleFormat.setAlignment(jxl.format.Alignment.LEFT);
titleFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
return titleFormat;
}
/**
* 填充excel表格数据
*
* @param firstIndex
* @param lastIndex
* @param list
* @param sheet
* @param enFields
* @param rowNo
* @param contentFormat
* @throws WriteException
*/
private static <T> void setCommonListContent(int firstIndex, int lastIndex, List<T> list, WritableSheet sheet,
String[] enFields, int rowNo, WritableCellFormat contentFormat) throws WriteException {
for (int index = firstIndex; index <= lastIndex; index++) {
// 获取单个对象
T item = list.get(index);
for (int i = 0; i < enFields.length; i++) {
String fieldValue;
Object objValue = ReflectUtil.getNestedProperty(item, enFields[i]);
fieldValue = objValue == null ? "" : objValue.toString();
Label label = new Label(i, rowNo, fieldValue, contentFormat);
sheet.addCell(label);
}
rowNo++;
}
}
}