简单的操作 附 Maven 配置
- <span style="white-space:pre"> </span><dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi</artifactId>
- <version>3.9</version>
- </dependency>
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi-excelant</artifactId>
- <version>3.9</version>
- </dependency>
- POIFSFileSystem excelFile = new POIFSFileSystem(new FileInputStream("E:/sellOrder.xls"));
- HSSFWorkbook wb = new HSSFWorkbook(excelFile);
用以上语句导出excel的时候报错:
信息: Request processing failed; nested exception is org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. POI only supports OLE2 Office documents
信息: Request processing failed; nested exception is org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. POI only supports OLE2 Office documents
原因是:
HSSFWorkbook:是操作Excel2003以前(包括2003)的版本,扩展名是.xls
XSSFWorkbook:是操作Excel2007的版本,扩展名是.xlsx
HSSFWorkbook:是操作Excel2003以前(包括2003)的版本,扩展名是.xls
XSSFWorkbook:是操作Excel2007的版本,扩展名是.xlsx
- import java.io.FileInputStream;
- import java.io.FileNotFoundException;
- import java.io.FileOutputStream;
- import java.io.IOException;
- import java.util.Date;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFCellStyle;
- import org.apache.poi.hssf.usermodel.HSSFDataFormat;
- import org.apache.poi.hssf.usermodel.HSSFRow;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import org.apache.poi.hssf.util.HSSFColor;
- import org.apache.poi.xssf.usermodel.XSSFRow;
- import org.apache.poi.xssf.usermodel.XSSFSheet;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- public class ExcelTest {
- public static void crateExcel(){
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet = wb.createSheet("new sheet");
- HSSFRow row = sheet.createRow(0);
- HSSFCell cell = row.createCell(0);
- cell.setCellValue("Name");
- HSSFCell cell2 = row.createCell(1);
- cell2.setCellValue("Neal");
- HSSFCell cell3 = row.createCell(2);
- cell3.setCellValue(true);
- HSSFCellStyle cellStyle = wb.createCellStyle();
- cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
- HSSFCell dCell = row.createCell(3);
- dCell.setCellValue(new Date());
- dCell.setCellStyle(cellStyle);
- HSSFCellStyle backageStyle = wb.createCellStyle();
- backageStyle.setFillForegroundColor(new HSSFColor.GREY_25_PERCENT().getIndex());
- HSSFCell cell5 = row.createCell(4);
- cell5.setCellValue("Color");
- cell5.setCellStyle(backageStyle);
- HSSFCellStyle backageStyle2 = wb.createCellStyle();
- backageStyle2.setFillForegroundColor(new HSSFColor.GREY_40_PERCENT().getIndex());
- backageStyle2.setBorderBottom((short)1);
- backageStyle2.setBorderTop((short)1);
- backageStyle2.setBorderLeft((short)1);
- backageStyle2.setBorderRight((short)1);
- HSSFCell cell6 = row.createCell(5);
- cell6.setCellValue("Color2");
- cell6.setCellStyle(backageStyle2);
- // Number Formart
- HSSFCellStyle numberFormart = wb.createCellStyle();
- numberFormart.setDataFormat(HSSFDataFormat.getBuiltinFormat(",###"));
- HSSFCell cell7 = row.createCell(6);
- cell7.setCellValue((double)1111111111.11111);
- cell7.setCellStyle(numberFormart);
- try {
- FileOutputStream fileOutputStream = new FileOutputStream("workbooks.xls");
- wb.write(fileOutputStream);
- fileOutputStream.close();
- } catch (FileNotFoundException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- } catch (IOException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- public static void loadExcelByXsl(){
- try {
- HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("workbooks.xls"));
- HSSFSheet sheet = workbook.getSheet("new sheet");
- HSSFRow row1 = sheet.getRow(0);
- if(row1!=null){
- System.out.println(row1.getCell(0).getStringCellValue());
- }
- HSSFRow row2 = sheet.getRow(1);
- if(row2 == null){
- System.out.println("Null");
- }
- } catch (FileNotFoundException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- } catch (IOException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- public static void loadExcelByXslx(){
- try {
- XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("workbooks.xlsx"));
- XSSFSheet sheet = workbook.getSheet("new sheet");
- XSSFRow row1 = sheet.getRow(0);
- if(row1!=null){
- System.out.println(row1.getCell(0).getStringCellValue());
- }
- XSSFRow row2 = sheet.getRow(1);
- if(row2 == null){
- System.out.println("Null");
- }
- } catch (FileNotFoundException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- } catch (IOException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- public static void main(String[] args) {
- loadExcelByXslx();
- }
- }
java解析excel解决excel类型问题
//解决excel类型问题,获得数值
public String getValue(Cell cell) {
String value = "";
if(null==cell){
return value;
}
switch (cell.getCellType()) {
//数值型
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
//如果是date类型则 ,获取该cell的date值
Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
value = format.format(date);;
}else {// 纯数字
BigDecimal big=new BigDecimal(cell.getNumericCellValue());
value = big.toString();
//解决1234.0 去掉后面的.0
if(null != value && !"".equals(value.trim())){
String[] item = value.split("[.]");
if(1<item.length&&"0".equals(item[1])){
value=item[0];
}
}
}
break;
//字符串类型
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue().toString();
break;
// 公式类型
case Cell.CELL_TYPE_FORMULA:
//读公式计算值
value = String.valueOf(cell.getNumericCellValue());
if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
value = cell.getStringCellValue().toString();
}
break;
// 布尔类型
case Cell.CELL_TYPE_BOOLEAN:
value = " "+ cell.getBooleanCellValue();
break;
// 空值
case Cell.CELL_TYPE_BLANK:
value = "";
LogUtil.getLogger().error("excel出现空值");
break;
// 故障
case Cell.CELL_TYPE_ERROR:
value = "";
LogUtil.getLogger().error("excel出现故障");
break;
default:
value = cell.getStringCellValue().toString();
}
if("null".endsWith(value.trim())){
value="";
}
return value;
}