1POI jar简介

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

2POI jar下载

poi下载地址：https://www.apache.org/dyn/closer.lua/poi/release/bin/poi-bin-3.17-20170915.tar.gz

下载之后把压缩包解压，然后在项目里面引用解压文件夹里面的jar

3新建ReadExcelTest java project，引入poi相关jar

4创建一个WriteExcel java类，编写代码，写入excel里面

package test;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

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;

public class WriteExcel {

private static final String EXCEL_XLS = "xls";

private static final String EXCEL_XLSX = "xlsx";

@SuppressWarnings("rawtypes")

public static void writeExcel(List<Map<String,String>> dataList, int cloumnCount,

String finalXlsxPath) {

OutputStream out = null;

try {

// 获取总列数

int columnNumCount = cloumnCount;

// 读取Excel文档

File finalXlsxFile = new File(finalXlsxPath);

Workbook workBook = getWorkbok(finalXlsxFile);

// sheet 对应一个工作页

Sheet sheet = workBook.getSheetAt(0);

/**

* 删除原有数据，除了属性列

*/

int rowNumber = sheet.getLastRowNum(); // 第一行从0开始算

System.out.println("原始数据总行数，除属性列：" + rowNumber);

for (int i = 1; i <= rowNumber; i++) {

Row row = sheet.getRow(i);

sheet.removeRow(row);

}

// 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效

out = new FileOutputStream(finalXlsxPath);

workBook.write(out);

/**

* 往Excel中写新数据

*/

for (int j = 0; j < dataList.size(); j++) {

// 创建一行：从第二行开始，跳过属性列

Row row = sheet.createRow(j + 1);

// 得到要插入的每一条记录

Map dataMap = dataList.get(j);

String name = dataMap.get("title_id").toString();

String address = dataMap.get("title_name").toString();

String phone = dataMap.get("title_name_en").toString();

for (int k = 0; k <= columnNumCount; k++) {

// 在一行内循环

Cell first = row.createCell(0);

first.setCellValue(name);

Cell second = row.createCell(1);

second.setCellValue(address);

Cell third = row.createCell(2);

third.setCellValue(phone);

}

}

// 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效

out = new FileOutputStream(finalXlsxPath);

workBook.write(out);

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

if (out != null) {

out.flush();

out.close();

}

} catch (IOException e) {

e.printStackTrace();

}

}

System.out.println("数据汇入成功");

}

/**

* 判断Excel的版本,获取Workbook

*

* @param in

* @param filename

* @return

* @throws IOException

*/

public static Workbook getWorkbok(File file) throws IOException {

Workbook wb = null;

FileInputStream in = new FileInputStream(file);

if (file.getName().endsWith(EXCEL_XLS)) { // Excel 2003

wb = new HSSFWorkbook(in);

} else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010

wb = new XSSFWorkbook(in);

}

return wb;

}

/**

* @param args

*/

public static void main(String[] args) {

List<Map<String,String>> mapList = new ArrayList<Map<String,String>>();

for(int k=0; k<100;k++){

Map<String,String> map = new HashMap<String,String>();

map.put("title_id", "title_id"+k);

map.put("title_name", "title_name"+k);

map.put("title_name_en", "title_name_en"+k);

mapList.add(map);

}

writeExcel(mapList, mapList.size(), "D:/360bizhi/readExcel.xlsx");

}

}

5运行main进行测试，查看写入结果

写入结果和预期是一样的，说明写入成功

请大家多多关注我的头条号，谢谢大家