POI操作EXCEL

标签: stringnullimportexceptionexcellist
1813人阅读 评论(0) 收藏 举报

package com.epro.xml;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * Excel2XMLProcess
 *
 * @author LHJ
 *
 */
public class Excel2XMLProcess {
 /**
  *
  * @param String filename
  * @return Collection dataList
  */
 public static List run(String filename) throws IOException{
  List dataList=new ArrayList();
  try {
   int sheetnum = 0;
   int lastrow = 0;
   int rownum = 0;
   HSSFSheet sheet = null;
   HSSFRow row = null;
   HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
     filename));
   sheet = workbook.getSheetAt(sheetnum);
   lastrow = sheet.getLastRowNum();
   //
   for (rownum = 1; rownum <= lastrow; rownum++) {
    row = sheet.getRow(rownum);
    if (row != null) {
     short firstcell = row.getFirstCellNum();
     short lastcell = row.getLastCellNum();
     short cellnum;
     HSSFCell cell = null;
     XMLBean dataBean=new XMLBean();
     String no = String.valueOf(rownum);
     dataBean.setNo(no);
     //System.out.print(no);
     for (cellnum = firstcell; cellnum < lastcell; cellnum++) {
      cell = row.getCell(cellnum);
      switch(cellnum){
      //Levelを取得します。
      case 1:
       if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
        //System.out.print(addZero(String.valueOf((int)cell.getNumericCellValue()),2));
        //Levelを設定します。
        dataBean.setLevel(addZero(String.valueOf((int)cell.getNumericCellValue()),2));
       }
       break;
      //項目名を取得します。
      case 2:
        if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
         //System.out.print(cell.getStringCellValue());
         //項目名を設定します。
         dataBean.setName(fullToHalf(cell.getStringCellValue()).trim());
           }
        break;
         //属性を取得します。
      case 3:
        if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
         //System.out.print(cell.getStringCellValue());
         //属性を設定します。
         dataBean.setType(cell.getStringCellValue());
           }
        break;
      //桁数を取得します。
      case 4:
       if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
        //System.out.print(String.valueOf((int)cell.getNumericCellValue()));
        //桁数を設定します。
        dataBean.setLength(String.valueOf((int)cell.getNumericCellValue()));
       }
       break;
      //再定義を取得します。
      case 5:
        if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
         //System.out.print(cell.getStringCellValue());
         //再定義を設定します。
         dataBean.setRedefine(cell.getStringCellValue());
           }
        break;
      //繰返しを取得します。
      case 6:
       if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
        //System.out.print(String.valueOf((int)cell.getNumericCellValue()));
        ////繰返しを設定します。
        dataBean.setOccures(String.valueOf((int)cell.getNumericCellValue()));
       }
       break;
      default: ;
       }
    }
     dataList.add(dataBean);
     //System.out.println();
    }
   }
  } catch (IOException e) {
   throw e;
   //System.out.println(e);
  }
  return dataList;
 }
// /**
//  * main method
//  * @param args
//  */
// public static void main(String[] args) {
//
//  Excel2XMLProcess.run("excel/cdbhosyo.xls");
//
// }
 /**
  * 0を追加
  * @param String level
  * @param int n
  * @return String tempLevel
  */
 public static String addZero(String level, int n) {
  String tempLevel = level;
  int tempLenght = tempLevel.length();
  for (int i = 0; i < n - tempLenght; i++) {
   tempLevel = "0" + tempLevel;
  }
  return tempLevel;
 }
 /**
  * 全部全角数は半角に変更
  * @param name
  * @return String tempName変更后の半角の字符
  */
 public static String fullToHalf(String name){
  //変更后の字符
  String tempName="";
  //半角number
  String [] halfNumber={"0","1","2","3","4","5","6","7","8","9"};
  //全角number
  String [] fullNumber={"0","1","2","3","4","5","6","7","8","9"};
        //半角char
  String [] halfChar={"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"};;
  //全角char
  String [] fullChar={"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"};

  tempName=name.replaceAll("_","_");
  for(int i=0;i<halfNumber.length;i++){
   tempName=tempName.replaceAll(fullNumber[i],halfNumber[i]);
  }
  for(int i=0;i<halfChar.length;i++){
   tempName=tempName.replaceAll(fullChar[i],halfChar[i]);
  }
  return tempName;
 }
 
// /**
//  * 取データ
//  *
//  * @param file
//  * @return List
//  */
// public List getData(String file) {
//  System.out.println("-----------getData() STA----------------");
//  int recordCount = 0;
//  String[] record;
//  String line;
//  try {
//   FileReader rd = new FileReader(file);
//   BufferedReader buffRd = new BufferedReader(rd);
//   data = new ArrayList();
//   while ((line = buffRd.readLine()) != null) {
//    record = line.split(",", 6);// TODO
//
//    String rcout = String.valueOf(recordCount);
//    XMLBean xmlBean = new XMLBean();
//
//    xmlBean.setNo(rcout);
//    xmlBean.setLevel(record[0]);
//    //xmlBean.setName(record[1]);
//    xmlBean.setName(mk.fullToHalf(record[1]).trim());//TODO
//    xmlBean.setType(record[2]);
//    xmlBean.setLength(record[3]);
//
//    recordCount++;
//
//    data.add(xmlBean);
//   }
//   rd.close();
//   buffRd.close();
//  } catch (IOException ioex) {
//   ioex.printStackTrace();
//  } catch (Exception ex) {
//   ex.printStackTrace();
//  }
//  System.out.println("-----------getData() END----------------");
//  return data;
// }
 
 
 
 
// public static void main(String[] args) {
//  try {
//   FileOutputStream out = new FileOutputStream("resultExcel.txt");
//   out.write(Excel2XMLProcess.run("excel/cdbhosyo.xls").getBytes());
//   out.flush();
//   out.close();
//  } catch (Exception ex) {
//   System.out.println(ex.toString());
//  }
// }
 // public static String run(String filename) {
 // String text = null;
 // try {
 //
 // int sheetnum = 0;
 // HSSFSheet sheet = null;
 // HSSFRow row = null;
 // HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
 // filename));
 // for (sheetnum = 0; sheetnum < workbook.getNumberOfSheets(); sheetnum++) {
 // sheet = workbook.getSheetAt(sheetnum);
 // int lastrow = 0;
 // int rownum = 0;
 // lastrow = sheet.getLastRowNum();
 // for (rownum = 0; rownum <= lastrow; rownum++) {
 // row = sheet.getRow(rownum);
 // if (row != null) {
 // short firstcell = row.getFirstCellNum();
 // short lastcell = row.getLastCellNum();
 // short cellnum;
 // HSSFCell cell = null;
 // for (cellnum = firstcell; cellnum < lastcell; cellnum++) {
 // cell = row.getCell(cellnum);
 // if (cell != null
 // && cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
 // text = text + cell.getNumericCellValue();
 // else if (cell != null
 // && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
 // text = text + cell.getStringCellValue();
 // }
 // }
 // text = text + "/n";
 // }
 // }
 // }
 // } catch (Exception e) {
 // System.out.println(e);
 // }
 // return text;
 // }
}

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:2247741次
    • 积分:20714
    • 等级:
    • 排名:第366名
    • 原创:287篇
    • 转载:205篇
    • 译文:3篇
    • 评论:439条
    最新评论