package com.excel.test.ff;import java.io.File;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import com.excel.test.vo.ExlInfo;import jxl.Cell;import jxl.Sheet;import jxl.Workbook;import jxl.format.Border;import jxl.format.BorderLineStyle;import jxl.write.Label;import jxl.write.WritableCellFormat;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;/* * jxl，excel读、写数据 */public class ExlToCompany {/** * 读取exl * @param excelFileName * @param tablename * @return */public List readExl(String excelFileName,String tablename){Workbook workBook = null;String file = excelFileName;try {workBook = workBook.getWorkbook(new File(file));} catch (Exception e) {e.printStackTrace();}Sheet sheet = workBook.getSheet(0);//int rows = sheet.getRows();//获取行数Cell cell = null;String companyCode = "";String companyName = "";String number = "";String companyType = "";String point = "";List list = new ArrayList();int i = 0;while(i < 37){ExlInfo exlInfo = new ExlInfo();cell = sheet.getCell(0, i);companyCode = cell.getContents();cell = sheet.getCell(1, i);companyName = cell.getContents();cell = sheet.getCell(2,i);number = cell.getContents();cell = sheet.getCell(3,i);companyType = cell.getContents();exlInfo.setCompanyCode(companyCode);exlInfo.setCompanyName(companyName);exlInfo.setNum(number);exlInfo.setCompanyType(companyType);list.add(exlInfo);i++;}ExlToCompany etc = new ExlToCompany();ExlInfo exlInfo = null;exlInfo = new ExlInfo();point = etc.obtainPoint(38,10,sheet,cell);exlInfo.setPoint(point);list.add(exlInfo);exlInfo = new ExlInfo();point = etc.obtainPoint(41,10,sheet,cell);exlInfo.setPoint(point);list.add(exlInfo);exlInfo = new ExlInfo();point = etc.obtainPoint(44,10,sheet,cell);exlInfo.setPoint(point);list.add(exlInfo);exlInfo = new ExlInfo();point = etc.obtainPoint(47,10,sheet,cell);exlInfo.setPoint(point);list.add(exlInfo);exlInfo = new ExlInfo();point = etc.obtainPoint(50,5,sheet,cell);exlInfo.setPoint(point);list.add(exlInfo);workBook.close();return list;}/** * 将数据写入excel * @param excelFileName文件名 * @param list数据 */public void companyToExl(String excelFileName, List list){File file = new File(excelFileName);Workbook workBook;try {WritableWorkbook wwb = Workbook.createWorkbook(file);//创建工作薄WritableSheet sheet = wwb.createSheet("Sheet1",0);//创建工作表 //workBook = Workbook.getWorkbook(file);//WritableSheet sheet = wwb.getSheet(0);WritableCellFormat wcf = new WritableCellFormat();wcf.setBorder(Border.ALL, BorderLineStyle.THIN);WritableCellFormat wcf2 = new WritableCellFormat();wcf2.setBorder(Border.ALL, BorderLineStyle.THIN);wcf2.setAlignment(jxl.format.Alignment.CENTRE);Label labelC = null;int rownum=0;//开始行String companyCode = "";String companyName = "";String num = "";String companyType = "";String point = "";labelC = new Label(0, rownum,"公司编号", wcf2);sheet.addCell(labelC);labelC = new Label(1, rownum,"公司名", wcf2);sheet.addCell(labelC);labelC = new Label(2, rownum,"编号", wcf2);sheet.addCell(labelC);labelC = new Label(3, rownum,"类型", wcf2);sheet.addCell(labelC);//合并单元格sheet.mergeCells(3, rownum, 7, rownum); //合并单元格，参数格式（开始列，开始行，结束列，结束行） rownum++;for(int i = 0; i<list.size(); i++){ExlInfo exlInfo = new ExlInfo();exlInfo = (ExlInfo) list.get(i);companyCode = exlInfo.getCompanyCode();companyName = exlInfo.getCompanyName();num = exlInfo.getNum();companyType = exlInfo.getCompanyType();point = exlInfo.getPoint();if(point == null){labelC = new Label(0, rownum,companyCode, wcf);sheet.addCell(labelC);labelC = new Label(1, rownum,companyName, wcf);sheet.addCell(labelC);labelC = new Label(2, rownum,num, wcf);sheet.addCell(labelC);labelC = new Label(3, rownum,companyType, wcf);sheet.addCell(labelC);//合并单元格sheet.mergeCells(3, rownum, 7, rownum); //合并单元格，参数格式（开始列，开始行，结束列，结束行） rownum++;}else{labelC = new Label(0, rownum,"得分", wcf);sheet.addCell(labelC);labelC = new Label(1, rownum,point, wcf);sheet.addCell(labelC);rownum++;}}wwb.write();wwb.close();} catch (Exception e) {e.printStackTrace();}}public static void main(String[] args) {File file=new File("D:\\test\\");String test[];String readExcelFileName = "";//读取exlString writeExcelFileName = "";//写入exltest=file.list();//循环获取test文件夹下文件for(int i=0;i<test.length;i++){readExcelFileName = "D:\\test\\"+test[i];//判断是否为xls格式if(readExcelFileName.endsWith(".xls")){String tablename = "";List<ExlToCompany> list = new ArrayList<ExlToCompany>();ExlToCompany exlToCom = new ExlToCompany();//读取exllist = exlToCom.readExl(readExcelFileName, tablename);//System.out.println(test[i]);//获取的文件名，如：XXX公司-2013-08-08File tempFile =new File( readExcelFileName.trim());String fileName = tempFile.getName();//获取当前时间放入到文件名//String[] str = fileName.split("-");//获取当前时间//SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//String time = sdf.format(new Date());//fileName = str[0]+time;//查询结果文件夹中是否存在此文件boolean exit = exlToCom.findExl(fileName);if(exit){//写入exlwriteExcelFileName = "D:\\result\\"+fileName;exlToCom.companyToExl(writeExcelFileName, list);System.out.println(fileName+" 解析完成！");}else{continue;}}}}/** * 获取得分 * @param row行数 * @param score 最大分数 * @param sheet * @param cell * @return String */public String obtainPoint(int row, int score, Sheet sheet,Cell cell){String point = "";for(int j = 0; j <= score;j++){ExlInfo exlInfo = new ExlInfo();cell = sheet.getCell(j,row);//指定行列point = cell.getContents();if("".equals(point)){continue;}else{int pointInt = j+1;point = ""+pointInt;break;}}return point;}/** * 查找指定文件夹下文件是否存在 * @param fileName 文件名 * @return boolean */public boolean findExl(String fileName){boolean exit = true;//查询文件夹路径File file=new File("D:\\result\\");//获取文件夹下所有文件名称放入数组String test[];test=file.list();//若文件夹不存在创建if(test == null){file.mkdir();}//循环数组并比对文件名String fName = "";for(int i=0;i<test.length;i++){fName = test[i];if(fileName.equals(fName)){//System.out.println("已存在，之前已经解析！");exit = false;}}return exit;}}
