import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import javax.servlet.http.HttpServletRequest; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List;
public class ExcelUtils {
/** * @Description: 传入 数据列表list 输出OutputStream * @Author: vdi100 * @CreateDate: 2019/3/29 17:55 * @Version: 1.0 */ public static <T> void listToExcel(List<T> list, String[] headers, String title, OutputStream os) { HSSFWorkbook workbook = new HSSFWorkbook(); //生成一个表格 HSSFSheet sheet = workbook.createSheet(title); //设置表格默认列宽15个字节 sheet.setDefaultColumnWidth(15); //生成一个样式 HSSFCellStyle style = getCellStyle(workbook); //生成一个字体 HSSFFont font = getFont(workbook); //把字体应用到当前样式 style.setFont(font); //生成表格标题 HSSFRow row = sheet.createRow(0); row.setHeight((short) 300); HSSFCell cell = null; for (int i = 0; i < headers.length; i++) { cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } //创建多个sheet //将数据放入sheet中 int index = 0;//记录额外创建的sheet数量 for (int i = 0; i < list.size(); i++) { if ((i + 1) % 65535 == 0) { sheet = workbook.createSheet(title + index); //设置表格默认列宽15个字节 sheet.setDefaultColumnWidth(15); row = sheet.createRow(0); row.setHeight((short) 300); for (int j = 0; j < headers.length; j++) { cell = row.createCell(j); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[j]); cell.setCellValue(text); } index++; } if(i >= 65534) { row = sheet.createRow((i + 2) - (index * 65535)); } else { row = sheet.createRow(i + 1); } T t = list.get(i); //利用反射,根据JavaBean属性的先后顺序,动态调用get方法得到属性的值 Field[] fields = t.getClass().getFields(); try { for (int j = 0; j < fields.length; j++) { cell = row.createCell(j); Field field = fields[j]; String fieldName = field.getName(); String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); Method getMethod = t.getClass().getMethod(methodName, new Class[]{}); Object value = getMethod.invoke(t, new Object[]{}); if (null == value) { value = ""; } cell.setCellValue(value.toString()); } } catch (Exception e) { logger.error(e); } } try { workbook.write(os); } catch (Exception e) { logger.error(e); } finally { try { os.flush(); os.close(); } catch (IOException e) { logger.error(e); } } }
}