Excel 数据的清洗 Java

package demo;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import java.io.FileOutputStream;
import java.util.List;

import demo.Utils;
import org.apache.poi.ss.usermodel.Row;

import javax.servlet.http.HttpServlet;

public class ExcelDataCleaning {
public static void main(String[] args) throws IOException {
SimpleDateFormat df = new SimpleDateFormat(“yyMMddHHmmss”);//设置日期格式
String d = df.format(new Date());
String strCon = “”;
String OriginSourcePath = System.getProperty(“OrignSourcePath”);
String TargetSourcePath = System.getProperty(“TargetSourcePath”);
String OriginFileName = System.getProperty(“OriginFileName”);
String TargetFileName = “\NPendingAlertReport” + d + “.xls”;
String op = OriginSourcePath + OriginFileName;
String tp = TargetSourcePath + TargetFileName;
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();

// Workbook workbook = new XSSFWorkbook();
// 设置字体

    //新建sheet Testing File 1.xlsx

// String filepath=“C:\wz/Testing File 1.xlsx”;
String filepath=“C:\wz//PendingAlertReport21-0630.xls”;
HSSFSheet sheet = workbook.createSheet(“Export WorkSheet”);
List lists= Utils.readAllByExcel(op);
// System.out.println(lists);
// for (Object li:lists){
// System.out.println(li);
// }
// List list1 = new ArrayList();
//累加计算
for (int i = 0; i < lists.size(); i++) {
List li= (List) lists.get(i);
int jcount = 0;
for (int j = 2; j < li.size()-1; j++){
int cell = Integer.parseInt((String) li.get(j));
jcount = cell + jcount;
li.set(li.size()-1, jcount);
// System.out.print(cell);
// System.out.print(li.get(j)+" ");
}
// list1.add(li);
// System.out.println(jcount);
// System.out.println(li.get(li.size()-1));
}
// System.out.println(lists);
// System.out.print(list1);
// for (Object li:list1) {
// for (int i = 0; i < 8; i++) {
// System.out.println();
// }
// }
// int rows=lists.;
// System.out.println(“rows:”+rows);
List li1 = Utils.readHeaderByExcel(op);
// System.out.print(li1);
//excel 添加头部
HSSFRow row = sheet.createRow(0);
for(int i=0;i < li1.size();i++){
row.createCell(i).setCellValue((String) li1.get(i));
}
//excel 填充记录
for(int i =1; i < lists.size();i++){
List li= (List) lists.get(i-1);
strCon = (String) li.get(1);
HSSFRow row1 = sheet.createRow(i);
for(int j =0; j < li.size();j++) {
if(j>1){
row1.createCell(j).setCellValue(new Integer(String.valueOf(li.get(j))));
}else {
row1.createCell(j).setCellValue(String.valueOf(li.get(j)));
}
// row1.createCell(j).setCellValue(li.get(j));
}
}
//删除包含IMT的business_unit
int rowsnum = sheet.getPhysicalNumberOfRows();
for (int i = 1; i < rowsnum; i++) {
Row rs = sheet.getRow(i);
// System.out.println(rs.getCell(1));
strCon = String.valueOf(rs.getCell(1));
// System.out.println(strCon);
boolean a = Utils.isExist(String.valueOf(strCon));
if (true == a) {
Utils.removeRow(sheet,i);
}
}
// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(tp);
// 把相应的Excel 工作簿存盘
workbook.write(fOut);
fOut.flush();
// 操作结束，关闭文件
fOut.close();
System.out.println(“文件生成…”);
}
public static String outputFile = “C:\wz//test7.xls”;
}