如何用<em>java</em> poi操作excel

1、用script标签包围，然后像xml语法一样书写

@Select({"<script>",
"SELECT * FROM tbl_order",
"WHERE 1=1",
"<when test='title!=null'>",
"AND mydate = #{mydate}",
"</when>",
"</script>"})
2、用Provider去实现SQL拼接，例如：e5a48de588b67a686964616f337

public class OrderProvider {
private final String TBL_ORDER = "tbl_order";

public String queryOrderByParam(OrderPara param) {
SQL sql = new SQL().SELECT("*").FROM(TBL_ORDER);
String room = param.getRoom();
if (StringUtils.hasText(room)) {
sql.WHERE("room LIKE #{room}");
}
Date myDate = param.getMyDate();
if (myDate != null) {
sql.WHERE("mydate LIKE #{mydate}");
}
return sql.toString();
}
}

public interface OrderDAO {

@SelectProvider(type = OrderProvider.class, method = "queryOrderByParam")
List<Order> queryOrderByParam(OrderParam param);

}
注意：方式1有个隐患就是当传入参数为空的时候，可能会造成全表查询。

复杂SQL用方式2会比较灵活（当然，并不建议写复杂SQL），而且可以抽象成通用的基类，使每个DAO都可以通过这个基类实现基本的通用查询，原理类似Spring JDBC Template。

注解类（将实体类加上该注解）
@Target({ ElementType.METHOD, ElementType.FIELD, ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField
{
//导出字段在excel中的名字
String title();
}

操作类（调用方法进行导出）
@Slf4j
public class ExcelUtil {

private static final int EXCEL_NUM_LIMIT = 200000;

/**
* 通用导出方法
*/
public static <T> void writeExcel(HttpServletResponse response, String fileName, List<T> list, Class<T> cls) {
// 1.创建一个workbook，对应一个Excel文件
SXSSFWorkbook workBook = new SXSSFWorkbook();
int page = list.size() % EXCEL_NUM_LIMIT == 0 ? list.size() / EXCEL_NUM_LIMIT : list.size() / EXCEL_NUM_LIMIT + 1;
log.info("sheet数量为：{}", page);
for (int m = 0; m < page; m++) {

Field[] fields = cls.getDeclaredFields();

ArrayList<String> headList = new ArrayList<>();

for (Field f : fields) {
ExcelField field = f.getAnnotation(ExcelField.class);
if (field != null) {
headList.add(field.title());
}
}

CellStyle style = getCellStyle(workBook);
Sheet sheet = workBook.createSheet();
workBook.setSheetName(m, "sheet" + String.valueOf(m + 1));
Header header = sheet.getHeader();
header.setCenter("sheet");

// 设置Excel表的第一行即表头
Row row = sheet.createRow(0);
for (int i = 0; i < headList.size(); i++) {
Cell headCell = row.createCell(i);
headCell.setCellType(CellType.STRING);
headCell.setCellStyle(style);//设置表头样式
headCell.setCellValue(String.valueOf(headList.get(i)));
sheet.setColumnWidth(i, 15 * 256);
}

int rowIndex = 1;
log.info("开始创建sheet{}", m);
int start = (EXCEL_NUM_LIMIT * m);
int end = list.size() - start >= EXCEL_NUM_LIMIT ? start + EXCEL_NUM_LIMIT : list.size();
log.info("开始{},结束{}", start, end);
for (int i = start; i < end; i++) {
Row rowData = sheet.createRow(rowIndex);//创建数据行
T q = list.get(i);
Field[] ff = q.getClass().getDeclaredFields();
int j = 0;
for (Field f : ff) {
ExcelField field = f.getAnnotation(ExcelField.class);
if (field == null) {
continue;
}
f.setAccessible(true);
Object obj = null;
try {
obj = f.get(q);
} catch (IllegalAccessException e) {
log.error("", e);
}
Cell cell = rowData.createCell(j);
cell.setCellType(CellType.STRING);
// 当数字时
if (obj instanceof Integer) cell.setCellValue((Integer) obj);
// 当Long时
if (obj instanceof Long) cell.setCellValue((Long) obj);
// 当为字符串时
if (obj instanceof String) cell.setCellValue((String) obj);
// 当为布尔时
if (obj instanceof Boolean) cell.setCellValue((Boolean) obj);
// 当为时间时
if (obj instanceof Date) cell.setCellValue(getFormatDate((Date) obj));
// 当为时间时
if (obj instanceof Calendar) cell.setCellValue((Calendar) obj);
// 当为小数时
if (obj instanceof Double) cell.setCellValue((Double) obj);
// 当为BigDecimal
if (obj instanceof BigDecimal) cell.setCellValue(Double.parseDouble(obj.toString()));
// 当ZonedDateTime
if (obj instanceof ZonedDateTime)
cell.setCellValue(((ZonedDateTime) obj).format(DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss")));
j++;
}
rowIndex++;
}
}

responseStream(workBook, response, fileName);
}

private static void responseStream(SXSSFWorkbook workBook, HttpServletResponse response, String fileName) {
OutputStream outputStream = null;
try {
response.setContentType("application/vnd.ms-excel; charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes("UTF-8"), "ISO-8859-1"));
response.setCharacterEncoding("utf-8");
//根据传进来的file对象创建可写入的Excel工作薄e799bee5baa6e79fa5e98193e59b9ee7ad94364
outputStream = response.getOutputStream();
log.info("数据导出Excel成功！");
workBook.write(outputStream);
} catch (IOException e) {
log.error("", e);
} finally {
try {
outputStream.close();
} catch (IOException e) {
log.error("", e);
}
}
}

/**
* 设置表头样式
*
* @param wb
* @return
*/
public static CellStyle getCellStyle(SXSSFWorkbook wb) {
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);//设置字体大小
font.setBold(true);//加粗
style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());// 设置背景色
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setAlignment(HorizontalAlignment.CENTER);//让单元格居中
style.setAlignment(HorizontalAlignment.CENTER_SELECTION);// 左右居中
style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
style.setWrapText(true);//设置自动换行
style.setFont(font);
return style;
}

public static String getFormatDate(Date date) {
SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
String dateString = formatter.format(date);
return dateString;
}

}
/**
* @author liuwu
* Excel的导入与导出
*/
@SuppressWarnings({ "unchecked" })
public class ExcelOperate {
/**
* @author liuwu
* 这是一个通用的方法，利用了JAVA的反射机制,
* 可以将放置在JAVA集合中并且符合一定条件的数据以EXCEL的形式输出到指定IO设备上
* @param title 表格标题名
* @param headers 表格属性e68a847a686964616f339列名数组
* @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。
* 此方法支持的 javabean属性【数据类型有java基本数据类型及String,Date,byte[](图片转成字节码)】
* @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
* @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
* @throws IOException
*/
public static void exportExcel(String title, String[] headers,Collection dataset, OutputStream out, String pattern) throws IOException {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
// 遍历集合数据，产生数据行
Iterator it = dataset.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
Object t = it.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < fields.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);//注意 实体get Set不要自己改名字不然反射会有问题
try {
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName,new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
HSSFRichTextString richString = new HSSFRichTextString(value.toString());
HSSFFont font3 = workbook.createFont();
font3.setColor(HSSFColor.BLUE.index);
richString.applyFont(font3);
cell.setCellValue(richString);
} catch (SecurityException e) {
e.printStackTrace();
e=null;
} catch (NoSuchMethodException e) {
e.printStackTrace();
e=null;
} catch (IllegalArgumentException e) {
e.printStackTrace();
e=null;
} catch (IllegalAccessException e) {
e.printStackTrace();
e=null;
} catch (InvocationTargetException e) {
e.printStackTrace();
e=null;
} finally {
// 清理资源
}
}
}
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
e=null;
}
}
}

可以考虑先建一个空的excel
写一条数据就完成对excel的编辑.
每次循环重新读取这个excel 重复编辑这个excel
这样效率可能不行,不过你要程序中断也要写入一些数据