java poi技术操作excel之读取Excel

Biegral 2018/12/28 5:00:07 2363

  1. import java.io.BufferedWriter;  
  2. import java.io.File;  
  3. import java.io.FileInputStream;  
  4. import java.io.FileWriter;  
  5. import java.io.IOException;  
  6. import java.io.InputStream;  
  7. import java.text.SimpleDateFormat;  
  8.   
  9. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
  10. import org.apache.poi.ss.usermodel.Cell;  
  11. import org.apache.poi.ss.usermodel.DateUtil;  
  12. import org.apache.poi.ss.usermodel.Row;  
  13. import org.apache.poi.ss.usermodel.Sheet;  
  14. import org.apache.poi.ss.usermodel.Workbook;  
  15. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
  16.   
  17. public class ReadWriteExcel {  
  18.   
  19.     private static final String EXCEL_XLS = "xls";  
  20.     private static final String EXCEL_XLSX = "xlsx";  
  21.   
  22.     /** 
  23.      * 判断Excel的版本,获取Workbook 
  24.      * @param in 
  25.      * @param filename 
  26.      * @return 
  27.      * @throws IOException 
  28.      */  
  29.     public static Workbook getWorkbok(InputStream in,File file) throws IOException{  
  30.         Workbook wb = null;  
  31.         if(file.getName().endsWith(EXCEL_XLS)){  //Excel 2003  
  32.             wb = new HSSFWorkbook(in);  
  33.         }else if(file.getName().endsWith(EXCEL_XLSX)){  // Excel 2007/2010  
  34.             wb = new XSSFWorkbook(in);  
  35.         }  
  36.         return wb;  
  37.     }  
  38.   
  39.     /** 
  40.      * 判断文件是否是excel 
  41.      * @throws Exception  
  42.      */  
  43.     public static void checkExcelVaild(File file) throws Exception{  
  44.         if(!file.exists()){  
  45.             throw new Exception("文件不存在");  
  46.         }  
  47.         if(!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))){  
  48.             throw new Exception("文件不是Excel");  
  49.         }  
  50.     }  
  51.   
  52.     /** 
  53.      * 读取Excel测试，兼容 Excel 2003/2007/2010 
  54.      * @throws Exception  
  55.      */  
  56.     public static void main(String[] args) throws Exception {  
  57.         SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");  
  58.         BufferedWriter bw = new BufferedWriter(new FileWriter(new File("E:/xxx/InsertSql.txt")));  
  59.         try {  
  60.             // 同时支持Excel 2003、2007  
  61.             File excelFile = new File("E:/xxx.xlsx"); // 创建文件对象  
  62.             FileInputStream is = new FileInputStream(excelFile); // 文件流  
  63.             checkExcelVaild(excelFile);  
  64.             Workbook workbook = getWorkbok(is,excelFile);  
  65.             //Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel2003/2007/2010都是可以处理的  
  66.   
  67.             int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量  
  68.             /** 
  69.              * 设置当前excel中sheet的下标：0开始 
  70.              */  
  71.             Sheet sheet = workbook.getSheetAt(0);   // 遍历第一个Sheet  
  72.   
  73.             // 为跳过第一行目录设置count  
  74.             int count = 0;  
  75.   
  76.             for (Row row : sheet) {  
  77.                 // 跳过第一行的目录  
  78.                 if(count == 0){  
  79.                     count++;  
  80.                     continue;  
  81.                 }  
  82.                 // 如果当前行没有数据，跳出循环  
  83.                 if(row.getCell(0).toString().equals("")){  
  84.                     return ;  
  85.                 }  
  86.                 String rowValue = "";  
  87.                 for (Cell cell : row) {  
  88.                     if(cell.toString() == null){  
  89.                         continue;  
  90.                     }  
  91.                     int cellType = cell.getCellType();  
  92.                     String cellValue = "";  
  93.                     switch (cellType) {  
  94.                         case Cell.CELL_TYPE_STRING:     // 文本  
  95.                             cellValue = cell.getRichStringCellValue().getString() + "#";  
  96.                             break;  
  97.                         case Cell.CELL_TYPE_NUMERIC:    // 数字、日期  
  98.                             if (DateUtil.isCellDateFormatted(cell)) {  
  99.                                 cellValue = fmt.format(cell.getDateCellValue()) + "#";  
  100.                             } else {  
  101.                                 cell.setCellType(Cell.CELL_TYPE_STRING);  
  102.                                 cellValue = String.valueOf(cell.getRichStringCellValue().getString()) + "#";  
  103.                             }  
  104.                             break;  
  105.                         case Cell.CELL_TYPE_BOOLEAN:    // 布尔型  
  106.                             cellValue = String.valueOf(cell.getBooleanCellValue()) + "#";  
  107.                             break;  
  108.                         case Cell.CELL_TYPE_BLANK: // 空白  
  109.                             cellValue = cell.getStringCellValue() + "#";  
  110.                             break;  
  111.                         case Cell.CELL_TYPE_ERROR: // 错误  
  112.                             cellValue = "错误#";  
  113.                             break;  
  114.                         case Cell.CELL_TYPE_FORMULA:    // 公式  
  115.                             // 得到对应单元格的公式  
  116.                             //cellValue = cell.getCellFormula() + "#";  
  117.                             // 得到对应单元格的字符串  
  118.                             cell.setCellType(Cell.CELL_TYPE_STRING);  
  119.                             cellValue = String.valueOf(cell.getRichStringCellValue().getString()) + "#";  
  120.                             break;  
  121.                         default:  
  122.                             cellValue = "#";  
  123.                     }  
  124.                     //System.out.print(cellValue);  
  125.                     rowValue += cellValue;  
  126.                 }  
  127.                 writeSql(rowValue,bw);  
  128.                 System.out.println(rowValue);  
  129.                 System.out.println();  
  130.             }  
  131.             bw.flush();  
  132.         } catch (Exception e) {  
  133.             e.printStackTrace();  
  134.         } finally{  
  135.             bw.close();  
  136.         }  
  137.     }  
  1. // 将值拼成sql语句  
  2.     public static void writeSql(String rowValue,BufferedWriter bw) throws IOException{  
  3.         String[] sqlValue = rowValue.split("#");  
  4.         String sql = "";  
  5.         sql="INSERT INTO table_name (列名1) VALUES("+ sqlValue[0].trim() + ");"+"\n";  
  6.         System.out.print(sql);  
  7.         try {  
  8.             bw.write(sql);  
  9.             bw.newLine();  
  10.         } catch (IOException e) {  
  11.             e.printStackTrace();  
  12.         }  
  13.     }  
本文转载:CSDN博客

阅读排行

分类

归档