操作CSV与EXCEL的工具

一 、操作CSV

1.maven依赖

<dependency>
    <groupId>net.sf.opencsv</groupId>
    <artifactId>opencsv</artifactId>
    <version>2.1</version>
 </dependency>

2.读写测试代码

String csvPath="E:/work/result.csv";
String marsCsvPath="E:/work/location.csv";
CSVReader reader=new CSVReader(new InputStreamReader(new FileInputStream(csvPath),"UTF-8"),’,’);
CSVWriter writer=new CSVWriter(new OutputStreamWriter(new FileOutputStream(marsCsvPath),"GBK"),’,’);       

int n = 0;

Calendar begin = Calendar.getInstance();       
String arrays[]=reader.readNext();
writer.writeNext(arrays);
arrays=reader.readNext();

while(arrays!=null){               
	String pointStr=arrays[4];
	String point[]=pointStr.split(";");
	StringBuffer sb=new StringBuffer();
	for(int i=0;i<point.length;i++){
		String ll=point[i];
		String llp[]=ll.split(",");
		double gg_lon=Long.valueOf(llp[1].trim())/1000000.0;
		double gg_lat=Long.valueOf(llp[0].trim())/1000000.0 ;
	   
		String gps = GpsHelper.bd_decrypt(gg_lat, gg_lon);
		int dindex=gps.indexOf(",");
		String longitude=gps.substring(0,dindex);
		String latitude=gps.substring(dindex+1);
	 
		sb.append(longitude.substring(0,longitude.indexOf(".")+7)+","+latitude.substring(0,latitude.indexOf(".")+7));
		if(i!=point.length-1){
			sb.append(";");
		}
	}
	arrays[4]=sb.toString();
	writer.writeNext(arrays);
	arrays=reader.readNext();
	n++;
}
writer.close();
reader.close();       
log.info(" end" + n);

二、excel操作

1.pom依赖

 <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
 </dependency>

2.读测试代码

SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss:SS");
        TimeZone t = sdf.getTimeZone();
        t.setRawOffset(0);
        sdf.setTimeZone(t);
        Long startTime = System.currentTimeMillis();
        String fileName = "C:\\Users\\dell\\Desktop\\天芮净妍美肤.xlsx";
        // 检测代码
        try {
            // 构造 XSSFWorkbook 对象，strPath 传入文件路径
            OPCPackage pkg = OPCPackage.open(fileName);
              XSSFWorkbook xwb = new XSSFWorkbook(pkg);
            // 读取第一章表格内容
            XSSFSheet sheet = xwb.getSheetAt(0);
            // 定义 row、cell
            XSSFRow row;
            String cell;
            // 循环输出表格中的内容
            for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
                    // 通过 row.getCell(j).toString() 获取单元格内容，
                    cell = row.getCell(j).toString();
                    System.out.print(cell + "\t");
                }
                System.out.println("");
            }
            pkg.close();
        } catch (Exception ex) {
            Logger.getLogger(ReadExcel.class.getName()).log(Level.SEVERE, null, ex);
        }
        Long endTime = System.currentTimeMillis();

        System.out.println("用时：" + sdf.format(new Date(endTime - startTime)));

3.写excel

private static void writeExcel(String fileName,int rowNum,List cells) {
        // 检测代码
        try {
            Workbook wb = null;
            File file=new File(fileName);
            if(!file.exists()){
                // 构造 XSSFWorkbook 对象，strPath 传入文件路径            
                if (fileName.endsWith("xls")) { // Excel 2003
                    wb = new HSSFWorkbook();
                } else if (fileName.endsWith("xlsx")) { // Excel 2007/2010
                    wb = new XSSFWorkbook();
                }
            }else{
                FileInputStream fis=new FileInputStream(file);
                if (fileName.endsWith("xls")) { // Excel 2003
                    wb = new HSSFWorkbook(fis);
                } else if (fileName.endsWith("xlsx")) { // Excel 2007/2010
                    wb = new XSSFWorkbook(fis);
                }
            }
            
            // 读取第一章表格内容
            Sheet sheet = null;
            if(wb.getNumberOfSheets()==0){
                sheet=wb.createSheet();
            }else{
                sheet=wb.getSheetAt(0);
            }
            Row row = sheet.createRow(rowNum);
            for(int i=0;i<cells.size();i++){
                Cell cell = row.createCell(i);
                cell.setCellValue(cells.get(i));
            }
            // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效  
            FileOutputStream out =  new FileOutputStream(fileName);  
            wb.write(out);  
            
            out.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

    }