POI导入导出

POI  -poor obfuscation implementation.

我们要用到的东西是HSSF和XSSF,区别在哪里,请看屏幕。

HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.

HSSF and XSSF provides ways to read spreadsheets create, modify, read and write XLS spreadsheets. They provide:

• low level structures for those with special needs
• an eventmodel api for efficient read-only access
• a full usermodel api for creating, reading and modifying XLS file

---创建xls
1.创建工作簿  03版本
HSSFWorkbook workbook = new HSSFWorkbook();
2.创建工作表
HSSFSheet sheet =workbook.createSheet('sheetname');
3.创建行
HSSFRow   sheet.createRow(3);
4.创建单元格
HSSFCell cell=row.createRow(3);
cell.setCellValue('hello world);
//输出到磁盘
String  filepath="";
FileOutputStream outputStream=new FileOutputStream(filepath);
//把excel输出到具体的路径
workbook.write(outputStream);
workbook.close();
outputStream.close();




---读取xls  03版本
FileInputStream inputStream = new FileInputStream(".xls");
//read the workbook 
HSSFWorkbook workbook=new HSSFWorkbook(inputStream);
//read the sheet
HSSFSheet sheet =workbook.getSheetAt(0);//based on the index where the sheet is locating.
//read the row 
HSSFRow row = sheet.getRow(2);// the third row 
//read the cell
HSSFCell cell=row.getCell(2);//the third row and the third col.
//print the content of the cell 
System.out.println(cell.getStringCellValue());






07版本的实现==>HSSFWorkbook ==> XSSFWorkbook 修改即可，哈哈。
//判断文档是哪个版本
03 xls 区别 07xsls 
正则匹配
String fileName="...xls";
fileName.matches("^.\\.(?i)((xls)|(xslx))$")  //斜线前面是任意的. 不区分大小写?i xls和xlsx结尾  (xls)|(xlsx)$


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

POI excel  style ：文档样式


合并单元格对象属于workbook运用于工作表
合并单元格对象CellRangeAddress  parameters int lastRow ,int firstCol ,int last col ;


样式属于工作簿






---合并单元


HSSFWorkbook workbook=new HSSFWorkbook();
new CellRangeAddress(2,2,2,4);//起始行号 结束行号 起始列 结束列
HSSFSheet sheet=workbook.createSheet("hello");
sheet.addMergedRegion(CellRangeAddress);


//创建单元格样式
HSSFCellStyle style =workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中  垂直居中 HSSFCellStyle.VERTICAL_CENTER
 HSSFCell cell = row.createCell();
 cell.setCellStyle(style);


 创建字体
 HSSFFont font=workbook.createFont();
 font.setBoldweight(HSSFont.BOLDWEIGHT_BOLD);//加粗
 font.setFontHeightInPoints((short)16);/字体大小
 加载字体，样式来加载
 style.setFont(font);
 style.setFIllPattern(HSSFCellStyle.DIAMONS);//设置填充色彩
 style.setFillForegroundColor(HSSFColor.red.index);



----------------------------------------------------------------------------------------------------------------------------------------------------------------




导入样例


public void importExcel(File userExcel, String userExcelFileName) {
try {
FileInputStream fileInputStream = new FileInputStream(userExcel);
boolean is03Excel = userExcelFileName.matches("^.+\\.(?i)(xls)$"); //如果文件的格式没有特殊要求，就可以忽略
//1、读取工作簿
Workbook workbook = isOldExcel ? new HSSFWorkbook(fileInputStream):new XSSFWorkbook(fileInputStream);
//2、读取工作表
Sheet sheet = workbook.getSheetAt(0);//多个工作表怎么办,逐个读取
//3、读取行
if(sheet.getPhysicalNumberOfRows() > 2){//excel表格上传的时候是预知的，所以做校验要逐行校验,且标题也要校验,注意去空格
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());
}
//默认用户密码为 123456
user.setPassword("123456");
//默认用户状态为 有效
user.setState(User.USER_STATE_VALID);

//5、保存用户
save(user);
}
}
workbook.close();
fileInputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}








导出例子


public class ExcelUtil {


/**
* 导出用户的所有列表到excel
* @param userList 用户列表
* @param outputStream 输出流
*/
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;
}


}

代码是依葫芦画瓢，按需编辑。