
com.github.fanzezhen.oldutils.ExcelUtils Maven / Gradle / Ivy
package com.github.fanzezhen.oldutils;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
/**
* Created by Andy on 2018/12/20.
* Desc:
*/
public class ExcelUtils {
// 去读Excel的方法readExcel,该方法的入口参数为一个File对象
public static List> readExcel(File file) {
InputStream is = null;
try {
// 创建输入流,读取Excel
is = new FileInputStream(file.getAbsolutePath());
// jxl提供的Workbook类
Workbook wb = Workbook.getWorkbook(is);
// Excel的页签数量
int sheet_size = wb.getNumberOfSheets();
// for (int index = 0; index < sheet_size; index++) {
List> outerList = new ArrayList<>();
for (Sheet sheet : wb.getSheets()) {
// 每个页签创建一个Sheet对象
// Sheet sheet = wb.getSheet(index);
// sheet.getRows()返回该页的总行数
for (int i = 0; i < sheet.getRows(); i++) {
List innerList = new ArrayList<>();
// sheet.getColumns()返回该页的总列数
for (int j = 0; j < sheet.getColumns(); j++) {
String cellInfo = sheet.getCell(j, i).getContents();
// if (cellInfo.isEmpty()) {
// continue;
// }
innerList.add(cellInfo);
}
outerList.add(i, innerList);
System.out.println();
}
}
return outerList;
} catch (BiffException | IOException e) {
e.printStackTrace();
} finally {
try {
assert is != null;
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
/**
* 导出excel报表
*
* @param res 查询到的结果集
* Page p=(Page)ResultList.getModel().get("page");
* res=p.getResult();
* @param s 需要提取的数据列,VO中只有列出的才会被导出
* String[] s={"activename","partnerid"};
* @param ws 工作表
*/
public static void createExcel(List res, String[] s, WritableSheet ws) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
for (int i = 0; i < res.size(); i++) {
Object obj = res.get(i);
String resultString = "";
Class classType = obj.getClass();
for (int j = 0; j < s.length; j++) {
String fieldName = s[j];
String stringLetter = fieldName.substring(0, 1).toUpperCase();
String getName = "get" + stringLetter + fieldName.substring(1);
Method getMethod = classType.getMethod(getName);
Object value = getMethod.invoke(obj);
if (value == null) {
ws.addCell(new Label(j, i + 1, ""));
} else {
if (fieldName.toUpperCase().contains("time".toUpperCase())) {
ws.addCell(new Label(j, i + 1, sdf.format(value)));
} else {
ws.addCell(new Label(j, i + 1, value.toString()));
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 生成excel的标题栏
*
* @param s 标题列
* @param ws 工作表
*/
public static void createTitle(String[] s, WritableSheet ws) {
try {
for (int j = 0; j < s.length; j++) {
String fieldName = s[j];
ws.addCell(new Label(j, 0, fieldName));
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void createExcelByMap(List> res, String[] s, WritableSheet ws) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
int row = 1;
for (HashMap hashMap : res) {
for (int j = 0; j < s.length; j++) {
ws.addCell(new Label(j, row, hashMap.get(s[j]) == null ? "" : hashMap.get(s[j]).toString()));
}
row++;
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void createExcelByBean(List
© 2015 - 2025 Weber Informatics LLC | Privacy Policy