- pom依赖
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version> <!-- 最新版本号 -->
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version> <!-- 最新版本号 -->
</dependency>
</dependencies>
- Java解析excel例子
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;
public class ExcelParserWithHeaders {
public static void main(String[] args) {
String excelFilePath = "path/to/your/excel/file.xlsx";
try (FileInputStream fis = new FileInputStream(excelFilePath);
Workbook workbook = new XSSFWorkbook(fis)) {
Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
Row headerRow = sheet.getRow(0); // 获取表头行
for (Row row : sheet) {
for (Cell cell : row) {
int columnIndex = cell.getColumnIndex();
String columnHeader = headerRow.getCell(columnIndex).getStringCellValue();
switch (cell.getCellType()) {
case STRING:
System.out.println(columnHeader + ": " + cell.getStringCellValue());
break;
case NUMERIC:
System.out.println(columnHeader + ": " + cell.getNumericCellValue());
break;
case BOOLEAN:
System.out.println(columnHeader + ": " + cell.getBooleanCellValue());
break;
case BLANK:
System.out.println(columnHeader + ": BLANK");
break;
default:
System.out.println(columnHeader + ": UNKNOWN");
}
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
- 如何更改单元格的内容
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellAddress;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelCellUpdateExample {
public static void main(String[] args) {
String excelFilePath = "path/to/your/excel/file.xlsx";
try (FileInputStream fis = new FileInputStream(excelFilePath);
Workbook workbook = new XSSFWorkbook(fis)) {
Sheet sheet = workbook.getSheetAt(0); // Assuming you want to update the first sheet
Row row = sheet.getRow(0); // Assuming you want to update the first row
Cell cell = row.getCell(0); // Assuming you want to update the first cell
// Update the cell value
cell.setCellValue("New Value");
try (FileOutputStream fos = new FileOutputStream(excelFilePath)) {
workbook.write(fos);
} catch (IOException e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
ps:
这里使用的是XSSF（XML Spreadsheet Format）格式的Workbook，适用于.xlsx文件。如果您的文件是.xls格式，您需要使用HSSFWorkbook而不是XSSFWorkbook。
- 导入审批记录脚本，提取表格内容，导入到ES引擎，作为检索元数据
package com.weimob.mp.activity.data.center.frame.utils;
import com.alibaba.fastjson.JSON;
import com.google.common.collect.Lists;
import com.weimob.mp.activity.data.center.dto.AuditDTO;
import com.weimob.zipkin.ZipkinContext;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.http.*;
import org.apache.http.auth.AuthScope;
import org.apache.http.auth.UsernamePasswordCredentials;
import org.apache.http.client.CredentialsProvider;
import org.apache.http.impl.client.BasicCredentialsProvider;
import org.apache.http.protocol.HttpContext;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.*;
import org.elasticsearch.action.DocWriteRequest;
import org.elasticsearch.action.bulk.BulkRequest;
import org.elasticsearch.action.bulk.BulkResponse;
import org.elasticsearch.action.index.IndexRequest;
import org.elasticsearch.client.RequestOptions;
import org.elasticsearch.client.RestClient;
import org.elasticsearch.client.RestClientBuilder;
import org.elasticsearch.client.RestHighLevelClient;
import org.elasticsearch.common.xcontent.XContentType;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;
public class ExcelTest {
public static final int MAX_CONN_TOTAL = 100;
public static final int CONNECT_TIMEOUT = 180_000;
public static final int SOCKET_TIMEOUT = 180_000;
public static final int MAX_CONN_PER_ROUTE = 10;
public static final String NODE = "weimob-public-qa.es.internal.weimobqa.com:9200";
public static final String NAME = "saas-mp-promotion";
public static final String PASS_WORD = "6bwtsFv3)Rxy";
// 创建一个静态的RestHighLevelClient实例
private static final RestHighLevelClient restHighLevelClient = getRestHighLevelClient();
public static void main(String[] args) {
String excelFilePath = "/Users/aalsc/Desktop/opsflow_marketing.xlsx";
List<AuditDTO> auditDTOList = Lists.newArrayList();
//FileInputStream 在 try 块的开始处被初始化，然后在 try 块结束时自动关闭，无需手动调用 close() 方法。这将确保文件流在不再需要时被正确地关闭，以避免资源泄漏问题
try (FileInputStream fis = new FileInputStream(excelFilePath);
Workbook workbook = new XSSFWorkbook(fis)) {
Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
Row headerRow = sheet.getRow(0); // 获取表头行
System.err.println("开始执行了");
for (Row row : sheet) {
if (row == null) {
continue;
}
if (row.getRowNum() == 0) {
continue;
}
AuditDTO auditDTO = new AuditDTO();
for (Cell cell : row) {
if (cell == null) {
continue;
}
int columnIndex = cell.getColumnIndex();
if (columnIndex == 5) {
continue;
}
//只记录前9列
if (columnIndex>8) {
break;
}
String columnHeader = headerRow.getCell(columnIndex).getStringCellValue();
if (CellType.STRING.equals(cell.getCellType())) {
if (StringUtils.equals("模板", columnHeader)) {
auditDTO.setTemplateName(cell.getStringCellValue());
}else if (StringUtils.equals("申请人", columnHeader)) {
auditDTO.setApplicate(cell.getStringCellValue());
}else if (StringUtils.equals("appId", columnHeader)) {
auditDTO.setAppId(cell.getStringCellValue());
} else if (StringUtils.equals("状态", columnHeader)) {
auditDTO.setStatus(cell.getStringCellValue());
} else if (StringUtils.equals("处理结果", columnHeader)) {
auditDTO.setDealResult(cell.getStringCellValue());
}
} else if (CellType.NUMERIC.equals(cell.getCellType()) && DateUtil.isCellDateFormatted(cell)) {
Date dateValue = cell.getDateCellValue();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String formattedDate = dateFormat.format(dateValue);
if (StringUtils.equals("申请时间", columnHeader)) {
auditDTO.setCreateTime(formattedDate);
} else if (StringUtils.equals("更新时间", columnHeader)) {
auditDTO.setUpdateTime(formattedDate);
}
} else if (CellType.NUMERIC.equals(cell.getCellType())) {
if (StringUtils.equals("ID", columnHeader)) {
auditDTO.setAuditId((long) cell.getNumericCellValue());
}
} else if (CellType.BLANK.equals(cell.getCellType())) {
System.out.println(columnHeader + ": BLANK");
} else {
System.out.println(columnHeader + ": " + cell.getCellType());
}
}
auditDTOList.add(auditDTO);
if (auditDTOList.size() == 50) {
System.out.println("开始插入");
insertES(auditDTOList);
auditDTOList.clear();
}
}
} catch (IOException e) {
System.err.println("读取excel失败");
e.printStackTrace();
}
//最后一次
if (CollectionUtils.isNotEmpty(auditDTOList)) {
System.err.println(JSON.toJSONString(auditDTOList));
System.out.println("最后一次开始插入：auditId:"+ auditDTOList.get(auditDTOList.size()-1).getAuditId());
insertES(auditDTOList);
}
System.err.println("执行结束了");
// 在程序结束时关闭RestHighLevelClient
Runtime.getRuntime().addShutdownHook(new Thread(() -> {
try {
restHighLevelClient.close();
} catch (IOException e) {
e.printStackTrace();
}
}));
}
public static void insertES (List<AuditDTO> auditDTOList) {
if (CollectionUtils.isEmpty(auditDTOList)) {
return;
}
BulkRequest bulkRequest = new BulkRequest();
for (AuditDTO auditDTO : auditDTOList) {
IndexRequest indexRequest = new IndexRequest();
indexRequest.index("saas-mp-promotion-audit_log");
indexRequest.id(String.valueOf(auditDTO.getAuditId()));
indexRequest.source(JSON.toJSONString(auditDTO), XContentType.JSON);
bulkRequest.add(indexRequest);
}
BulkResponse response = null;
try {
response = restHighLevelClient.bulk(bulkRequest, RequestOptions.DEFAULT);
} catch (IOException e) {
System.err.println(JSON.toJSONString(auditDTOList));
}
}
public static RestHighLevelClient getRestHighLevelClient() {
List<HttpHost> httpHostList = new ArrayList<>();
String[] clusterNodes = StringUtils.split(NODE, ",");
if (ArrayUtils.isEmpty(clusterNodes)) {
throw new RuntimeException("没有配置es地址.");
}
for (String clusterNode : clusterNodes) {
String hostName = StringUtils.substringBeforeLast(clusterNode, ":");
String port = StringUtils.substringAfterLast(clusterNode, ":");
httpHostList.add(new HttpHost(hostName, Integer.parseInt(port)));
}
RestClientBuilder restClientBuilder = RestClient.builder(httpHostList.toArray(new HttpHost[0]));
RestClientBuilder.HttpClientConfigCallback httpClientConfigCallback = getHttpClientConfigCallback();
if (httpClientConfigCallback != null) {
restClientBuilder.setHttpClientConfigCallback(httpClientConfigCallback);
}
RestClientBuilder.RequestConfigCallback requestConfigCallback = getRequestConfigCallback();
if (requestConfigCallback != null) {
restClientBuilder.setRequestConfigCallback(requestConfigCallback);
}
return new RestHighLevelClient(restClientBuilder);
}
/**
* 开启授权
*
* @return
*/
public static RestClientBuilder.HttpClientConfigCallback getHttpClientConfigCallback() {
final CredentialsProvider credentialsProvider = new BasicCredentialsProvider();
credentialsProvider.setCredentials(
AuthScope.ANY,
new UsernamePasswordCredentials(NAME, PASS_WORD)
);
return httpClientBuilder -> httpClientBuilder
.addInterceptorFirst(new HttpRequestInterceptor(){
@Override
public void process(HttpRequest request, HttpContext context) throws HttpException, IOException {
long interceptorStart = System.currentTimeMillis();
context.setAttribute("InterceptorStart",interceptorStart);
String traceId = ZipkinContext.getContext().getGlobalTicket();
if(!StringUtils.isEmpty(traceId)){
request.addHeader("X-Opaque-ID",traceId);
}
}
})
.addInterceptorFirst(new HttpResponseInterceptor(){
@Override
public void process(HttpResponse response, HttpContext context) throws HttpException, IOException {
long interceptorEnd = System.currentTimeMillis();
Object interceptorStart = context.getAttribute("InterceptorStart");
long cost = 0;
if(Objects.nonNull(interceptorStart)){
cost = interceptorEnd - Long.parseLong(String.valueOf(interceptorStart));
}
}
})
.setDefaultCredentialsProvider(credentialsProvider)
.setMaxConnTotal(MAX_CONN_TOTAL)
.setMaxConnPerRoute(MAX_CONN_PER_ROUTE);
}
public static RestClientBuilder.RequestConfigCallback getRequestConfigCallback() {
return requestConfigBuilder -> requestConfigBuilder
.setConnectTimeout(CONNECT_TIMEOUT)
.setSocketTimeout(SOCKET_TIMEOUT);
}
}
扫一扫
337
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
