上篇文章學習了Java讀取Excel檔案，這篇文章接着來簡單總結Java寫入Excel，話不多說，看代碼：
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
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.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.lmb.excel.bean.Employee;
/**
* 寫入Excel檔案的方法（寫表頭，寫資料）
* @author lmb
* @date 2017-3-16
*
*/
public class WriteExcel {
/**
* 将List集合資料寫入excel（單個sheet）
* @param filePath 檔案路徑
* @param excelTitle 檔案表頭
* @param employeeList 要寫入的資料集合
* @param sheetName sheet名稱
*/
public static void writeEmployeeListToExcel(String filePath,String[] excelTitle,List<Employee> employeeList,String sheetName){
System.out.println("開始寫入檔案>>>>>>>>>>>>");
Workbook workbook = null;
if (filePath.toLowerCase().endsWith("xls")) {//2003
workbook = new XSSFWorkbook();
}else if(filePath.toLowerCase().endsWith("xlsx")){//2007
workbook = new HSSFWorkbook();
}else{
// logger.debug("invalid file name,should be xls or xlsx");
}
//create sheet
Sheet sheet = workbook.createSheet(sheetName);
int rowIndex = 0;//辨別位，用于辨別sheet的行号
//周遊資料集，将其寫入excel中
try{
//寫表頭資料
Row titleRow = sheet.createRow(rowIndex);
for (int i = 0; i < excelTitle.length; i++) {
//建立表頭單元格,填值
titleRow.createCell(i).setCellValue(excelTitle[i]);
}
System.out.println("表頭寫入完成>>>>>>>>");
rowIndex++;
//循環寫入主表資料
for (Iterator<Employee> employeeIter = employeeList.iterator();employeeIter.hasNext();) {
Employee employee = employeeIter.next();
//create sheet row
Row row = sheet.createRow(rowIndex);
//create sheet coluum(單元格)
Cell cell0 = row.createCell(0);
cell0.setCellValue(employee.getName());
Cell cell1 = row.createCell(1);
cell1.setCellValue(employee.getGender());
Cell cell2 = row.createCell(2);
cell2.setCellValue(employee.getAge());
Cell cell3 = row.createCell(3);
cell3.setCellValue(employee.getDepartment());
Cell cell4 = row.createCell(4);
cell4.setCellValue(employee.getSalary());
Cell cell5 = row.createCell(5);
cell5.setCellValue(employee.getDate());
rowIndex++;
}
System.out.println("主表資料寫入完成>>>>>>>>");
FileOutputStream fos = new FileOutputStream(filePath);
workbook.write(fos);
fos.close();
System.out.println(filePath + "寫入檔案成功>>>>>>>>>>>");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 讀取Excel2003的主表資料 （單個sheet）
* @param filePath
* @return
*/
private static List<Employee> readFromXLS2003(String filePath) {
File excelFile = null;// Excel檔案對象
InputStream is = null;// 輸入流對象
String cellStr = null;// 單元格，最終按字元串處理
List<Employee> employeeList = new ArrayList<Employee>();// 傳回封裝資料的List
Employee employee = null;// 每一個雇員資訊對象
try {
excelFile = new File(filePath);
is = new FileInputStream(excelFile);// 擷取檔案輸入流
HSSFWorkbook workbook2003 = new HSSFWorkbook(is);// 建立Excel2003檔案對象
HSSFSheet sheet = workbook2003.getSheetAt(0);// 取出第一個工作表，索引是0
// 開始循環周遊行，表頭不處理，從1開始
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
HSSFRow row = sheet.getRow(i);// 擷取行對象
employee = new Employee();// 執行個體化Student對象
if (row == null) {// 如果為空，不處理
continue;
}
// 循環周遊單元格
for (int j = 0; j < row.getLastCellNum(); j++) {
HSSFCell cell = row.getCell(j);// 擷取單元格對象
if (cell == null) {// 單元格為空設定cellStr為空串
cellStr = "";
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {// 對布爾值的處理
cellStr = String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {// 對數字值的處理
cellStr = cell.getNumericCellValue() + "";
} else {// 其餘按照字元串處理
cellStr = cell.getStringCellValue();
}
// 下面按照資料出現位置封裝到bean中
if (j == 0) {
employee.setName(cellStr);
} else if (j == 1) {
employee.setGender(cellStr);
} else if (j == 2) {
employee.setAge(new Double(cellStr).intValue());
} else if (j == 3) {
employee.setDepartment(cellStr);
} else if(j == 4){
employee.setSalary(new Double(cellStr).intValue());
}else {
employee.setDate(cellStr);
}
}
employeeList.add(employee);// 資料裝入List
}
} catch (IOException e) {
e.printStackTrace();
} finally {// 關閉檔案流
if (is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return employeeList;
}
/**
* 讀取Excel2003的表頭
* @param filePath 需要讀取的檔案路徑
* @return
*/
public static String[] readHeaderFromXLS2003(String filePath){
String[] excelTitle = null;
FileInputStream is = null;
try{
File excelFile = new File(filePath);
is = new FileInputStream(excelFile);
HSSFWorkbook workbook2003 = new HSSFWorkbook(is);
//循環讀取工作表
for (int i = 0; i < workbook2003.getNumberOfSheets(); i++) {
HSSFSheet hssfSheet = workbook2003.getSheetAt(i);
//*************擷取表頭是start*************
HSSFRow sheetRow = hssfSheet.getRow(i);
excelTitle = new String[sheetRow.getLastCellNum()];
for (int k = 0; k < sheetRow.getLastCellNum(); k++) {
HSSFCell hssfCell = sheetRow.getCell(k);
excelTitle[k] = hssfCell.getStringCellValue();
// System.out.println(excelTitle[k] + " ");
}
//*************擷取表頭end*************
}
}catch (IOException e) {
e.printStackTrace();
} finally {// 關閉檔案流
if (is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return excelTitle;
}
//主函數
public static void main(String[] args) {
List<Employee> employeeList = readFromXLS2003("D:\\employee.xls");
String[] excelTitle = readHeaderFromXLS2003("D:\\employee.xls");
writeEmployeeListToExcel("D:\\writeemployee.xls",excelTitle,employeeList,"employee");
}
}
程式運作效果：
