java 导出数据为office xls文件
引入包(Maven/gradle)
office操作使用apache poi开源组件
maven
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
gradle
compile(
'org.apache.poi:poi:3.8',
'org.apache.poi:poi-ooxml:3.8'
)
编写一个工具类来简化操作
(代码很臭，用了3个循环，各位看看就好。如果你有更优的写法，也可以分享给我，让我学习学习~)
import org.apache.poi.hssf.usermodel.*;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class ExportExcel {
public InputStream exportExcel(String[] headers, List<Object[]> li, Object[] totalHeaders, Object[] totalData) throws Exception {
final String sheetname = "sheet";
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置居中格式
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 粗体
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
// 生成一个表格(若结果集大于2000行，会分成多个页签)
HSSFSheet sheet;
int pageSize = 2000;
int sheetCount = li.size() % pageSize == 0 ? li.size() / pageSize : li.size() / pageSize + 1;
for (int currPage =1; currPage<=sheetCount; currPage++) {
// 遍历集合数据，产生数据行
sheet = workbook.createSheet(sheetname + currPage);
int index = 0; // 计数器
sheet.setDefaultColumnWidth(25);
if (totalHeaders != null && totalData != null) {
HSSFRow row = sheet.createRow(0);
addTotalRow(row, style, totalHeaders);
row = sheet.createRow(1);
addTotalDataRow(row, totalData);
index = 2;
}
HSSFRow row = sheet.createRow(index);
// 产生表格标题行
addHeader(row, style, headers);
int begin = (currPage - 1) * pageSize;
int end = (begin + pageSize) > li.size() ? li.size() : (begin + pageSize);
System.out.println("begin:" + begin + ",end=" + end);
int rowCount = 1;
for (int n = begin; n < end; n++) {
row = sheet.createRow(rowCount);
rowCount++;
Object[] lo = li.get(n);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
// 设置成文本格式
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
try {
// 获取值
String value;
if (null == lo[i]) {
value = "";
} else {
value = lo[i].toString();
}
// 判断值的类型后进行强制类型转换
if (null == value) {
cell.setCellValue(new HSSFRichTextString(""));
} else {
cell.setCellValue(new HSSFRichTextString(value));
}
} catch (Exception e) {
// 捕获异常
e.printStackTrace();
throw new Exception("导出时，转换JavaBean出错");
}
}
}
}
ByteArrayOutputStream bos = new ByteArrayOutputStream();
try {
workbook.write(bos);
} catch (IOException e) {
throw new Exception("导出失败");
}
byte[] ba = bos.toByteArray();
bos.close();
ByteArrayInputStream bis = new ByteArrayInputStream(ba);
return bis;
}
/**
* 设置表格标题行
* @param row
* @param style
* @param headers
*/
protected static void addHeader(HSSFRow row, HSSFCellStyle style, String[] headers) {
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
//设置成文本格式
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
}
private void addTotalRow(HSSFRow row, HSSFCellStyle style, Object[] totalHeaders) {
for (int i = 0; i < totalHeaders.length; i++) {
HSSFCell cell = row.createCell(i);
//设置成文本格式
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(totalHeaders[i]==null?"":totalHeaders[i].toString());
cell.setCellValue(text);
}
}
private void addTotalDataRow(HSSFRow row, Object[] totalData) {
for (int i = 0; i < totalData.length; i++) {
HSSFCell cell = row.createCell(i);
//设置成文本格式
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
HSSFRichTextString text = new HSSFRichTextString(totalData[i]==null?"":totalData[i].toString());
cell.setCellValue(text);
}
}
}
调用方法例示
ExportExcel ex = new ExportExcel();
String[] headers = {"ID", "姓名", "账户", "注册时间", "状态"};
String querySQL = "SELECT `id`, `name`, `username`, `reg_time`, `status` FROM `users` ORDER BY `id`";
List<Map<String, Object>> items = jdbcTemplate.queryForList(querySQL);
List<Object[]> excelData = Lists.newArrayList();
InputStream in = null;
OutputStream out = null;
int totalUser = 0;
try {
// j2ee 将请求头设置为 excel文件(浏览器会自动弹出下载xls)
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + java.net.URLEncoder.encode("文件名称", "UTF-8") + ".xls");
for (int i = 0; i < items.size(); i++) {
Map<String, Object> item = items.get(i);
Object[] data = new Object[headers.length];
data[0] = item.get("id");
data[1] = item.get("name");
data[3] = item.get("username");
data[4] = item.get("reg_time");
data[5] = item.get("status");
excelData.add(data);
totalUser++;
}
Object[] data = new Object[headers.length];
data[0] = "";
data[1] = "";
data[2] = "合计";
data[3] = totalUser + " 个用户";
data[4] = "";
data[5] = "";
excelData.add(data);
in = ex.exportExcel(headers, excelData);
HSSFWorkbook xwb = (HSSFWorkbook) WorkbookFactory.create(in);
out = response.getOutputStream();
xwb.write(out);
out.flush();
} catch (Exception e) {
e.printStackTrace();
}