poi
Excel导出
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.List;
/**
* @Describution:将数据库中的数据以Excel文件的方式导出到本地
* @Author: Yang Yong
* @Date: 2020-12-31 14:59
* @Version: 1.0
**/
public class ExportExcel<T> {
/**
* 生成Excel文件
*
* @param <T>
* @param data
* @param columns
* @param sheetTitle
* @throws IOException
*/
public static <T> void generateExcelFile(List<T> data, List<String> columns, OutputStream stream, String sheetTitle) throws IOException {
Workbook workbook = generateExcelFile(data, columns, sheetTitle);
workbook.write(stream);
}
/**
* 生成Excel文件
*
* @param <T>
* @param data
* @param columns
* @return
*/
public static <T> Workbook generateExcelFile(List<T> data, List<String> columns,String sheetTitle) {
Workbook workbook = new HSSFWorkbook();
generateExcelSheet(workbook,data,columns,sheetTitle);
return workbook;
}
/**
* 生成Excel文件的Sheet页面 (拆分自generateExcelFile,便于复用)
* @param workbook Excel文件
* @param data 数据
* @param columns 列集合
* @param sheetTitle sheet页标题
*/
private static <T> void generateExcelSheet(Workbook workbook,List<T> data, List<String> columns, String sheetTitle){
Sheet sheet = workbook.createSheet(sheetTitle);
{
//产生表格标题行
Row headerRow = sheet.createRow(0);
for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
Cell cell = headerRow.createCell(columnIndex);
HSSFRichTextString text = new HSSFRichTextString(columns.get(columnIndex));
cell.setCellValue(text);
}
}
//遍历产生数据行
for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) {
T dataItem = data.get(rowIndex);
Row row = sheet.createRow(1 + rowIndex);
// 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
Field[] fields = dataItem.getClass().getDeclaredFields();
try {
//遍历产生单元格
for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
//Cell cell = row.createCell(columnIndex);
Cell cell = row.createCell(columnIndex);
Field field = fields[columnIndex];
String fieldName = field.getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
Class tCls = dataItem.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(dataItem, new Object[] {});
// 判断值的类型后进行强制类型转换
String textValue = null;
// 其它数据类型都当作字符串简单处理
if(value != null && value != ""){
textValue = value.toString();
}
if (textValue != null) {
// XSSFRichTextString richString = new XSSFRichTextString(textValue);
cell.setCellValue(textValue);
}
// ColumnDef columnDef = columns.get(columnIndex);
// Object objValue = columnDef.toDisplayString(columnDef.getPropertyValue(dataItem, rowIndex, columnIndex));
// if (objValue == null) {
// continue;
// }
// if (objValue instanceof Byte) {
// cell.setCellValue(((Byte) objValue).doubleValue());
// } else if (objValue instanceof Short) {
// cell.setCellValue(((Short) objValue).doubleValue());
// } else if (objValue instanceof Integer) {
// cell.setCellValue(((Integer) objValue).doubleValue());
// } else if (objValue instanceof Long) {
// cell.setCellValue(((Long) objValue).doubleValue());
// } else if (objValue instanceof Float) {
// cell.setCellValue(((Float) objValue).doubleValue());
// } else if (objValue instanceof Character) {
// cell.setCellValue(objValue.toString());
// } else {
// cell.setCellValue(objValue.toString());
// }
}
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
}
测试
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class Test {
public static void main(String[] args) {
//List<T> data, List<String> columns, OutputStream stream, String sheetTitle
//封装表头数据
List<String> columns = new ArrayList<String>();
columns.add("id");
columns.add("name");
columns.add("age");
columns.add("birthday");
// columns.add("other");
//创建表数据集合
List<Object> dateList = new ArrayList<Object>();
//封装表数据,20条
for (int i = 0; i < 20; i++) {
Student stu = new Student();
stu.setId(i);
stu.setName(i+":name");
stu.setAge(i);
stu.setBirthday(new Date());
// stu.setOther(new Te("chi","sleep"));
// List<Object> rowList = new ArrayList<Object>();
// for (int columnsIndex = 0; columnsIndex < columns.size(); columnsIndex++) {
// String s = columns.get(columnsIndex);
// rowList.add(i+s);
// }
dateList.add(stu);
}
File file = new File("G:\\demo\\demo03.xlsx");
try {
OutputStream stream = new FileOutputStream(file);
ExportExcel.generateExcelFile(dateList,columns,stream,"demo001");
stream.close();
System.out.println("ok");
} catch (Exception e) {
e.printStackTrace();
}
}
}