poi导出excel用反射+sql表字段注释也可以很简单！！
1，引入poi版本的jar包！
2.开始使用：
1查询表注释当做excel的表头，字段名用来取值：
查询注释的sql: select COLUMN_NAME ,column_comment from INFORMATION_SCHEMA.Columns where table_name='######' and table_schema='######'
我是导出需要的就加上了字段，不是到处需要的就是空
传入查出来的listmap转换为map；中间因为顺序乱了就改为了LinkedHashMap
public static LinkedHashMap<String, String> fields(List<HashMap<String, String>> fields){
LinkedHashMap<String, String> fields2 = new LinkedHashMap<>();
for (int i = 0; i < fields.size(); i++) {
String key="",value="";
int k=0;
HashMap<String, String> hashMap = fields.get(i);
for (Iterator it = hashMap.keySet().iterator(); it.hasNext();) {
if(k==0) {
key = hashMap.get((String) it.next());
}else if(k==1){
value = hashMap.get((String) it.next());
}else {
System.out.println("k=="+k+"====================================");
}
k++;
}
if("".equals(value.trim())||value==null) {
continue;
}
fields2.put(key, value);
}
return fields2;
}
//数据的话就用List<实体对象> user
String path = "导出excel的名称.xlsx";
XSSFWorkbook workbook = new XSSFWorkbook();
//参数1list实体 2，查询出来的注释和字段，第几个sheet,sheet的名称
ExcelUtil.export(user, fields ,workbook,0,"sheetname");
OutputStream out = null;
//下载生成的excel
response.reset();
response.setContentType("application/vnd.ms-excel");
//toUtf8String这方法在下边
response.setHeader("Content-disposition", "inline;filename="+toUtf8String(path));
try{
out = response.getOutputStream();
workbook.write(out);
out.close();
workbook=null;
} catch (Exception e) {
e.printStackTrace();
} finally {
if(out != null){
out.close();
}
if(workbook != null){
workbook=null;
}
}
export工具方法：
/**
* 通过反射的方式导出Excel1
* @param <K>
* @param dataList 实体数据
* @param fields 表头
* @param workbook
* @param sheetindex sheet第一个传入0
* @param sheetname sheet叫什么
* @throws Exception
*
*/
public static <K> void export(List<K> dataList , Map<String, String> fields, XSSFWorkbook workbook,int sheetindex,String sheetname ) throws Exception {
if (dataList == null || dataList.size() == 0) {
throw new Exception("dataList is empty");
}
//表头key
String[] fieldNames = new String[fields.keySet().size()];
//表头value
String[] displayNames = new String[fields.keySet().size()];
int i = 0;
for (String fieldName : fields.keySet()) {
fieldNames[i] = fieldName;
displayNames[i] = fields.get(fieldName);
i ++;
}
// 定义单元格格式，添加单元格表样式，并添加到工作薄
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setWrapText(true);
cellStyle.setBorderRight(CellStyle.BORDER_THIN); // 设置右边框类型
cellStyle.setRightBorderColor(HSSFColor.BLACK.index); // 设置右边框颜色
cellStyle.setBorderLeft(CellStyle.BORDER_THIN); // 设置右边框类型
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index); // 设置右边框颜色
cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 设置右边框类型
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置右边框颜色
cellStyle.setBorderTop(CellStyle.BORDER_THIN); // 设置右边框类型
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
cellStyle.setWrapText(true);//自动换行
// 单元格字体
Font font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
XSSFSheet sheet = workbook.createSheet();
//创建第一行
XSSFRow row0 = sheet.createRow(0);
row0.setHeight((short) 800); // 设置高度 //属性结束
Cell cell_hebin = row0.createCell(0);
//第一行是啥这里将第一行表头和sheet名称设置了一致
row0.createCell(0).setCellValue(sheetname);
//excel第一行样式
CellStyle cellStyl2 = workbook.createCellStyle();
cellStyl2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyl2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 指定单元格垂直居中个对齐
cellStyl2.setWrapText(true);
// 设置单元格字体
Font font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font2.setFontName("宋体");
font2.setFontHeightInPoints((short) 14); // 字体大小
cellStyl2.setFont(font2);
cell_hebin.setCellStyle(cellStyl2);
//合并第一行
CellRangeAddress region = new CellRangeAddress(0, 0, 0, displayNames.length-1);
sheet.addMergedRegion(region);
XSSFRow row = sheet.createRow(1);
workbook.setSheetName(sheetindex, sheetname);
// 初始化列名这里取出查的map注释
for (int cellNum = 0; cellNum < displayNames.length; cellNum++) {
Cell cell1 = row.createCell(cellNum);
cell1.setCellStyle(cellStyle);
cell1.setCellValue(displayNames[cellNum]);
}
XSSFCell cell;
K data;
for (int j = 0; j < dataList.size(); j++) {
row = sheet.createRow(j + 2);
data = dataList.get(j);
for (int cellNum = 0; cellNum < fieldNames.length; cellNum++) {
cell = row.createCell(cellNum);
cell.setCellStyle(cellStyle);
// 通过反射的方式取值赋给单元格
Field field = data.getClass().getDeclaredField(fieldNames[cellNum]);
field.setAccessible(true);
if (field.get(data) == null) {
cell.setCellValue("");
} else if (field.getType() == String.class) {
cell.setCellValue((String) field.get(data));
} else if (field.getType() == Integer.class) {
cell.setCellValue((Integer)field.get(data));
} else if (field.getType() == Double.class) {
cell.setCellValue((Double)field.get(data));
} else if (field.getType() == LocalDate.class) {
cell.setCellValue(((LocalDate)field.get(data)).format(DateTimeFormatter.ISO_DATE));
}else if(field.getType() == Date.class){
DateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
String format = format1.format((Date)field.get(data));
cell.setCellValue(format);
}
}
}
}
//toUtf8String
public String toUtf8String(String s){
StringBuffer sb = new StringBuffer();
for (int i=0;i<s.length();i++){
char c = s.charAt(i);
if (c >= 0 && c <= 255){
sb.append(c);
}else{
byte[] b;
try {
b = Character.toString(c).getBytes("utf-8");
}catch (Exception ex) {
b = new byte[0];
}
for (int j = 0; j < b.length; j++) {
int k = b[j];
if (k < 0) k += 256;
sb.append("%" + Integer.toHexString(k).toUpperCase());
}
}
}
return sb.toString();
}