POI Excel操作与数据类型处理

来源：互联网发布：matlab plot 矩阵编辑：程序博客网时间：2024/05/17 16:15

简单的操作附 Maven 配置

[html] view plain copy
 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 plain copy
 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;  }  

0 0