apache poi使用例_Apache-poi工具类的简单使用示例

package cn.jiguang.excel;

import cn.jiguang.entity.ExcelEntity;

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.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.junit.Test;

import java.io.*;

import java.util.List;

import java.util.concurrent.CopyOnWriteArrayList;

/**

* @desc: hssfwork=.xls xssfwork=.xlsx

* @author: zengxc

* @date: 2018/4/26

*/

public class ExcelUtils {

private static final String T_API_USER_PATH = "C:\Users\zengxc\Desktop\dataBase\t_api_user.xls";

private static final String T_API_AUTH_PATH = "C:\Users\zengxc\Desktop\dataBase\t_api_auth.xls";

private static final String RESULT_DATA_MAP = "C:\Users\zengxc\Desktop\dataBase\data_map.xlsx";

private static final String TITLE_CELL_USERNAME = "username";

private static final String TITLE_CELL_DEVKEY = "devkey";

private static final String TITLE_CELL_APIKEY = "apikey";

private static String[] names = new String[3];

static {

names[0] = TITLE_CELL_USERNAME;

names[1] = TITLE_CELL_DEVKEY;

names[2] = TITLE_CELL_APIKEY;

}

@Test

public void test01() throws IOException {

List userDevkey = getDataApiUserByExcel(T_API_USER_PATH);

List apikeyDevkey = getDataApiAuthByExcel(T_API_AUTH_PATH);

apikeyDevkey.forEach(entity ->{

userDevkey.forEach(entityV2 ->{

if (entity.getDevkey().equals(entityV2.getDevkey())){

entity.setUsername(entityV2.getUsername());

}

});

});

handlerDataSetAndExport(RESULT_DATA_MAP, apikeyDevkey, names);

}

/**

* 处理数据集并导出

*/

protected static void handlerDataSetAndExport(String filePath, List entityList, String[] names) throws IOException {

// 写出到excel

XSSFWorkbook xssfWorkbook = new XSSFWorkbook();

XSSFSheet sheet = xssfWorkbook.createSheet("user-devkey-apikey");

XSSFRow fristRow = sheet.createRow(0);

XSSFCell fristCell = fristRow.createCell(0);

fristCell.setCellValue(names[0]);

fristCell = fristRow.createCell(1);

fristCell.setCellValue(names[1]);

fristCell = fristRow.createCell(2);

fristCell.setCellValue(names[2]);

for (int i = 1; i < entityList.size() + 1; i++) {

XSSFRow row = sheet.createRow(i);

ExcelEntity excelEntity = entityList.get(i - 1);

XSSFCell cell = row.createCell(0);

cell.setCellValue(excelEntity.getUsername());

cell = row.createCell(1);

cell.setCellValue(excelEntity.getDevkey());

cell = row.createCell(2);

cell.setCellValue(excelEntity.getApikey());

}

FileOutputStream fos = new FileOutputStream(new File(filePath));

BufferedOutputStream bis = new BufferedOutputStream(fos);

xssfWorkbook.write(bis);

// 关流

xssfWorkbook.close();

closeOsAll(bis, fos);

}

/**

* 从excel中获取devkey和username关系

* @return

*/

protected static List getDataApiUserByExcel(String filePath) throws IOException {

InputStream is = new FileInputStream(new File(filePath));

BufferedInputStream bis = new BufferedInputStream(is);

HSSFWorkbook workbook = new HSSFWorkbook(bis);

HSSFSheet sheet = workbook.getSheetAt(0);

List excelList = new CopyOnWriteArrayList<>();

int lastRowNum = sheet.getLastRowNum();

// 遍历行数

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

HSSFRow row = sheet.getRow(i);

int lastCellNum = row.getLastCellNum();

// 遍历每行的单元格

ExcelEntity excelEntity = new ExcelEntity();

for (int j = 0; j < lastCellNum; j++) {

if (j == 2){

HSSFCell cell = row.getCell(2);

excelEntity.setDevkey(cell.getStringCellValue());

}

if (j == 7){

HSSFCell cell = row.getCell(7);

excelEntity.setUsername(cell.getStringCellValue());

}

}

excelList.add(excelEntity);

}

//关流

workbook.close();

closeIsAll(bis, is);

return excelList;

}

/**

* 从excel中获取apikey和devkey关系

* @return

* @throws IOException

*/

protected static List getDataApiAuthByExcel(String filePath) throws IOException {

InputStream is = new FileInputStream(new File(filePath));

BufferedInputStream bis = new BufferedInputStream(is);

HSSFWorkbook workbook = new HSSFWorkbook(bis);

HSSFSheet sheet = workbook.getSheetAt(0);

List excelList = new CopyOnWriteArrayList<>();

int lastRowNum = sheet.getLastRowNum();

// 遍历行数

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

HSSFRow row = sheet.getRow(i);

int lastCellNum = row.getLastCellNum();

// 遍历每行的单元格

ExcelEntity excelEntity = new ExcelEntity();

for (int j = 0; j < lastCellNum; j++) {

if (j == 2){

HSSFCell cell = row.getCell(2);

excelEntity.setDevkey(cell.getStringCellValue());

}

if (j == 3){

HSSFCell cell = row.getCell(3);

excelEntity.setApikey(cell.getStringCellValue());

}

}

excelList.add(excelEntity);

}

//关流

workbook.close();

closeIsAll(bis, is);

return excelList;

}

protected static void closeOsAll(OutputStream... os) throws IOException {

for (OutputStream stream : os) {

if (stream != null){

stream.close();

}

}

}

protected static void closeIsAll(InputStream... is) throws IOException {

for (InputStream stream : is) {

if (stream != null){

stream.close();

}

}

}

}

package cn.jiguang.entity;

import lombok.Data;

/**

* @desc: 工作表字段实体

* @author: zengxc

* @date: 2018/4/26

*/

@Data

public class ExcelEntity {

private String apikey;

private String devkey;

private String username;

public ExcelEntity(){}

public ExcelEntity(String apikey, String devkey){

this.apikey = apikey;

this.devkey = devkey;

}

public ExcelEntity(String apikey, String devkey, String username){

this.apikey = apikey;

this.devkey = devkey;

this.username = username;

}

}