CRM-使用POI导入导出Excel文档
需求:
在项目数据库中,有一张用户表user,里面有系统的用户数据;现在需要将这个用户表的数据导出到excel中提供用户下载。
要求excel要有个一个主标题,各个列名称。
导出
以下代码,按照上图格式,根据用户列表创建Excel文件,并将Excel文件写入流中。
public static void exportUserExcel(List<User> userList, ServletOutputStream outputStream) {
try {
//1、创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//1.1、创建合并单元格对象
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 4);//起始行号,结束行号,起始列号,结束列号
//1.2、头标题样式
HSSFCellStyle style1 = createCellStyle(workbook, (short)16);
//1.3、列标题样式
HSSFCellStyle style2 = createCellStyle(workbook, (short)13);
//2、创建工作表
HSSFSheet sheet = workbook.createSheet("用户列表");
//2.1、加载合并单元格对象
sheet.addMergedRegion(cellRangeAddress);
//设置默认列宽
sheet.setDefaultColumnWidth(25);
//3、创建行
//3.1、创建头标题行;并且设置头标题
HSSFRow row1 = sheet.createRow(0);
HSSFCell cell1 = row1.createCell(0);
//加载单元格样式
cell1.setCellStyle(style1);
cell1.setCellValue("用户列表");
//3.2、创建列标题行;并且设置列标题
HSSFRow row2 = sheet.createRow(1);
String[] titles = {"用户名","帐号", "所属部门", "性别", "电子邮箱"};
for(int i = 0; i < titles.length; i++){
HSSFCell cell2 = row2.createCell(i);
//加载单元格样式
cell2.setCellStyle(style2);
cell2.setCellValue(titles[i]);
}
//4、操作单元格;将用户列表写入excel
if(userList != null){
for(int j = 0; j < userList.size(); j++){
HSSFRow row = sheet.createRow(j+2);
HSSFCell cell11 = row.createCell(0);
cell11.setCellValue(userList.get(j).getName());
HSSFCell cell12 = row.createCell(1);
cell12.setCellValue(userList.get(j).getAccount());
HSSFCell cell13 = row.createCell(2);
cell13.setCellValue(userList.get(j).getDept());
HSSFCell cell14 = row.createCell(3);
cell14.setCellValue(userList.get(j).isGender()?"男":"女");
HSSFCell cell15 = row.createCell(4);
cell15.setCellValue(userList.get(j).getEmail());
}
}
//5、输出
workbook.write(outputStream);
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 创建单元格样式
* @param workbook 工作簿
* @param fontSize 字体大小
* @return 单元格样式
*/
private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontSize) {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
//创建字体
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗字体
font.setFontHeightInPoints(fontSize);
//加载字体
style.setFont(font);
return style;
}
导入
将下面代码所示的Excel文件,导入到数据库中。
@Override
public void importExcel(File userExcel, String userExcelFileName) {
try {
FileInputStream fileInputStream = new FileInputStream(userExcel);
boolean is03Excel = userExcelFileName.matches("^.+\\.(?i)(xls)$");
//1、读取工作簿
Workbook workbook = is03Excel ? new HSSFWorkbook(fileInputStream):new XSSFWorkbook(fileInputStream);
//2、读取工作表
Sheet sheet = workbook.getSheetAt(0);
//3、读取行
if(sheet.getPhysicalNumberOfRows() > 2){
User user = null;
for(int k = 2; k < sheet.getPhysicalNumberOfRows(); k++){
//4、读取单元格
Row row = sheet.getRow(k);
user = new User();
//用户名
Cell cell0 = row.getCell(0);
user.setName(cell0.getStringCellValue());
//帐号
Cell cell1 = row.getCell(1);
user.setAccount(cell1.getStringCellValue());
//所属部门
Cell cell2 = row.getCell(2);
user.setDept(cell2.getStringCellValue());
//性别
Cell cell3 = row.getCell(3);
user.setGender(cell3.getStringCellValue().equals("男"));
//手机号
String mobile = "";
Cell cell4 = row.getCell(4);
try {
mobile = cell4.getStringCellValue();
} catch (Exception e) {
double dMobile = cell4.getNumericCellValue();
mobile = BigDecimal.valueOf(dMobile).toString();
}
user.setMobile(mobile);
//电子邮箱
Cell cell5 = row.getCell(5);
user.setEmail(cell5.getStringCellValue());
//生日
Cell cell6 = row.getCell(6);
if(cell6.getDateCellValue() != null){
user.setBirthday(cell6.getDateCellValue());
}
//5、保存用户
save(user);
}
}
workbook.close();
fileInputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}