jxl poi 对xls 的读写,实例较量
????????? 最近公司要扫描全部xls 文件，xls 是一个单，并且将xls的单数据 全部写入一个xls 中! 心里一热，采用jxl 和poi 两种方式进行读取，其实就是解决xls 2003 和xls 2007 版本问题，再次晒出代码，希望与更多人交流，QQ:962589149
?
public class XlsManger {
? /**
?? * @author 樱花无罪
?? * @version 2012-09-18
?? * @see xls 操作类
?? */
?public XlsManger(){
??this.allData=new ArrayList();
?}
?private static List allData=new ArrayList();//全家记录条数
?private static boolean? isin=true;//数量是否in
?private static boolean? isin2=true;
?public static void main(String args[]) throws Exception
?{
???
??allData=new ArrayList();
??XlsManger xls=new XlsManger();
??//xls.read2007XSSF(new File("C:\\test"),"16888.xlsx");
??//xls.read2007XSSF(new File("C:\\test"),"16888.xlsx");
???? //xls.read2007HSSF(new File("C:\\test"),"Book1.xls");
??//System.out.println(xls.getTitle(new File("C:\\test\\configtxt.txt")));
??
??//xls.readXlsJXL(File dir,String filename) throws Exception
??
??String title=xls.getTitle(new File("C:\\test\\configtxt.txt"));//所有列标题
??System.out.println(title);
??String[] titledata=title.split("#");
??String outpath="C:\\test\\workbook.xls";
??List alldata=xls.readXls(new File("C:\\test"),0,titledata[2].split(","));//所有数据
??System.out.println(alldata.size()+"==========所有记录条数===========");
??if(xls.readIntoXls(titledata[1].split(","), alldata, outpath))
??{
???System.out.println("合并成功");
??}else
??{
???System.out.println("合并失败");
??}
??
?}
?public boolean beginXls()
?{
??try
??{
???XlsManger xls=new XlsManger();
???String title=xls.getTitle(new File("C:\\test\\configtxt.txt"));//所有列标题
???System.out.println(title);
???String[] titledata=title.split("#");
???String outpath="C:\\test\\workbook.xls";
???List alldata=xls.readXls(new File("C:\\test"),0,titledata[2].split(","));//所有数据
???System.out.println(alldata.size()+"======所有记录条数===========");
???if(xls.readIntoXls(titledata[1].split(","), alldata, outpath))
???{
????return true;
???}else
???{
????return false;
???}
???
??}catch(Exception ex)
??{
???ex.printStackTrace();
??}
??return false;
?}
?//初始化表头
?public String getTitle(File file)
?{
??String title="111";
??BufferedReader reader = null;
??if(file.exists())
??{
???try {
???????????? System.out.println("以行为单位读取文件内容，一次读一整行：");
???????????? reader = new BufferedReader(new FileReader(file));
???????????? String tempString = null;
???????????? int line = 1;
???????????? // 一次读入一行，直到读入null为文件结束
???????????? while ((tempString = reader.readLine()) != null) {
???????????? ?title=title+"#"+tempString;
???????????????? line++;
???????????? }
???????????? reader.close();
???????? } catch (IOException e) {
???????????? e.printStackTrace();
???????? } finally {
???????????? if (reader != null) {
???????????????? try {
???????????????????? reader.close();
???????????????? } catch (IOException e1) {
???????????????? }
???????????? }
???????? }
??}else
??{
???title="表头配置无法获取";
??}
??return title;
?}
?//初始2007阅读器
?public static List read2007XSSF(File dir,String filename) throws Exception
?{
??List datalist=new ArrayList();
??if(dir.exists())//文件存在
??{
???XSSFWorkbook xwb = null;?
???try {?
??????????????? xwb = new XSSFWorkbook(dir.getPath() +"\" + filename);
??????????????? XSSFFormulaEvaluator evaluator? = new XSSFFormulaEvaluator(xwb);
??????????????? //获取xwb 文件工作表个数
??????????????? int sheetcount=xwb.getNumberOfSheets();
??????????????? //System.out.println("一共有"+sheetcount+"表");
??????????????? // 读取第一章表格内容???
??????????????? StringBuffer rowStr=null;
??????????????? for(int s=0;s<sheetcount;s++)
??????????????? {
??????????????? ?XSSFSheet sheet = xwb.getSheetAt(s);
??????????????? ?//获取行数
??????????????? ?XSSFRow row=null;
??????????????? ?XSSFCell cell=null;
??????????????? ?rowStr=new StringBuffer();//行容器
??????????? ??rowStr.append("文件名,");
??????????? ??rowStr.append(dir.getPath() +"\" + filename+",");
??????????????? ? for (int i = sheet.getFirstRowNum()+1; i < sheet.getPhysicalNumberOfRows(); i++) {????
??? ????????????????? row = sheet.getRow(i);
??? ?????????????????
??? ????????????????? if(row!=null){//行不为空
??? ????????????????? for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
??? ???????????????? ?? cell=row.getCell(j);
??? ???????????????? ?? CellValue cellValue = evaluator.evaluate(cell);
??? ???????????????? ?? String restr=formart(cell,cellValue);
???????????????? ??? if(restr!=null)
???????????????? ??? {
???????????????? ???? if(restr.equals("排版尺寸")&&rowStr.indexOf("排版尺寸")!=-1)
???????????????? ???? {
???????????????? ?????
???????????????? ???? }else if(restr.equals("数量"))
???????????????? ???? {
???????????????? ????? if(isin)
???????????????? ????? {
???????????????? ?????? rowStr.append(restr+",");
???????????????? ?????? isin=false;
???????????????? ????? }
???????????????? ???? } else if(restr.equals("裁"))
???????????????? ???? {
???????????????? ????? if(isin2)
???????????????? ????? {
???????????????? ?????? rowStr.append(restr+",");
???????????????? ?????? isin2=false;
???????????????? ????? }
???????????????? ???? }else
???????????????? ????
???????????????? ???? {
???????????????? ?????? //System.out.println(formartHSSF(cell,cellValue)+":"+cellValue.getNumberValue());
??????????????????????????????????? rowStr.append(restr+",");
???????????????? ???? }
???????????????? ??? }?
??? ????????????????? ?}
??? ????????????????? }
??????????????? ? }
????????????????? // 每个表为单位
??????????????? ? //System.out.println("第"+s+"表"+rowStr);
????????????????? if(rowStr!=null&&rowStr.toString().trim().length()!=0)
????????????????? {
???????????????? ? if(rowStr.toString().toLowerCase().indexOf("ltd")!=-1)
???????????????? ? {
???????????????? ??
???????????????? ? }else
???????????????? ? {
???????????????? ??// System.out.println(rowStr+"888888888888888888888888");
???????????????? ?? datalist.add(rowStr);//将文件的所有记录保存到list
???????????????? ? }
????????????????? }
?????????????????
?????????????????
??????????????? ?
??????????????? }
??????????? } catch (IOException e) {
??????????????? e.printStackTrace();?
??????????? }?????
??}
??//System.out.println("文件"+dir+"获取的记录条数"+datalist.size());
??return datalist;
?}
?//初始2007列格式问题
?public? static String formart(XSSFCell cell,CellValue cellValue)
?{
??switch (cell.getCellType()) {??
??????? case XSSFCell.CELL_TYPE_NUMERIC: // 数字??
??????????? return String.valueOf(cell.getNumericCellValue());?
??????? case XSSFCell.CELL_TYPE_STRING: // 字符串??
??????????? return cell.getStringCellValue() ;
??????? case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean??
??????????? return String.valueOf(cell.getBooleanCellValue());
??????? case XSSFCell.CELL_TYPE_FORMULA: // 公式???
??????? ?? return String.valueOf(cellValue.getNumberValue());
??case XSSFCell.CELL_TYPE_BLANK: // 空值???
??????????? break;??
??????? case XSSFCell.CELL_TYPE_ERROR: // 故障???
??????????? break;??
??????? default:?
??????????? return cell.getStringCellValue();??
??????? }??
??return null;
?}
?//初始2007列格式问题
?public? static String formartHSSF(HSSFCell cell,CellValue cellValue)
?{
??switch (cell.getCellType()) {??
??????? case HSSFCell.CELL_TYPE_NUMERIC: // 数字??
??????????? return String.valueOf(cell.getNumericCellValue());?
??????? case HSSFCell.CELL_TYPE_STRING: // 字符串??
??????????? return cell.getStringCellValue() ;
??????? case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean??
??????????? return String.valueOf(cell.getBooleanCellValue());
??????? case HSSFCell.CELL_TYPE_FORMULA: // 公式???
??????????? return String.valueOf(cellValue.getNumberValue());
??case HSSFCell.CELL_TYPE_BLANK: // 空值???
??????????? break;??
??????? case HSSFCell.CELL_TYPE_ERROR: // 故障???
??????????? break;??
??????? default:?
??????????? return cell.getStringCellValue();??
??????? }??
??return null;
?}
?//初始化2007 读写方式二
?public static List read2007HSSF(File dir,String filename) throws Exception
?{
??List<StringBuffer> datalist=new ArrayList();
??if(dir.exists())//文件存在
??{
???HSSFWorkbook xwb = null;?
???try {?
????
??????????????? xwb = new HSSFWorkbook(new FileInputStream(new File(dir.getPath() +"\" + filename)));
??????????????? HSSFFormulaEvaluator evaluator? = new HSSFFormulaEvaluator(xwb);
??????????????? //获取xwb 文件工作表个数
??????????????? int sheetcount=xwb.getNumberOfSheets();
??????????????? //System.out.println("一共有"+sheetcount+"表");
??????????????? // 读取第一章表格内容???
??????????????? StringBuffer rowStr=null;
??????????????? for(int s=0;s<sheetcount;s++)
??????????????? {
??????????????? ? rowStr=new StringBuffer();//行容器
??????????????? ? rowStr.append("文件名,");
??????????? ?? rowStr.append(dir.getPath() +"\" + filename+",");
??????????????? ?HSSFSheet sheet = xwb.getSheetAt(s);
??????????????? ?//获取行数
??????????????? ?HSSFRow row=null;
??????????????? ?HSSFCell cell=null;
????????????????? isin=true;
????????????????? isin2=true;
??????????????? ? for (int i = sheet.getFirstRowNum()+1; i < sheet.getPhysicalNumberOfRows(); i++) {????
??? ????????????????? row = sheet.getRow(i);
??? ????????????????? if(row!=null){//行不为空
??? ????????????????? for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
??? ???????????????? ?? cell=row.getCell(j);
??? ???????????????? ?? if(cell!=null)
??? ???????????????? ?? {
??? ???????????????? ??? CellValue cellValue = evaluator.evaluate(cell);
??? ???????????????? ??? String restr=formartHSSF(cell,cellValue);
??? ???????????????? ??? if(restr!=null)
??? ???????????????? ??? {
??? ???????????????? ???? if(restr.equals("排版尺寸")&&rowStr.indexOf("排版尺寸")!=-1)
??? ???????????????? ???? {
??? ???????????????? ?????
??? ???????????????? ???? }else if(restr.equals("数量"))
??? ???????????????? ???? {
??? ???????????????? ????? if(isin)
??? ???????????????? ????? {
??? ???????????????? ?????? rowStr.append(restr+",");
??? ???????????????? ?????? isin=false;
??? ???????????????? ????? }
??? ???????????????? ???? } else if(restr.equals("裁"))
??? ???????????????? ???? {
??? ???????????????? ????? if(isin2)
??? ???????????????? ????? {
??? ???????????????? ?????? rowStr.append(restr+",");
??? ???????????????? ?????? isin2=false;
??? ???????????????? ????? }
??? ???????????????? ???? }else
??? ???????????????? ????
??? ???????????????? ???? {
??? ???????????????? ?????? //System.out.println(formartHSSF(cell,cellValue)+":"+cellValue.getNumberValue());
??????????????????????????????????????? rowStr.append(restr+",");
??? ???????????????? ???? }
??? ???????????????? ??? }?
??? ???????????????? ?? }
??? ????????????????? ?}
??? ????????????????? }
??????????????? ? }
??????????????? ? // 每个表为单位
????????????????? //System.out.println("第"+s+"表"+rowStr);
????????????????? if(rowStr!=null&&rowStr.toString().trim().length()!=0)
????????????????? {
???????????????? ? if(rowStr.toString().toLowerCase().indexOf("ltd")!=-1)
???????????????? ? {
???????????????? ??
???????????????? ? }else
???????????????? ? {
???????????????? ??
???????????????? ?? //System.out.println(rowStr);
???????????????? ?? datalist.add(rowStr);//将文件的所有记录保存到list
???????????????? ? }
????????????????? }
??????????????? }
??????????? } catch (IOException e) {
??????????????? e.printStackTrace();?
??????????? }?????
??}
??return datalist;
?}
?/**
? * 开始读取
? * @param di 文件目录
? * @param level 文件记录数
? * @return list
? * @throws Exception
? */
?public static boolean isExists(String filename,String[] exifile)
?{
??for(int i=0;i<exifile.length;i++)
??{
???if(filename.toLowerCase().indexOf(exifile[i])!=-1)
???{
????return true;
???}
??}
??return false;
?}
?public List readXls(File dir,int level,String[] exifile) throws Exception
?{
??
??File [] f = dir.listFiles();//获取目录下文件
??List DATxls=new ArrayList();
??for (int x = 0; x < f.length; x++)
???? {
?? System.out.println("扫描文件...."+dir.getPath()+"\"+f[x].getName());
?? try{
?????? if (f[x].isDirectory()){//如果是目录
????? ? readXls(f[x], level,exifile);
?????? }else if(isExists(f[x].getName(),exifile))//含税G类文件
?????? {
????? ?
?????? }
?????? else if(f[x].getName().toLowerCase().endsWith(".xlsx")){//2007 读取方式一
????? ? DATxls=new ArrayList();
????? ? DATxls=this.read2007XSSF(dir, f[x].getName());
????? ? System.out.println("方式一记录数"+DATxls.size());
????? ? for(int r=0;r<DATxls.size();r++)
????? ? {
????? ?? allData.add(DATxls.get(r).toString());
????? ? }
??? ????? }
?????? else if(f[x].getName().toLowerCase().endsWith(".xls")){//2007 读取方式二
????? ? DATxls=new ArrayList();
????? ? DATxls=this.read2007HSSF(dir, f[x].getName());
????? ? System.out.println("方式二记录数"+DATxls.size());
????? ? for(int r=0;r<DATxls.size();r++)
????? ? {
????? ?? allData.add(DATxls.get(r).toString());
????? ? }
??? ????? }
?? }catch(Exception ex)
?? {
???//如果出错,尝试jxl 读取方式
???// this.readXlsJXL(dir, f[x].getName());
?? }
?????
?????
????? level++;
???? }
??//System.out.println("共读取"+level+"个文件"+"共"+allData.size()+"记录");
??return allData;
?}
?//初始jxl 方式读取
?public static List readXlsJXL(File dir,String filename) throws Exception
?{
??System.out.println("读取方式三"+dir.getPath()+"\"+filename);
?? File file=new File(dir.getPath()+"\"+filename);
?? List excelValueList = new ArrayList();??
?? if (file.exists() && file.canRead() && (file.getName().toLowerCase().lastIndexOf(".xls") >= 1)) {
??? Workbook workbook = null;?
??? StringBuffer rowValue=null;
???????????? try {??
???????????????? workbook = Workbook.getWorkbook(file);??
???????????????? Sheet sheet=null;
???????????????? for(int s=0;s<workbook.getSheets().length;s++)
???????????????? {
???????????????? ?rowValue=new StringBuffer();
???????????????? ?sheet= workbook.getSheet(s);? //表
???????????????? ? int row = sheet.getRows();??
????????????????????? int col = sheet.getColumns();??
????????????????????? for (int r = 0; r < row; r++) {????
????????????????????????? for (int c = 0; c < col; c++) {??
???????????????????????? ? rowValue.append(sheet.getCell(c, r).getContents() != null ? sheet.getCell(c, r).getContents(): ""+",");??
????????????????????????? }??
???????????????????????????
????????????????????? }
?????????????????? excelValueList.add(rowValue);
???????????????? }
???????????? }catch(Exception ex)
???????????? {
???????????? ?ex.printStackTrace();
???????????? }
????????????
???
?? }
??return null;
?}
?//初始写入xls
?public boolean readIntoXls(String[] title,List datalist,String outPath)throws Exception
?{
??try{
????HSSFWorkbook wb = new HSSFWorkbook();
?????? HSSFSheet sheet = wb.createSheet("发料合并表");
?????? HSSFRow row = sheet.createRow(0);
?????? for(int i=0;i<title.length;i++)
?????? {
?????? ?row.createCell(i).setCellValue(title[i]);
?????? }
?????? for(int j=1;j<datalist.size()+1;j++)
?????? {
?????? ?//System.out.println(String.valueOf(datalist.get(j-1))+"*************");
?????? ?
?????? ?String[] data=String.valueOf(datalist.get(j-1)).split(",");
?????? ?row = sheet.createRow(j);
?????? ?//System.out.println(datalist.get(j-1));
??????? for(int i=0;i<title.length;i++)
??????? {
??????? ?for(int k=0;k<data.length;k++)
??????? ?{
??????? ??//System.out.println(data[k]);
???????? ?if(data[k].trim().equals(title[i].trim()))
???????? ?{?
???????? ??if(title[i].trim().equals("排版数量"))
???????? ??{
???????? ???if(data[k].equals("排版数量")&data[k+1].trim().equals("（1）"))
???????? ???{
???????? ????row.createCell(i).setCellValue(data[k+2]);
???????? ???}
???????? ??}else
???????? ??{
???????? ???if(data[k].trim().length()!=0)
???????? ???{
???????? ????row.createCell(i).setCellValue(data[k+1]);
???????? ???}
???????? ??}
???????? ??
???????? ?}else if(title[i].trim().equals("客户订单号"))
??????? ??{
??????? ???if(data[k].indexOf("客户订单号")!=-1)
??????? ???{
??????? ????row.createCell(i).setCellValue(data[k]);
??????? ???}
??????? ??}else{
???????? ??if(title[i].trim().equals("发料日期"))
???????? ??{
???????? ???if(data[data.length-1].indexOf("CA-MP")!=-1)
???????? ???{
???????? ????row.createCell(i).setCellValue(data[data.length-2]);
???????? ???}else
???????? ???{
???????? ????row.createCell(i).setCellValue(data[data.length-1]);
???????? ???}
???????? ???
???????? ??}
???????? ?}
??????? ?}
??????? }
?????? ?
?????? }
?????? FileOutputStream fileOut = new FileOutputStream(outPath);
?????? wb.write(fileOut);
?????? fileOut.close();
??}catch(Exception ex)
??{
???return false;
??}
??return true;
?}
}
