excel：表格
可以作为一个数据库使用
Pom.xml:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.1.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.hczk</groupId>
<artifactId>hczk</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>hczk</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-freemarker</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>先在本地创建
E:/writeExcel.xlsx或者 E:\\hc.xls
然后进行java方操作
WriteExcel ：将数据写入Excel
package com.hczk.controller;
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.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;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* Created by Administrator on 2018/12/12 0012.
*/
public class WriteExcel {
public static void main(String[] args) {
Map<String, Object> dataMap = new HashMap<>();
dataMap.put("name", "张久风");
dataMap.put("age", 13);
dataMap.put("score", 98);
Map<String, Object> dataMap2 = new HashMap<>();
dataMap2.put("name", "张久yi");
dataMap2.put("age", 134);
dataMap2.put("score", 298);
List<Map> list = new ArrayList<>();
list.add(dataMap);
list.add(dataMap2);
writeExcel(list, dataMap.size(), "E:\\hc.xls");
}
// dataMap.size()==cloumnCount 对象多少属性
public static void writeExcel(List<Map> 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);
if (row != null) {//如果列为空，跳过
sheet.removeRow(row);
}
}
// 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
out = new FileOutputStream(finalXlsxPath);
workBook.write(out);
//第一行
Row rows = sheet.createRow(0);
Cell firsts = rows.createCell(0);
firsts.setCellValue("name");
Cell seconds = rows.createCell(1);
seconds.setCellValue("age");
Cell thirds = rows.createCell(2);
thirds.setCellValue("score");
/**
* 往Excel中写新数据
*/
for (int j = 0; j < dataList.size(); j++) {
// 创建一行：从第二行开始，跳过属性列
Row row = sheet.createRow(j + 1);
// 得到要插入的每一条记录
Map dataMap = dataList.get(j);
String name = dataMap.get("name").toString();
int age = (int) dataMap.get("age");
int score = (int) dataMap.get("score");
for (int k = 0; k < columnNumCount; k++) {
// 在一行内循环
Cell first = row.createCell(0);
first.setCellValue(name);
Cell second = row.createCell(1);
second.setCellValue(age);
Cell third = row.createCell(2);
third.setCellValue(score);
}
}
// 创建文件输出流，准备输出电子表格：这个必须有，否则你在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
*/
// private static final String EXCEL_XLS = "xls";
// private static final String EXCEL_XLSX = "xlsx";
public static Workbook getWorkbok(File file) throws IOException {
Workbook wb = null;
FileInputStream in = new FileInputStream(file);
if (file.getName().endsWith("xls")) { //Excel 2003
wb = new HSSFWorkbook(in);
} else if (file.getName().endsWith("xlsx")) { // Excel 2007/2010
wb = new XSSFWorkbook(in);
}
return wb;
}
}
ReadExcel ：读取Excel中的数据
package com.hczk.controller;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ReadExcel {
public static void main(String[] args) {
Workbook wb = null;
Sheet sheet = null;
Row row = null;
List<Map<String, Object>> list = null;
String cellData = null;
String filePath = "E:\\hc.xls";
String columns[] = {"name", "age", "score"};
wb = readExcel(filePath);
if (wb != null) {
//用来存放表中数据
list = new ArrayList<>();
//获取第一个sheet
sheet = wb.getSheetAt(0);
//获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
//获取第一行
row = sheet.getRow(0);
//获取最大列数
int colnum = row.getPhysicalNumberOfCells();
for (int i = 1; i < rownum; i++) {
Map<String, Object> map = new LinkedHashMap<>();
row = sheet.getRow(i);
if (row != null) {
for (int j = 0; j < colnum; j++) {
cellData = (String) getCellFormatValue(row.getCell(j));
map.put(columns[j], cellData);
}
} else {
break;
}
list.add(map);
}
}
//遍历解析出来的list
for (Map<String, Object> map : list) {
for (Entry<String, Object> entry : map.entrySet()) {
System.out.print(entry.getKey() + ":" + entry.getValue() + ";");
}
System.out.println();
}
}
//读取excel
public static Workbook readExcel(String filePath) {
Workbook wb = null;
if (filePath == null) {
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if (".xls".equals(extString)) {
return new HSSFWorkbook(is);
} else if (".xlsx".equals(extString)) {
return new XSSFWorkbook(is);
} else {
return null;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
public static Object getCellFormatValue(Cell cell) {
Object cellValue = null;
if (cell != null) {
//判断cell类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
//判断cell是否为日期格式
if (DateUtil.isCellDateFormatted(cell)) {
//转换为日期格式YYYY-mm-dd
cellValue = cell.getDateCellValue();
} else {//数字
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING: {
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
}
}经实际检验，对xls、xlsx都操作有效
很nice！
