Question

JAVA怎么向Excel中写入批量数据?

Answer 1

public static void drawExcel(HSSFWorkbook wb, String sheetName, String title, int n, List<?> exlList, int[] index){
List<Object[]> exList =(List<Object[]>)exlList;
int len = exList.get(0).length;
// 创建一个sheet表单
HSSFSheet sheet = wb.createSheet(sheetName);
Region region = null;
//样式
HSSFCellStyle cellStyle1 = setStyleBorder(wb);
HSSFCellStyle cellStyle2 = setStyleFontBorder(wb);
// 创建标题行
HSSFRow row = sheet.createRow(0);
row.setHeight((short)500);
// 创建单元格
HSSFCell cell = null;
if(title != null && !"".equals(title)){
region = new Region(0, (short)0, 0, (short)(len- 1));
sheet.addMergedRegion(region);

cell = row.createCell(0);
// 标题写入单元格
cell.setCellValue(title);
cell.setCellStyle(setStyleFontSize(wb, 18));
}else{
n = n - 1;
}

NumberFormat formatter = NumberFormat.getNumberInstance();
formatter.setMaximumFractionDigits(8);
//合计信息
Double[] sum = new Double[len];
Object[] s = null;
for(int i = 0; i < exList.size(); i++){
s = exList.get(i);
row = sheet.createRow(i + n);
// 创建数据行
for(int j = 0; j < s.length; j++){
cell = row.createCell(j);
/***
* jobin create
*/
if(s[j] instanceof Integer || s[j] instanceof Float){
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
}else{
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
}
if(!StringUtils.isNotEmpty(s[j])){
cell.setCellStyle(cellStyle1);
continue;
}
String[] rs = null;
int l = 3000;
if(i == 0 && s[j].toString().indexOf(",") > 0){
rs = s[j].toString().split(",");
cell.setCellValue(rs[0]);
l = Integer.parseInt(rs[1]);
}else{
cell.setCellValue(s[j].toString());
}
if(i == 0){
cell.setCellStyle(cellStyle2);
//设置列宽
// if(j == 0){
sheet.setColumnWidth(j, l);
// }else if(j == s.length -1){
// sheet.setColumnWidth(j, 5000);
// }else{
// sheet.setColumnWidth(j, 3000);
// }
}else{
cell.setCellStyle(cellStyle1);
//合计统计
if(index != null){
for(int in : index){
if(in == j){
if(sum[in] == null) sum[in] = 0.0;
sum[in] += Double.parseDouble(s[j].toString());
}
}
}
}
}
}
//合计信息
if(index != null){
region = new Region(exList.size() + 1, (short)0, exList.size() + 1, (short)(index[0]-1));
sheet.addMergedRegion(region);
row = sheet.createRow(exList.size() + n);
for(int i = 0; i < sum.length; i++){
cell = row.createCell(i);
cell.setCellStyle(cellStyle2);
if(i == 0){
cell.setCellValue("合计");
}else if(sum[i] != null){
cell.setCellValue(formatter.format(sum[i]).replace(",", ""));
}
}
}

}
核心代码，我项目上使用的