今天分享的是我的工作上的作業，要把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();
				}
			}
}