POI Excel操作与数据类型处理

最新推荐文章于 2024-06-26 13:49:00 发布

CODE男孩

最新推荐文章于 2024-06-26 13:49:00 发布

阅读量659

点赞数

分类专栏： Java

本文链接：https://blog.csdn.net/qq_24084925/article/details/53992094

版权

Java 专栏收录该内容

39 篇文章 0 订阅

订阅专栏

简单的操作附 Maven 配置

[html]view plaincopy 
   
print?
 <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

原因是：
HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx

[java]view plaincopy 
   
print?
 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;  
}   
  
 
  
 
 