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;
// }
}