今天分享的是我的工作上的作業,要把Excel某些欄位的參數做處理,利用POJO與Collection把符合條件的資料存在一個POJO後放入一個List,readFromExcel即產生List<Alarm>(也是昨日XML POJO的來源),再呼叫exportExcel傳入List<Alarm>,即可以產生Excel檔。
package read;
import java.io.FileOutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.util.ArrayList;
import java.util.Iterator;
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;
import org.apache.poi.ss.usermodel.Row;
import alarm.Alarm;
public class GetDataFromFile {
List<Alarm> aplist=new ArrayList<>();
Alarm alp;
Iterator<Alarm> it;
public List<Alarm> readFromExcel(){
Path src=Paths.get("L:\\ALARM_CROSS_REFERENCE_ICC_20120912_V1.xls");
try{
HSSFWorkbook workbook = new HSSFWorkbook(Files.newInputStream(src, StandardOpenOption.READ)); //讀入xls檔
HSSFSheet sheet= workbook.getSheet("ALARM_CROSS_REFERENCE");//讀取要操作的資料表
int rows = sheet.getPhysicalNumberOfRows(); //取得所有的Row
for(int r=1; r<rows; r++){ //掃描所有的Row取得資料
HSSFRow row=sheet.getRow(r);
HSSFCell sys=row.getCell(0, Row.CREATE_NULL_AS_BLANK);
HSSFCell refid=row.getCell(1, Row.CREATE_NULL_AS_BLANK);
HSSFCell alarmid=row.getCell(2, Row.CREATE_NULL_AS_BLANK);
HSSFCell des=row.getCell(3, Row.CREATE_NULL_AS_BLANK);
HSSFCell alstate=row.getCell(4,Row.CREATE_NULL_AS_BLANK);
HSSFCell P1=row.getCell(17, Row.CREATE_NULL_AS_BLANK);
HSSFCell P2=row.getCell(18, Row.CREATE_NULL_AS_BLANK);
HSSFCell P3=row.getCell(19, Row.CREATE_NULL_AS_BLANK);
HSSFCell P4=row.getCell(20, Row.CREATE_NULL_AS_BLANK);
HSSFCell P5=row.getCell(21, Row.CREATE_NULL_AS_BLANK);
if(alstate.getStringCellValue().contains("HIGH/LOW")){
if(!refid.getStringCellValue().isEmpty()){
if(!P1.getStringCellValue().isEmpty()){
alp= new Alarm();
alp.setAlarmid(alarmid.getStringCellValue()+"."+P1.getStringCellValue());
alp.setSys(sys.getStringCellValue());
alp.setDes(des.getStringCellValue());
alp.setRefid(refid.getStringCellValue());
alp.setState("");
aplist.add(alp);
}
if(!P2.getStringCellValue().isEmpty()){
alp= new Alarm();
alp.setAlarmid(alarmid.getStringCellValue()+"."+P2.getStringCellValue());
alp.setSys(sys.getStringCellValue());
alp.setDes(des.getStringCellValue());
alp.setRefid(refid.getStringCellValue());
alp.setState("HI");
aplist.add(alp);
}
if(!P3.getStringCellValue().isEmpty()){
alp= new Alarm();
alp.setAlarmid(alarmid.getStringCellValue()+"."+P3.getStringCellValue());
alp.setSys(sys.getStringCellValue());
alp.setDes(des.getStringCellValue());
alp.setRefid(refid.getStringCellValue());
alp.setState("HI HI");
aplist.add(alp);
}
if(!P4.getStringCellValue().isEmpty()){
alp= new Alarm();
alp.setAlarmid(alarmid.getStringCellValue()+"."+P4.getStringCellValue());
alp.setSys(sys.getStringCellValue());
alp.setDes(des.getStringCellValue());
alp.setRefid(refid.getStringCellValue());
alp.setState("LOW");
aplist.add(alp);
}
if(!P5.getStringCellValue().isEmpty()){
alp= new Alarm();
alp.setAlarmid(alarmid.getStringCellValue()+"."+P5.getStringCellValue());
alp.setSys(sys.getStringCellValue());
alp.setDes(des.getStringCellValue());
alp.setRefid(refid.getStringCellValue());
alp.setState("LOW LOW");
aplist.add(alp);
}
}
}
}
}catch(Exception e){
e.printStackTrace();
}
return aplist;
// it=aplist.iterator();
// while(it.hasNext()){
// ap t=it.next();
// System.out.println(t.getRefid()+", "+t.getAlarmid()+", "+t.getDes()+","+t.getState()+", "+t.getSys());
//}
}
public void exportExcel(List<Alarm> alist){
int colCount= 5;
HSSFWorkbook workbook= new HSSFWorkbook();
HSSFSheet sheet= workbook.createSheet("AlarmID");
int rowNum=0;
HSSFRow header= sheet.createRow(rowNum);
HSSFCell refid=header.createCell(0);
HSSFCell aid=header.createCell(1);
HSSFCell des=header.createCell(2);
HSSFCell state=header.createCell(3);
HSSFCell sys=header.createCell(4);
refid.setCellValue("REFERENCE ID");
aid.setCellValue("ALARM ID");
des.setCellValue("DESCRIPTION");
state.setCellValue("ALARMING STATE");
sys.setCellValue("SYSTEM");
Iterator<Alarm> it=alist.iterator();
while(it.hasNext()){
Alarm t=it.next();
String [] c={t.getRefid(), t.getAlarmid(),t.getDes(),t.getState(),t.getSys()};
rowNum++;
HSSFRow row= sheet.createRow(rowNum);
for (int i=0;i<colCount;i++){
HSSFCell cell=row.createCell(i);
cell.setCellValue(c[i]);
}
}
try{
FileOutputStream out = new FileOutputStream("L:\\output.xls");
workbook.write(out);
out.flush();
out.close();
System.out.println("Success");
}catch(Exception e){
e.printStackTrace();
}
}
}