使用POI操作excel向模板填信息

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
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 org.springframework.core.io.InputStreamSource;


import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;


public class POIUtils {


public static void main(String[] args) {
String path="d:\\123.xlsx";
Map<String,String> map=new HashMap<String,String>();
map.put("username","lisi");
map.put("pwd",null);
map.put("type","ssme");


try {
OutputStream outputStream=new FileOutputStream(new File("d:\\456.xlsx"));
getFileStream(map,path,outputStream);
} catch (IOException e) {
e.printStackTrace();
}
}


public static void getFileStream(Map<String,String> map,String path,OutputStream outputStream) throws IOException {
File excelFile = new File(path);
getFileStream(map,excelFile,outputStream);
}


public static void getFileStream(Map<String,String> map,File file,OutputStream outputStream) throws IOException {
Workbook workbook=getWorkbook(file);
getFileStream(map,workbook,outputStream);
}


public static void getFileStream(Map<String,String> map,Workbook workbook,OutputStream outputStream) throws IOException {
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
Sheet sheet = workbook.getSheetAt(i);
for (Row row : sheet) {
for (Cell cell : row) {
String key=getCellValueAsString(cell);
if(map.containsKey(key)){
cell.setCellValue(map.get(key));
}
}
}
}
workbook.write(outputStream);
}



public static Workbook getWorkbook(File excelFile) throws IOException {
InputStream ins1 = new FileInputStream(excelFile);
InputStream ins2 = new FileInputStream(excelFile);
Workbook workbook = null;
try {
// 默认是xlsx格式，如果创建异常，则使用xlsx格式
workbook = new XSSFWorkbook(ins1);
} catch (Exception e) {
// 如果是xls格式
workbook = new HSSFWorkbook(ins2);
} finally {
if (ins1 != null) {
ins1.close();
}
if (ins2 != null) {
ins2.close();
}
}
return workbook;
}

public static Workbook getWorkbook(InputStreamSource inputStreamSource) throws IOException{
InputStream ins1 = inputStreamSource.getInputStream();
InputStream ins2 =  inputStreamSource.getInputStream();
Workbook workbook = null;
try {
//默认是xlsx格式，如果创建异常，则使用xls格式
workbook = new XSSFWorkbook(ins1);
} catch (Exception e) {
//如果是xls格式
workbook = new HSSFWorkbook(ins2);
} finally {
if(ins1 != null){
ins1.close();
}
if(ins2 != null){
ins2.close();
}
}
return workbook;
}



/**
* 不论是什么类型都当作String拿出来
* @param cell
* @return
*/
public static String getCellValueAsString(Cell cell){
String cellValue;
if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
cellValue = "";
}else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
cellValue = String.valueOf(cell.getBooleanCellValue());
}else if(cell.getCellType() == Cell.CELL_TYPE_ERROR){
cellValue = String.valueOf(cell.getErrorCellValue());
}else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
cellValue = cell.getCellFormula();
}else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
cellValue = String.valueOf(cell.getNumericCellValue());
}else if(cell.getCellType() == Cell.CELL_TYPE_STRING){
cellValue = cell.getStringCellValue();
}else{
cellValue = null;
}
return cellValue;
}
}