1、先导入pom.xml
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.7</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-io -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-io</artifactId>
<version>1.3.2</version>
</dependency>
2、java文件
public String ExcelDownload() {
List<User> userList= getAllUser();//获取信息
String filename = mercNo + "_信息_" + TimeUtil.getTime("yyyyMMddHHmmss") + ".xls";// 导出文件名
HSSFWorkbook workBook = null;
String[] cellTitle = { "账号Id", "名字 ", "密码", "权限" };
try {
workBook = new HSSFWorkbook();// 创建工作薄
HSSFSheet sheet = workBook.createSheet();
workBook.setSheetName(0, "信息");// 工作簿名称
HSSFFont font = workBook.createFont();
font.setColor(HSSFFont.COLOR_NORMAL);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle cellStyle = workBook.createCellStyle();// 创建格式
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 创建第一行标题
HSSFRow titleRow = sheet.createRow((short) 0);// 第一行标题
for (int i = 0, size = cellTitle.length; i < size; i++) {// 创建第1行标题单元格
switch (i) {
case 0:
sheet.setColumnWidth(0, 5000);
break;
case 1:
sheet.setColumnWidth(1, 10000);
break;
case 2:
sheet.setColumnWidth(2, 9000);
break;
case 3:
sheet.setColumnWidth(3, 6000);
break;
}
HSSFCell cell = titleRow.createCell(i, 0);
cell.setCellStyle(cellStyle);
cell.setCellValue(cellTitle[i]);
}
// 从第二行开始写入数据
// 注:此处如果数据过多,会抛出java.lang.IllegalStateException异常:The maximum
// number of cell styles was exceeded.
// You can define up to 4000 styles in a .xls workbook。这是是由于cell
// styles太多create造成,故一般可以把cellstyle设置放到循环外面
if (StatementList != null && !StatementList.isEmpty()) {
HSSFCellStyle style = workBook.createCellStyle();// 创建格式
for (int i = 0, size = StatementList.size(); i < size; i++) {
User entity = userList.get(i);
HSSFRow row = sheet.createRow((short) i + 1);
for (int j = 0, length = cellTitle.length; j < length; j++) {
HSSFCell cell = row.createCell(j, 0);// 在上面行索引0的位置创建单元格
cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型
switch (j) {// 在单元格中输入一些内容
case 0:
cell.setCellValue(entity.getId());
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style);
break;
case 1:
cell.setCellValue(entity.getUsername());
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style);
break;
case 2:
cell.setCellValue(entity.getPassword());
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style);
break;
case 3:
cell.setCellValue(entity.getPower());
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style);
break;
}
}
}
String path = "D:\\test\\";
String filepath = path + "\\" + filename;
File file = new File(filepath);
File file123 = new File(filepath);
FileOutputStream outStream = new FileOutputStream(file123);
workBook.write(outStream);
outStream.flush();
outStream.close();
} catch (Exception e) {
System.out.println("文件导出发生异常!异常原因:" + e.getMessage());
e.printStackTrace();
}
return filename;
}