Java使用poi操作excel——批量给单元格设值

这里提供批量替换和单个替换两个方法

import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.util.Map; import java.util.HashMap; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelUtils{ public static final String EXCEL_XLS = "xls"; public static final String EXCEL_XLSX = "xlsx"; public static void main(String[] args){ //组装测试数据 Map<String, String> params = new HashMap<>(30); params.put("A1","1277"); /*给单元格设值*/ Workbook wb = null; //读取excel文件 try { File file = new File("文件路径"); FileInputStream fileInputStream = new FileInputStream(file); if(file.getName().endsWith(EXCEL_XLSX)) { wb = new XSSFWorkbook(fileInputStream); System.out.println("========文件加载已完成2016========"); }else { System.out.println("Not a Excel"); } } catch (Exception e) { e.printStackTrace(); System.out.println("IO Erro" + e.getMessage()); } //写入数据 writeInTemplate(wb,params); //导出excel文件 try{ File file = new File("文件导出路径"); FileOutputStream fileOutputStream = new FileOutputStream(file); wb.write(fileOutputStream); fileOutputStream.close(); System.out.println("========文件导出已完成========"); }catch(Exception e){ e.printStackTrace(); System.out.println("IO Erro" + e.getMessage()); } }

/**

Excel值批量替换@param wb;@param params; */ public static void writeInTemplate(Workbook wb,Map<String, String> params) { for (Map.Entry<String, String> entry : params.entrySet()) {//map映射<坐标>:<数据> Sheet sheet1 = wb.getSheet("Quotation"); CellAddress address = new CellAddress(entry.getKey()); XSSFRow row = (XSSFRow) sheet1.getRow(address.getRow());//得到行 XSSFCell cell = row.getCell(address.getColumn());//得到列 cell.setCellValue(entry.getValue());//改变数据 } System.out.println("========文件替换已完成========"); }

/**

替换单个单元格值@param value@param params */ public static void replaceValue(Sheet sheet2,String index,String value) { CellAddress address = new CellAddress(index); XSSFRow row = (XSSFRow) sheet2.getRow(address.getRow());//得到行 XSSFCell cell = row.getCell(address.getColumn()); cell.setCellValue(value);//改变数据 System.out.println("改变成功 "+index); } }