Apache POI-Excel入门与实战 - ToB企服应用市场:ToB评测及商务社交产业平台

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>

复制代码

/**
* ApachePOI 测试类
*/
public class ApachePOITest {
/**
* 读取 Excel 文件
*/
public static void write() {
// 在内存中创建一个 Excel 文件
XSSFWorkbook excel = new XSSFWorkbook();
// 创建一个工作表sheet
XSSFSheet sheet = excel.createSheet("info");
//在sheet中添加表头第1行,RowNum是从0开始的
XSSFRow row = sheet.createRow(1);
row.createCell(1).setCellValue("姓名");
row.createCell(2).setCellValue("年龄");
//创建一个新的row
XSSFRow row2 = sheet.createRow(2);
//在row中创建单元格
row2.createCell(1).setCellValue("张三");
row2.createCell(2).setCellValue("20");
//创建一个新的row
sheet.createRow(3).createCell(1).setCellValue("李四");
sheet.getRow(3).createCell(2).setCellValue("30");
//将excel写入到文件中
try {
//通过文件输出流将内存中的excel写入到文件中
FileOutputStream fileOutputStream = new FileOutputStream("E:" + File.separator + "POITest.xlsx");
excel.write(fileOutputStream);
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
write();
}
//关闭流
excel.close
}

复制代码

/**
* 读取 Excel 文件
*/
public static void read() throws IOException {
//通过文件输入流读取文件
FileInputStream fileInputStream = new FileInputStream("E:" + File.separator + "POITest.xlsx");
XSSFWorkbook excel = new XSSFWorkbook(fileInputStream);
//读取第一个工作表
//XSSFSheet sheet = excel.getSheet("info");
XSSFSheet sheetAt = excel.getSheetAt(0);
//获取最后一行的行号
int lastRowNum = sheetAt.getLastRowNum();
//从第二行开始读取数据
for (int i = 1; i <= lastRowNum; i++) {
//获取当前行
XSSFRow row = sheetAt.getRow(i);
//获取当前行的最后一个单元格的编号
short lastCellNum = row.getLastCellNum();
//遍历所有的单元格，从第二个单元格开始
for (int j = 1; j < lastCellNum; j++) {
System.out.print(row.getCell(j) + "\t");
}
System.out.println();
}
//关闭流
excel.close();
fileInputStream.close();
}

复制代码

<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpclient</artifactId>
<version>4.5.13</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.76</version>
</dependency>

复制代码

@Test
void WeatherTest() throws URISyntaxException, IOException {
// TODO:这里的key需要引用自己的
String key = "xxxxxxx";
String city = "110101";
CloseableHttpClient httpClient = HttpClients.createDefault();
URIBuilder uriBuilder = new URIBuilder("https://restapi.amap.com/v3/weather/weatherInfo");
uriBuilder.addParameter("key", key);
uriBuilder.addParameter("city", city);
HttpGet httpGet = new HttpGet(uriBuilder.build());
try (CloseableHttpResponse response = httpClient.execute(httpGet)) {
int statusCode = response.getStatusLine().getStatusCode();
if (statusCode == 200) {
HttpEntity entity = response.getEntity();
String result = EntityUtils.toString(entity, "UTF-8");
System.out.println(result);
} else {
System.out.println("Failed to retrieve data, status code: " + statusCode);
}
}
httpClient.close();
}
}

复制代码

@Test
void AdCodeQuery() throws IOException {
// TODO：这里输入下载好的地址对照表
String AMapFilePath = "E:\\AMap_adcode_citycode.xlsx";
// 需要查询的地址
String address = "北京市";
FileInputStream fileInputStream = new FileInputStream(AMapFilePath);
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
XSSFSheet sheetAt = workbook.getSheetAt(0);
for (int i=1;i<sheetAt.getLastRowNum();i++){
XSSFCell cell = sheetAt.getRow(i).getCell(0);
String stringCellValue = cell.getStringCellValue();
if (stringCellValue.equals(address)){
XSSFCell cell1 = sheetAt.getRow(i).getCell(1);
String adcode = cell1.getStringCellValue();
System.out.println("查询到地区编号为"+adcode);
}
}
}

复制代码

@Test
void WeatherTest() throws URISyntaxException, IOException {
// TODO:这里的key需要引用自己的
String key = "xxxxxxx";
String city = "110101";
String extensions = "all";
CloseableHttpClient httpClient = HttpClients.createDefault();
URIBuilder uriBuilder = new URIBuilder("https://restapi.amap.com/v3/weather/weatherInfo");
uriBuilder.addParameter("key", key);
uriBuilder.addParameter("city", city);
uriBuilder.addParameter("extensions",extensions);
HttpGet httpGet = new HttpGet(uriBuilder.build());
try (CloseableHttpResponse response = httpClient.execute(httpGet)) {
int statusCode = response.getStatusLine().getStatusCode();
if (statusCode == 200) {
HttpEntity entity = response.getEntity();
String jsonString = EntityUtils.toString(entity, "UTF-8");
// 通过POI写入文件
// 将JSON字符串解析为Java对象
Map<String, Object> map = JSON.parseObject(jsonString, new TypeReference<Map<String, Object>>() {});
// 获取casts列表
List<Map<String, Object>> casts = (List<Map<String, Object>>) ((List<Map<String, Object>>) map.get("forecasts")).get(0).get("casts");
// 创建Excel工作簿
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Weather Forecasts");
// 创建标题行
Row headerRow = sheet.createRow(0);
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 写入标题
int cellIndex = 0;
String[] columnHeaders = {"date", "week", "dayweather", "nightweather", "daytemp", "nighttemp", "daywind", "nightwind", "daypower", "nightpower", "daytemp_float", "nighttemp_float"};
for (String columnHeader : columnHeaders) {
Cell cell = headerRow.createCell(cellIndex++);
cell.setCellValue(columnHeader);
cell.setCellStyle(headerStyle);
}
// 创建数据行
int rowIndex = 1;
for (Map<String, Object> cast : casts) {
Row row = sheet.createRow(rowIndex++);
// 写入数据
cellIndex = 0;
for (String columnHeader : columnHeaders) {
Cell cell = row.createCell(cellIndex++);
cell.setCellValue(cast.get(columnHeader).toString());
}
}
// 写入Excel文件
try (FileOutputStream fileOut = new FileOutputStream("WeatherForecasts.xlsx")) {
workbook.write(fileOut);
}
// 关闭工作簿
workbook.close();
} else {
System.out.println("Failed to retrieve data, status code: " + statusCode);
}
}
httpClient.close();
}

复制代码