/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package apps.poi.xlsx.infoimport.dl.dao;


import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 *
 * @author Administrator
 */
public class ImportExcelDataDfUtil {
    
    //读取
    public static List<HashMap<String,Object> > impData(){
        
        String impData="E:/WjWork/3618med/V2.0/ImportDaliDfWeb/data_import.xlsx";
        
       List<HashMap<String,Object>  >infoList=new ArrayList<HashMap<String,Object> > ();
       
       
       try {
            XSSFWorkbook book=new XSSFWorkbook(impData);
            
            XSSFSheet sheet = book.getSheetAt(0);
            
            //获取行业数
            
            int rows=sheet.getLastRowNum();
            
            for(int i=0;i<rows;i++){
                XSSFRow row = sheet.getRow(i);
                //获取列数
                short lastCellNum = row.getLastCellNum();
                
                System.out.println("读取行："+i);
             HashMap rowMap=new   HashMap<String,Object>();
                
                    for(int k=0;k<lastCellNum;k++){
                        
                        
                        System.out.println("读取列："+k);
                        //获取单元格
                         XSSFCell cell = row.getCell(k);
                            if(cell!=null){
                                int vt=cell.getCellType();
                                
                                if(vt==HSSFCell.CELL_TYPE_BLANK){
                                    
                                    continue;
                                }
                                
                                if(k==0){
                                    String productName=cell.getStringCellValue();
                                    
                                    rowMap.put("productName",productName);
                                }
                                
                                 if(k==1){
                                    String areaName=cell.getStringCellValue();
                                       rowMap.put("areaName",areaName);
                                }
                                 
                                   if(k==2){
                                    String userName=cell.getStringCellValue();
                                    
                                     rowMap.put("userName",userName);
                                }
                                   
                                    if(k==3){
                                        
                                         
                                        
                                        if(vt==HSSFCell.CELL_TYPE_NUMERIC){
                                             double telphone = cell.getNumericCellValue();
                                        System.out.println("列"+ vt+"Str:  "+(long)telphone);
                                        
                                          rowMap.put("telphone",((long)telphone)+"");
                                        }
                                        
                                         if(vt==HSSFCell.CELL_TYPE_STRING){
                                              String telphone=cell.getStringCellValue();
                                     
                                               System.out.println("列"+ vt+"Str:  "+telphone);
                                       
                                              //  rowMap.put("telphone", telphone);
                                               
                                            //   +" db: "+text);
                                         }
                                  //  String mobiName=cell.getRawValue();
                                   
                                }
                                    
                              if(k==4){
                                    
                                       if(vt==HSSFCell.CELL_TYPE_NUMERIC){
                                             Double email=cell.getNumericCellValue();
                                              System.out.println("列"+ vt+"Str:  "+email);
                                        
                                              //   rowMap.put("email", email+"");
                                        }
                                        
                                         if(vt==HSSFCell.CELL_TYPE_STRING){
                                            String email=cell.getStringCellValue();
                                 
                                     
                                       System.out.println("列"+ vt+"Str:  "+email);
                                                    rowMap.put("email", email+"");
                                            //   +" db: "+text);
                                         }
                                    
                                }
                                      
                                if(k==5){
                                  //  Date dateCellValue = cell.getDateCellValue();
                                    
                                       if(vt==HSSFCell.CELL_TYPE_NUMERIC){
                                             Date date=cell.getDateCellValue();
                                        System.out.println("列"+ vt+"Str:  "+date);
                                        
                                         rowMap.put("date", date);
                                        }
                                        
                                         if(vt==HSSFCell.CELL_TYPE_STRING){
                                              String date=cell.getStringCellValue();
                                     
                                       System.out.println("列"+ vt+"Str:  "+date);
                                               
                                            //   +" db: "+text);
                                         }
                                    
                                    
                                }
                                    
                                  
                                //  String rawValue = cell.getRawValue();
                                  
                                  
                            }//end if
                  
                                infoList.add(rowMap);
                         
                    }//end for
            }//end for
           
        } catch (IOException ex) {
            ex.printStackTrace();
            Logger.getLogger(ImportExcelDataDfUtil.class.getName()).log(Level.SEVERE, null, ex);
        }
        
       return infoList;
    }
    
    
   //输出excel
    
   public static void output(){
        // import org.apache.poi.ss.usermodel.*;

Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() };
for(int i=0; i<wbs.length; i++) {
   Workbook wb = wbs[i];
   CreationHelper createHelper = wb.getCreationHelper();

   // create a new sheet
   Sheet s = wb.createSheet();
   // declare a row object reference
   Row r = null;
   // declare a cell object reference
   Cell c = null;
   // create 2 cell styles
   CellStyle cs = wb.createCellStyle();
   CellStyle cs2 = wb.createCellStyle();
   DataFormat df = wb.createDataFormat();

   // create 2 fonts objects
   Font f = wb.createFont();
   Font f2 = wb.createFont();

   // Set font 1 to 12 point type, blue and bold
   f.setFontHeightInPoints((short) 12);
   f.setColor( IndexedColors.RED.getIndex() );
   f.setBoldweight(Font.BOLDWEIGHT_BOLD);

   // Set font 2 to 10 point type, red and bold
   f2.setFontHeightInPoints((short) 10);
   f2.setColor( IndexedColors.RED.getIndex() );
   f2.setBoldweight(Font.BOLDWEIGHT_BOLD);

   // Set cell style and formatting
   cs.setFont(f);
   cs.setDataFormat(df.getFormat("#,##0.0"));

   // Set the other cell style and formatting
   cs2.setBorderBottom(cs2.BORDER_THIN);
   cs2.setDataFormat(df.getFormat("text"));
   cs2.setFont(f2);


   // Define a few rows
   for(int rownum = 0; rownum < 30; rownum++) {
        r = s.createRow(rownum);
       for(int cellnum = 0; cellnum < 10; cellnum += 2) {
            c = r.createCell(cellnum);
           Cell c2 = r.createCell(cellnum+1);
   
           c.setCellValue((double)rownum + (cellnum/10));
           c2.setCellValue(
                 createHelper.createRichTextString("Hello! " + cellnum)
           );
       }
   }

   // Save
   String filename = "E:/WjWork/3618med/V2.0/ImportDaliDfWeb/workbook.xls";
   if(wb instanceof XSSFWorkbook) {
     filename = filename + "x";
   }
   
   
 
   FileOutputStream out;
    try {
        
        
        out = new FileOutputStream(filename);
         wb.write(out);
         
   out.close();
   
   
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ImportExcelDataDfUtil.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(ImportExcelDataDfUtil.class.getName()).log(Level.SEVERE, null, ex);
    }
    
     
 
    }

   }
   
   public static void main(String argvs[]){
       //  output();
        impData();
   }
}