java poi 匯入 Excel的cell類型

標籤：sts   throw   else   alt   _for   from   tee   user   ...   

(1):CellType 類型值　　CELL_TYPE_NUMERIC 數值型 0　　CELL_TYPE_STRING 字串型 1　　CELL_TYPE_FORMULA 公式型 2　　CELL_TYPE_BLANK 空值 3　　CELL_TYPE_BOOLEAN 布爾型 4　　CELL_TYPE_ERROR 錯誤 5

（2） SimpleDateFormat sdf1 =  new  SimpleDateFormat( "yyyy-MM-dd hh:mm" ); //12小時制（3） SimpleDateFormat sdf2 =  new  SimpleDateFormat( "yyyy-MM-dd HH:mm" ); //24小時制  （4）java包

package com.common;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.text.DecimalFormat;import java.text.NumberFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.Iterator;import java.util.List;import java.util.Map;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.HSSFDateUtil;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.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class UploadExcel {public static void main(String[] args) throws IOException { UploadExcel.readExcel();//UploadExcel.writeExcel();}//對日期格式//數字類型的格式化public static String getFormatKey(short formatNumber, Date date){String format = "" ;SimpleDateFormat sdf = null;if(formatNumber == HSSFDataFormat.getBuiltinFormat("h:mm")){sdf = new SimpleDateFormat("HH:mm");}else if(formatNumber == HSSFDataFormat.getBuiltinFormat("m/d/yy")){sdf = new SimpleDateFormat("yyyy-MM-dd");}else if(formatNumber == HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")){sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");}else{sdf = new SimpleDateFormat("MM-dd");}format = sdf.format(date);return format;}//讀excelpublic static void readExcel() throws IOException{  String excelFilePath = "F://a.xlsx";        FileInputStream inputStream = new FileInputStream(new File(excelFilePath));                //讀寫xls和xlsx格式時，HSSFWorkbook針對xls，XSSFWorkbook針對xlsx                Workbook workbook = null;                 int index = excelFilePath.lastIndexOf(".");        String suffix = excelFilePath.substring(index + 1).toLowerCase();                if ("xls".equals(suffix))        {          workbook = new HSSFWorkbook(inputStream);                     }        else if("xlsx".equals(suffix))        {          workbook = new XSSFWorkbook(inputStream);          }          else        {        System.err.println("錯誤");        }        Sheet firstSheet = workbook.getSheetAt(0);                Iterator<Row> iterator = firstSheet.iterator();                int  i = 0;         while (iterator.hasNext()) {                System.out.print("序號:" + (i++) + "");                    Row nextRow = iterator.next();            Iterator<Cell> cellIterator = nextRow.cellIterator();                         while (cellIterator.hasNext()) {                Cell cell = cellIterator.next();                                switch (cell.getCellType()) {                                    case Cell.CELL_TYPE_BOOLEAN:                    {                    System.out.print(cell.getBooleanCellValue());                        break;                    }                    case Cell.CELL_TYPE_NUMERIC:                    {                     short format = cell.getCellStyle().getDataFormat(); //擷取類型值                                         String printString = "";                     if( format == 14 || format == 20 || format == 22                     || format == 31 || format == 32 || format == 57 || format == 58)                     {                     printString  =  UploadExcel.getFormatKey(format, cell.getDateCellValue());                     }                     else                     {                    DecimalFormat df = new DecimalFormat("#.#");                    printString = df.format(cell.getNumericCellValue());                      }                                          System.out.print(printString);                                          break;                    }                    case Cell.CELL_TYPE_STRING:                    {                        System.out.print(cell.getStringCellValue());                        break;                    }                    default :                     {                    System.out.print(cell.getStringCellValue());                    }                                    }                System.out.print("|");            }            System.out.println("###");        }                 workbook.close();        inputStream.close();}//寫excelpublic static void writeExcel(){try {FileOutputStream fileOut = new FileOutputStream("F://poi-test.xls");HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet worksheet = workbook.createSheet("POI Worksheet");// index from 0,0... cell A1 is cell(0,0)HSSFRow row1 = worksheet.createRow((short) 0);HSSFCell cellA1 = row1.createCell((short) 0);cellA1.setCellValue("Hello");HSSFCellStyle cellStyle = workbook.createCellStyle();cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);cellA1.setCellStyle(cellStyle);HSSFCell cellB1 = row1.createCell((short) 1);cellB1.setCellValue("Goodbye");cellStyle = workbook.createCellStyle();cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);cellB1.setCellStyle(cellStyle);HSSFCell cellC1 = row1.createCell((short) 2);cellC1.setCellValue(true);HSSFCell cellD1 = row1.createCell((short) 3);cellD1.setCellValue(new Date());cellStyle = workbook.createCellStyle();cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));cellD1.setCellStyle(cellStyle);workbook.write(fileOut);fileOut.flush();fileOut.close();System.out.println("讀取結束");} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}}}

　　

 

java poi 匯入 Excel的cell類型