关闭

POI Excel操作与数据类型处理

194人阅读 评论(0) 收藏 举报
分类:

简单的操作 附 Maven 配置

[html] view plain copy
 print?
  1. <span style="white-space:pre">      </span><dependency>  
  2.             <groupId>org.apache.poi</groupId>  
  3.             <artifactId>poi</artifactId>  
  4.             <version>3.9</version>  
  5.         </dependency>  
  6.         <dependency>  
  7.             <groupId>org.apache.poi</groupId>  
  8.             <artifactId>poi-excelant</artifactId>  
  9.             <version>3.9</version>  
  10.         </dependency>  
注:
  1. POIFSFileSystem excelFile = new POIFSFileSystem(new FileInputStream("E:/sellOrder.xls"));
  2. 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
 
原因是:
HSSFWorkbook:是操作Excel2003以前(包括2003)的版本,扩展名是.xls 
XSSFWorkbook:是操作Excel2007的版本,扩展名是.xlsx



[java] view plain copy
 print?
  1. import java.io.FileInputStream;  
  2. import java.io.FileNotFoundException;  
  3. import java.io.FileOutputStream;  
  4. import java.io.IOException;  
  5. import java.util.Date;  
  6.   
  7. import org.apache.poi.hssf.usermodel.HSSFCell;  
  8. import org.apache.poi.hssf.usermodel.HSSFCellStyle;  
  9. import org.apache.poi.hssf.usermodel.HSSFDataFormat;  
  10. import org.apache.poi.hssf.usermodel.HSSFRow;  
  11. import org.apache.poi.hssf.usermodel.HSSFSheet;  
  12. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
  13. import org.apache.poi.hssf.util.HSSFColor;  
  14. import org.apache.poi.xssf.usermodel.XSSFRow;  
  15. import org.apache.poi.xssf.usermodel.XSSFSheet;  
  16. import org.apache.poi.xssf.usermodel.XSSFWorkbook;    
  17.   
  18. public class ExcelTest {  
  19.       
  20.     public static void crateExcel(){  
  21.         HSSFWorkbook wb = new HSSFWorkbook();  
  22.         HSSFSheet sheet = wb.createSheet("new sheet");  
  23.         HSSFRow row = sheet.createRow(0);  
  24.         HSSFCell cell = row.createCell(0);  
  25.         cell.setCellValue("Name");  
  26.           
  27.         HSSFCell cell2 = row.createCell(1);  
  28.         cell2.setCellValue("Neal");  
  29.           
  30.         HSSFCell cell3 = row.createCell(2);  
  31.         cell3.setCellValue(true);  
  32.           
  33.         HSSFCellStyle cellStyle = wb.createCellStyle();  
  34.         cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));  
  35.         HSSFCell dCell = row.createCell(3);  
  36.         dCell.setCellValue(new Date());  
  37.         dCell.setCellStyle(cellStyle);  
  38.           
  39.         HSSFCellStyle backageStyle = wb.createCellStyle();  
  40.         backageStyle.setFillForegroundColor(new HSSFColor.GREY_25_PERCENT().getIndex());  
  41.         HSSFCell cell5 = row.createCell(4);  
  42.         cell5.setCellValue("Color");  
  43.         cell5.setCellStyle(backageStyle);  
  44.           
  45.         HSSFCellStyle backageStyle2 = wb.createCellStyle();  
  46.         backageStyle2.setFillForegroundColor(new HSSFColor.GREY_40_PERCENT().getIndex());  
  47.         backageStyle2.setBorderBottom((short)1);  
  48.         backageStyle2.setBorderTop((short)1);  
  49.         backageStyle2.setBorderLeft((short)1);  
  50.         backageStyle2.setBorderRight((short)1);  
  51.           
  52.         HSSFCell cell6 = row.createCell(5);  
  53.         cell6.setCellValue("Color2");  
  54.         cell6.setCellStyle(backageStyle2);  
  55.           
  56.         // Number Formart  
  57.         HSSFCellStyle numberFormart = wb.createCellStyle();  
  58.         numberFormart.setDataFormat(HSSFDataFormat.getBuiltinFormat(",###"));  
  59.         HSSFCell cell7 = row.createCell(6);  
  60.         cell7.setCellValue((double)1111111111.11111);  
  61.         cell7.setCellStyle(numberFormart);  
  62.           
  63.           
  64.         try {  
  65.             FileOutputStream fileOutputStream = new FileOutputStream("workbooks.xls");  
  66.             wb.write(fileOutputStream);  
  67.             fileOutputStream.close();  
  68.         } catch (FileNotFoundException e) {  
  69.             // TODO Auto-generated catch block  
  70.             e.printStackTrace();  
  71.         } catch (IOException e) {  
  72.             // TODO Auto-generated catch block  
  73.             e.printStackTrace();  
  74.         }  
  75.           
  76.     }  
  77.       
  78.     public static void loadExcelByXsl(){  
  79.         try {  
  80.             HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("workbooks.xls"));  
  81.             HSSFSheet sheet = workbook.getSheet("new sheet");  
  82.             HSSFRow row1 = sheet.getRow(0);  
  83.             if(row1!=null){  
  84.                 System.out.println(row1.getCell(0).getStringCellValue());  
  85.             }  
  86.             HSSFRow row2 = sheet.getRow(1);  
  87.             if(row2 == null){  
  88.                 System.out.println("Null");  
  89.             }  
  90.               
  91.         } catch (FileNotFoundException e) {  
  92.             // TODO Auto-generated catch block  
  93.             e.printStackTrace();  
  94.         } catch (IOException e) {  
  95.             // TODO Auto-generated catch block  
  96.             e.printStackTrace();  
  97.         }  
  98.     }  
  99.     public static void loadExcelByXslx(){  
  100.         try {  
  101.             XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("workbooks.xlsx"));  
  102.             XSSFSheet sheet = workbook.getSheet("new sheet");  
  103.             XSSFRow row1 = sheet.getRow(0);  
  104.             if(row1!=null){  
  105.                 System.out.println(row1.getCell(0).getStringCellValue());  
  106.             }  
  107.             XSSFRow row2 = sheet.getRow(1);  
  108.             if(row2 == null){  
  109.                 System.out.println("Null");  
  110.             }  
  111.               
  112.         } catch (FileNotFoundException e) {  
  113.             // TODO Auto-generated catch block  
  114.             e.printStackTrace();  
  115.         } catch (IOException e) {  
  116.             // TODO Auto-generated catch block  
  117.             e.printStackTrace();  
  118.         }  
  119.     }  
  120.       
  121.       
  122.     public static void main(String[] args) {  
  123.         loadExcelByXslx();  
  124.     }  
  125.       
  126. }  



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;  
}  


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:111913次
    • 积分:3511
    • 等级:
    • 排名:第10636名
    • 原创:253篇
    • 转载:42篇
    • 译文:0篇
    • 评论:1条