【Apache POI】Java写入Excel文件

妖狐艹你老母 2022-09-30 11:50 200阅读 0赞

上篇文章学习了[Java读取Excel文件][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");
    	}
    }

程序运行效果：

![Center][]  
![Center 1][]

![Center 2][]

[Java_Excel]: http://blog.csdn.net/lmb55/article/details/64537293
[Center]: /images/20220706/72e25872dad14062865cc34a3c0eeb8f.png
[Center 1]: /images/20220706/5a2b3457094c401589fa246b7170499f.png
[Center 2]: /images/20220706/18038292430442f593c596fab8b594db.png