JAVA 利用poi EXCLE模板文档导出数据

JAVA 利用poi EXCLE模板文档导出数据

1. 导入jar包

下载地址：https://pan.baidu.com/s/1w-VJ8AK4WKpKDXD16r5iuQ
提取码：wllf

2.EXCLE模板

3.代码示例

package utill;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellAddress;

/**
 * 数据导入到EXCLE模板中
 * @author PC
 *
 */
public class Excle {
	
	private static String inputUrl="G:/file/docx/测试.xlsx";
	private static String outputUrl="G:/file/docx/2.xlsx";
	
	
	/**
	 * 测试
	 * @param args
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 * @throws EncryptedDocumentException 
	 */
	public static void main(String[] args) throws InvalidFormatException, EncryptedDocumentException, IOException {
		//writeSpecifiedCell(inputUrl, outputUrl, "D6", "测试");
		/*writeSpecifiedCell(inputUrl, outputUrl, "B28", "2019.10.23");
		writeSpecifiedCell(inputUrl, outputUrl, "B7", "章三");
		writeSpecifiedCell(inputUrl, outputUrl, "F13", "8℃");*/
		Map<String, String> paramsMap = new HashMap<String, String>();
		//paramsMap.put("customerName","123213");
		paramsMap.put("B28","2019.10.23");
		paramsMap.put("D5","1555625658");
		paramsMap.put("D6","车轮");
		paramsMap.put("D7","12*56");
		paramsMap.put("F13","8℃");
		ExportExcle(inputUrl, outputUrl, paramsMap);
	}
	/**
	 * 
	 * @param path 读取模板文件在路径
	 * @param outpath  写入数据路径
	 * @param paramMap  位置数值
	 * @throws IOException 
	 * @throws InvalidFormatException 
	 * @throws EncryptedDocumentException 
	 */
	 public static void ExportExcle(String path,String outpath,Map<String, String> paramMap) throws IOException, EncryptedDocumentException, InvalidFormatException{
		//根据路径获取文件
	 	  File file = new File(path);
	 	  //定义输入流对象
	 	  FileInputStream excelFileInputStream;
	 	 excelFileInputStream = new FileInputStream(file);
	 	   // 拿到文件转化为JavaPoi可操纵类型
	 	   Workbook workbook = WorkbookFactory.create(excelFileInputStream);
	 	   excelFileInputStream.close();
	 	   获取excel表格
	 	   Sheet sheet = workbook.getSheetAt(0);
	 	   for(Entry<String, String> entry:paramMap.entrySet()){
	 		//获取单元格的row和cell
		 	   CellAddress address = new CellAddress(entry.getKey());
		 	   // 获取行
		 	   Row row = sheet.getRow(address.getRow());
		 	   // 获取列
		 	   Cell cell = row.getCell(address.getColumn());
		 	   //设置单元的值
		 	   cell.setCellValue(entry.getValue());  
	 	   }
	 	  //写入数据
	 	   FileOutputStream excelFileOutPutStream = new FileOutputStream(outpath);
	 	   workbook.write(excelFileOutPutStream);
	 	   excelFileOutPutStream.flush();
	 	   excelFileOutPutStream.close();
	 	   System.out.println("指定单元格设置数据写入完成");
	 	   
	 }
	/** 给excel指定位置写入值（单个）
	  * 
	  * @param path       写入文件在路径
	  * @param coordinate 写入内容的位置（例如:B4）
	  * @param value      写的值
	  */
	 public static void writeSpecifiedCell(String path,String outpath, String coordinate, String value) throws InvalidFormatException {
	 	  //根据路径获取文件
	 	  File file = new File(path);
	 	  //定义输入流对象
	 	  FileInputStream excelFileInputStream;
	 	  try {
	 	   excelFileInputStream = new FileInputStream(file);
	 	   // 拿到文件转化为JavaPoi可操纵类型
	 	   Workbook workbook = WorkbookFactory.create(excelFileInputStream);
	 	   excelFileInputStream.close();
	 	   获取excel表格
	 	   Sheet sheet = workbook.getSheetAt(0);
	 	   //获取单元格的row和cell
	 	   CellAddress address = new CellAddress(coordinate);
	 	   // 获取行
	 	   Row row = sheet.getRow(address.getRow());
	 	   // 获取列
	 	   Cell cell = row.getCell(address.getColumn());
	 	   //设置单元的值
	 	   cell.setCellValue(value);
	 	   //写入数据
	 	   FileOutputStream excelFileOutPutStream = new FileOutputStream(outpath);
	 	   workbook.write(excelFileOutPutStream);
	 	   excelFileOutPutStream.flush();
	 	   excelFileOutPutStream.close();
	 	   System.out.println("指定单元格设置数据写入完成");
	 	  } catch (FileNotFoundException e) {
	 	   e.printStackTrace();
	 	  } catch (EncryptedDocumentException e) {
	 	   e.printStackTrace();
	 	  } catch (IOException e) {
	 	   e.printStackTrace();
	 	  }
	 	 }

}

4.结果展示