java操作excel、word

150 阅读 0 评论 99 点赞

我是靠谱客的博主顺利蜗牛，这篇文章主要介绍java操作excel、word，现在分享给大家，希望可以做个参考。

一、用到的代码：

源码工程在这里：https://download.csdn.net/download/pp814274513/86771971

配合使用的de工具在这里：excel宏编程工具合并多个excel文件合并多个sheet

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
import javafx.scene.control.Cell;
import jxl.Sheet;
import jxl.Workbook;
import java.io.File;
/**
* 简单的操作
*/
public class Excel2 {
public static void main(String[] args) throws Exception {
ReadExcel("");
}
private static void ReadExcel(String url) throws Exception {
// 指定文件夹路径
File dir = new File("F:\myWorkFiles\xxx\2022年10月8日，导入数据\数据\临时\dddd - 副本");
// 获取该路径下所有excel文件
File[] files = dir.listFiles();
for (File file : files) {
// 注意调过这些字符打头的文件
if (file.getPath().toString().contains("$")) {
continue;
}
//1:创建workbook
Workbook workbook = Workbook.getWorkbook(file);
//2:获取第一个工作表sheet，下标从0开始表示第一个工作表
Sheet sheet = workbook.getSheet(0);
//3:获取数据
String s = "";
// 遍历每一行数据
for (int i = 0; i < sheet.getRows(); i++) {
// 遍历每一列数据
for (int j = 0; j < sheet.getColumns(); j++) {
// 获取单元格，这里注意j i的顺序不要颠倒
Cell cell = (Cell) sheet.getCell(j, i);
s += cell.getText() + "####";
}
}
System.out.println(s);
//4：关闭资源
workbook.close();
}
}
}

复制代码

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.PrintStream;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class ExcelConsoleFile {
public static void main(String[] args) throws Exception {
ReadExcel("");
}
private static void ReadExcel(String url) throws Exception {
// 注意这里，由于输出到控制台数据太多，展示不全，所以输出到文件
String path2222 = "F:\myWorkFiles\XXXXXXX\2022年10月8日，导入数据\数据\临时\111.txt";
FileOutputStream puts = new FileOutputStream(path2222, true);
PrintStream out = new PrintStream(puts);
System.setOut(out);
File dir = new File("F:\myWorkFiles\XXXXXXX\2022年10月8日，导入数据\数据\临时\物业机房整理半截快好了的");
// 获取到所有服务单位文件夹
File[] serviceCompanys = dir.listFiles();
for (File serviceCompany : serviceCompanys) {
String[] fwzxs = serviceCompany.getPath().split("\\");
String fwzx = fwzxs[fwzxs.length - 1];
//
System.out.println("服务中心【："+fwzx+"【");
// 服务单位下的文件
File[] files = new File(serviceCompany.getPath()).listFiles();
for (File file : files) {
if (file.getPath().contains("$") || !file.getPath().contains("物业机房统计表") || !file.getPath().toString().endsWith("xls")) {
continue;
}
// 获取物业机房文件
Workbook workbook = Workbook.getWorkbook(file);
String id = "";
Sheet aaa = workbook.getSheet(2);
for (int i = 1; i < aaa.getRows() - 1; i++) {
Cell[] cells = aaa.getRow(i);
// 替换所有回车、空白
Pattern p = Pattern.compile("\s*|t|r|n");
if (cells.length == 0) {
continue;
}
if (cells[0].getContents().split("]").length != 2) {
continue;
}
Matcher m = p.matcher(cells[0].getContents().split("]")[1]);
id = "shebei" + fwzx + i + System.currentTimeMillis();
// 分类
String smallType = m.replaceAll("").replaceAll("B1、B2", "地下");
// 名称
Matcher n =
p.matcher(cells[2].getContents() + "，" + cells[3].getContents() + "，" + cells[4].getContents() + "，" + cells[5].getContents());
String name = n.replaceAll("");
// 位置
String location =
"楼号：" + cells[2].getContents() + "，层数：" + cells[3].getContents() + "，位置：" + cells[4].getContents() +
"，" + cells[5].getContents();
System.out.println("INSERT tab_resource ( id, company_id, resource_name, resource_location, " +
"small_type, create_time, del_flag )VALUES( '" + id + "', '" + fwzx + "', '" + name + "', '" + location + "', " +
"'" + smallType + "', NOW( ), 0 );");
// 获取责任人联系电话
Cell cell1 = (Cell) workbook.getSheet(0).getCell(3, 1);
Pattern p2 = Pattern.compile("\s*|t|r|n");
Matcher m2 = p2.matcher(cell1.getContents());
String dest =
m2.replaceAll("").replaceAll("负责人", "").replaceAll("电话", "").replaceAll("1：", "").replaceAll("2："
, "").replaceAll("3：", "");
List<String> zerenrens = PhoneNum.checkCellphone(dest);
for (int i2 = 0; i2 < zerenrens.size(); i2++) {
System.out.println("INSERT into tab_resource_people(id,resource_id,user_id,create_time,del_flag) " +
"VALUES (UUID(),'" + id + "','" + zerenrens.get(i2) + "',NOW(),0);");
}
}
workbook.close();
}
}
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.PrintStream;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class ExcelConsoleFile {
public static void main(String[] args) throws Exception {
ReadExcel("");
}
private static void ReadExcel(String url) throws Exception {
// 注意这里，由于输出到控制台数据太多，展示不全，所以输出到文件
String path2222 = "F:\myWorkFiles\XXXXXXX\2022年10月8日，导入数据\数据\临时\111.txt";
FileOutputStream puts = new FileOutputStream(path2222, true);
PrintStream out = new PrintStream(puts);
System.setOut(out);
File dir = new File("F:\myWorkFiles\XXXXXXX\2022年10月8日，导入数据\数据\临时\物业机房整理半截快好了的");
// 获取到所有服务单位文件夹
File[] serviceCompanys = dir.listFiles();
for (File serviceCompany : serviceCompanys) {
String[] fwzxs = serviceCompany.getPath().split("\\");
String fwzx = fwzxs[fwzxs.length - 1];
//
System.out.println("服务中心【："+fwzx+"【");
// 服务单位下的文件
File[] files = new File(serviceCompany.getPath()).listFiles();
for (File file : files) {
if (file.getPath().contains("$") || !file.getPath().contains("物业机房统计表") || !file.getPath().toString().endsWith("xls")) {
continue;
}
// 获取物业机房文件
Workbook workbook = Workbook.getWorkbook(file);
String id = "";
Sheet aaa = workbook.getSheet(2);
for (int i = 1; i < aaa.getRows() - 1; i++) {
Cell[] cells = aaa.getRow(i);
// 替换所有回车、空白
Pattern p = Pattern.compile("\s*|t|r|n");
if (cells.length == 0) {
continue;
}
if (cells[0].getContents().split("]").length != 2) {
continue;
}
Matcher m = p.matcher(cells[0].getContents().split("]")[1]);
id = "shebei" + fwzx + i + System.currentTimeMillis();
// 分类
String smallType = m.replaceAll("").replaceAll("B1、B2", "地下");
// 名称
Matcher n =
p.matcher(cells[2].getContents() + "，" + cells[3].getContents() + "，" + cells[4].getContents() + "，" + cells[5].getContents());
String name = n.replaceAll("");
// 位置
String location =
"楼号：" + cells[2].getContents() + "，层数：" + cells[3].getContents() + "，位置：" + cells[4].getContents() +
"，" + cells[5].getContents();
System.out.println("INSERT tab_resource ( id, company_id, resource_name, resource_location, " +
"small_type, create_time, del_flag )VALUES( '" + id + "', '" + fwzx + "', '" + name + "', '" + location + "', " +
"'" + smallType + "', NOW( ), 0 );");
// 获取责任人联系电话
Cell cell1 = (Cell) workbook.getSheet(0).getCell(3, 1);
Pattern p2 = Pattern.compile("\s*|t|r|n");
Matcher m2 = p2.matcher(cell1.getContents());
String dest =
m2.replaceAll("").replaceAll("负责人", "").replaceAll("电话", "").replaceAll("1：", "").replaceAll("2："
, "").replaceAll("3：", "");
List<String> zerenrens = PhoneNum.checkCellphone(dest);
for (int i2 = 0; i2 < zerenrens.size(); i2++) {
System.out.println("INSERT into tab_resource_people(id,resource_id,user_id,create_time,del_flag) " +
"VALUES (UUID(),'" + id + "','" + zerenrens.get(i2) + "',NOW(),0);");
}
}
workbook.close();
}
}
}
}

复制代码

import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* 从一堆字符里提取电话号码
*/
public class PhoneNum {
public static void main(String[] args) {
//要提前号码的字符串
String str = "n13977777777s18911111111你好15988888888hha0955-7777777sss0775-6678111";
//提取手机号码
checkCellphone(str);
//提取固定电话号码
checkTelephone(str);
}
/**
* 查询符合的手机号码
*
* @param str
* @return
*/
public static List<String> checkCellphone(String str) {
// 将给定的正则表达式编译到模式中
Pattern pattern = Pattern.compile("((13[0-9])|(14[5|7])|(15([0-3]|[5-9]))|(18[0,5-9]))\d{8}");
// 创建匹配给定输入与此模式的匹配器。
Matcher matcher = pattern.matcher(str);
//查找字符串中是否有符合的子字符串
List<String> phoneGrop = new ArrayList<>();
while (matcher.find()) {
phoneGrop.add(matcher.group());
//查找到符合的即输出
//
System.out.println("查询到一个符合的手机号码：" + matcher.group());
}
return phoneGrop;
}
/**
* 查询符合的固定电话
*
* @param str
*/
public static void checkTelephone(String str) {
// 将给定的正则表达式编译到模式中
Pattern pattern = Pattern.compile("(0\d{2}-\d{8}(-\d{1,4})?)|(0\d{3}-\d{7,8}(-\d{1,4})?)");
// 创建匹配给定输入与此模式的匹配器。
Matcher matcher = pattern.matcher(str);
//查找字符串中是否有符合的子字符串
while (matcher.find()) {
//查找到符合的即输出
System.out.println("查询到一个符合的固定号码：" + matcher.group());
}
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* 从一堆字符里提取电话号码
*/
public class PhoneNum {
public static void main(String[] args) {
//要提前号码的字符串
String str = "n13977777777s18911111111你好15988888888hha0955-7777777sss0775-6678111";
//提取手机号码
checkCellphone(str);
//提取固定电话号码
checkTelephone(str);
}
/**
* 查询符合的手机号码
*
* @param str
* @return
*/
public static List<String> checkCellphone(String str) {
// 将给定的正则表达式编译到模式中
Pattern pattern = Pattern.compile("((13[0-9])|(14[5|7])|(15([0-3]|[5-9]))|(18[0,5-9]))\d{8}");
// 创建匹配给定输入与此模式的匹配器。
Matcher matcher = pattern.matcher(str);
//查找字符串中是否有符合的子字符串
List<String> phoneGrop = new ArrayList<>();
while (matcher.find()) {
phoneGrop.add(matcher.group());
//查找到符合的即输出
//
System.out.println("查询到一个符合的手机号码：" + matcher.group());
}
return phoneGrop;
}
/**
* 查询符合的固定电话
*
* @param str
*/
public static void checkTelephone(String str) {
// 将给定的正则表达式编译到模式中
Pattern pattern = Pattern.compile("(0\d{2}-\d{8}(-\d{1,4})?)|(0\d{3}-\d{7,8}(-\d{1,4})?)");
// 创建匹配给定输入与此模式的匹配器。
Matcher matcher = pattern.matcher(str);
//查找字符串中是否有符合的子字符串
while (matcher.find()) {
//查找到符合的即输出
System.out.println("查询到一个符合的固定号码：" + matcher.group());
}
}
}

复制代码

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
public class ReadExcelhuiyishizhegnligeshi {
public static void main(String[] args) throws Exception {
excel();
}
public static void excel() throws Exception {
File dir = new File("F:\myWorkFiles\XXXXXXX\2022年10月8日，导入数据\数据\临时\会议室资源 - 整理数据格式\最终");
File[] files = dir.listFiles();
for (File file : files) {
if (!file.exists()) {
throw new Exception("文件不存在!");
}
if(file.getPath().contains("$")){
continue;
}
InputStream in = new FileInputStream(file);
if(file.getPath().endsWith(".xlsx")){
// 读取整个Excel
XSSFWorkbook sheets = new XSSFWorkbook(in);
// 获取第一个表单Sheet
XSSFSheet sheetAt = sheets.getSheetAt(0);
for (int i = 0; i < 503; i++) {
String id = "huiyishi"+i;
String bangongqu = sheetAt.getRow(i).getCell(0).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(1).setCellType(CellType.STRING);
String roomName = sheetAt.getRow(i).getCell(1).getStringCellValue().replaceAll("\s*", "");
String location = sheetAt.getRow(i).getCell(2).getStringCellValue().replaceAll("\s*", "");
String smallType = sheetAt.getRow(i).getCell(3).getStringCellValue().replaceAll("\s*", "");
Object remark;
if(sheetAt.getRow(i).getCell(4)!= null){
if(sheetAt.getRow(i).getCell(4).getCellType().equals(CellType.NUMERIC)){
sheetAt.getRow(i).getCell(4).setCellType(CellType.NUMERIC);
remark = sheetAt.getRow(i).getCell(4).getNumericCellValue();
}else {
sheetAt.getRow(i).getCell(4).setCellType(CellType.STRING);
remark = sheetAt.getRow(i).getCell(4).getStringCellValue().replaceAll("\s*", "");
}
}else{
remark = "";
}
String companyId= sheetAt.getRow(i).getCell(5).getStringCellValue().replaceAll("\s*", "");
String baozhangdanwei = sheetAt.getRow(i).getCell(6).getStringCellValue().replaceAll("\s*", "");
//
System.out.println("huiyishi"+i+"，"+bangongqu+"，"+roomName+"，"+location+"，"+smallType+"，"+remark+"，"+companyId+
//
"，"+baozhangdanwei);
//
System.out.println("INSERT INTO tab_resource (id,resource_name,big_type,small_type,office_area," +
//
"resource_location,company_id,create_time)VALUES('"+id+"','"+roomName+"'," +
//
"'1503605963402776502','"+smallType+"'," +
//
"'"+bangongqu+"','"+location+"','"+companyId+"',now());");
String fuzerenName1 = sheetAt.getRow(i).getCell(7).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(8).setCellType(CellType.STRING);
String fuzerenphone1 = sheetAt.getRow(i).getCell(8).getStringCellValue().replaceAll("\s*", "");
if(!"".equals(fuzerenName1)){
//
System.out.println(fuzerenName1+"，"+fuzerenphone1);
System.out.println("INSERT INTO tab_resource_people ( id, resource_id, duty_type, user_id ) VALUES
( uuid(), '"+id+"', 'zrdw100', '"+fuzerenphone1+"' );");
}
String fuzerenName2 = "",fuzerenphone2 = "";
if(sheetAt.getRow(i).getCell(9)!= null){
fuzerenName2 = sheetAt.getRow(i).getCell(9).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(10).setCellType(CellType.STRING);
fuzerenphone2 = sheetAt.getRow(i).getCell(10).getStringCellValue().replaceAll("\s*", "");
}
if(!"".equals(fuzerenName2)){
//
System.out.println(fuzerenName2+"，"+fuzerenphone2);
System.out.println("INSERT INTO tab_resource_people ( id, resource_id, duty_type, user_id ) VALUES
( uuid(), '"+id+"', 'zrdw100', '"+fuzerenphone2+"' );");
}
String fuzerenName3 = "",fuzerenphone3 = "";
if(sheetAt.getRow(i).getCell(11)!= null){
fuzerenName3 = sheetAt.getRow(i).getCell(11).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(12).setCellType(CellType.STRING);
fuzerenphone3 = sheetAt.getRow(i).getCell(12).getStringCellValue().replaceAll("\s*", "");
}
if(!"".equals(fuzerenName3)){
//
System.out.println(fuzerenName3+"，"+fuzerenphone3);
System.out.println("INSERT INTO tab_resource_people ( id, resource_id, duty_type, user_id ) VALUES
( uuid(), '"+id+"', 'zrdw100', '"+fuzerenphone3+"' );");
}
String fuzerenName4 = "",fuzerenphone4="";
if(sheetAt.getRow(i).getCell(13) != null){
fuzerenName4 = sheetAt.getRow(i).getCell(13).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(14).setCellType(CellType.STRING);
fuzerenphone4 = sheetAt.getRow(i).getCell(14).getStringCellValue().replaceAll("\s*", "");
}
if(!"".equals(fuzerenName4)){
//
System.out.println(fuzerenName4+"，"+fuzerenphone4);
System.out.println("INSERT INTO tab_resource_people ( id, resource_id, duty_type, user_id ) VALUES
( uuid(), '"+id+"', 'zrdw100', '"+fuzerenphone4+"' );");
}
//
System.out.println("=====================================");
}
}
}
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
public class ReadExcelhuiyishizhegnligeshi {
public static void main(String[] args) throws Exception {
excel();
}
public static void excel() throws Exception {
File dir = new File("F:\myWorkFiles\XXXXXXX\2022年10月8日，导入数据\数据\临时\会议室资源 - 整理数据格式\最终");
File[] files = dir.listFiles();
for (File file : files) {
if (!file.exists()) {
throw new Exception("文件不存在!");
}
if(file.getPath().contains("$")){
continue;
}
InputStream in = new FileInputStream(file);
if(file.getPath().endsWith(".xlsx")){
// 读取整个Excel
XSSFWorkbook sheets = new XSSFWorkbook(in);
// 获取第一个表单Sheet
XSSFSheet sheetAt = sheets.getSheetAt(0);
for (int i = 0; i < 503; i++) {
String id = "huiyishi"+i;
String bangongqu = sheetAt.getRow(i).getCell(0).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(1).setCellType(CellType.STRING);
String roomName = sheetAt.getRow(i).getCell(1).getStringCellValue().replaceAll("\s*", "");
String location = sheetAt.getRow(i).getCell(2).getStringCellValue().replaceAll("\s*", "");
String smallType = sheetAt.getRow(i).getCell(3).getStringCellValue().replaceAll("\s*", "");
Object remark;
if(sheetAt.getRow(i).getCell(4)!= null){
if(sheetAt.getRow(i).getCell(4).getCellType().equals(CellType.NUMERIC)){
sheetAt.getRow(i).getCell(4).setCellType(CellType.NUMERIC);
remark = sheetAt.getRow(i).getCell(4).getNumericCellValue();
}else {
sheetAt.getRow(i).getCell(4).setCellType(CellType.STRING);
remark = sheetAt.getRow(i).getCell(4).getStringCellValue().replaceAll("\s*", "");
}
}else{
remark = "";
}
String companyId= sheetAt.getRow(i).getCell(5).getStringCellValue().replaceAll("\s*", "");
String baozhangdanwei = sheetAt.getRow(i).getCell(6).getStringCellValue().replaceAll("\s*", "");
//
System.out.println("huiyishi"+i+"，"+bangongqu+"，"+roomName+"，"+location+"，"+smallType+"，"+remark+"，"+companyId+
//
"，"+baozhangdanwei);
//
System.out.println("INSERT INTO tab_resource (id,resource_name,big_type,small_type,office_area," +
//
"resource_location,company_id,create_time)VALUES('"+id+"','"+roomName+"'," +
//
"'1503605963402776502','"+smallType+"'," +
//
"'"+bangongqu+"','"+location+"','"+companyId+"',now());");
String fuzerenName1 = sheetAt.getRow(i).getCell(7).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(8).setCellType(CellType.STRING);
String fuzerenphone1 = sheetAt.getRow(i).getCell(8).getStringCellValue().replaceAll("\s*", "");
if(!"".equals(fuzerenName1)){
//
System.out.println(fuzerenName1+"，"+fuzerenphone1);
System.out.println("INSERT INTO tab_resource_people ( id, resource_id, duty_type, user_id ) VALUES
( uuid(), '"+id+"', 'zrdw100', '"+fuzerenphone1+"' );");
}
String fuzerenName2 = "",fuzerenphone2 = "";
if(sheetAt.getRow(i).getCell(9)!= null){
fuzerenName2 = sheetAt.getRow(i).getCell(9).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(10).setCellType(CellType.STRING);
fuzerenphone2 = sheetAt.getRow(i).getCell(10).getStringCellValue().replaceAll("\s*", "");
}
if(!"".equals(fuzerenName2)){
//
System.out.println(fuzerenName2+"，"+fuzerenphone2);
System.out.println("INSERT INTO tab_resource_people ( id, resource_id, duty_type, user_id ) VALUES
( uuid(), '"+id+"', 'zrdw100', '"+fuzerenphone2+"' );");
}
String fuzerenName3 = "",fuzerenphone3 = "";
if(sheetAt.getRow(i).getCell(11)!= null){
fuzerenName3 = sheetAt.getRow(i).getCell(11).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(12).setCellType(CellType.STRING);
fuzerenphone3 = sheetAt.getRow(i).getCell(12).getStringCellValue().replaceAll("\s*", "");
}
if(!"".equals(fuzerenName3)){
//
System.out.println(fuzerenName3+"，"+fuzerenphone3);
System.out.println("INSERT INTO tab_resource_people ( id, resource_id, duty_type, user_id ) VALUES
( uuid(), '"+id+"', 'zrdw100', '"+fuzerenphone3+"' );");
}
String fuzerenName4 = "",fuzerenphone4="";
if(sheetAt.getRow(i).getCell(13) != null){
fuzerenName4 = sheetAt.getRow(i).getCell(13).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(14).setCellType(CellType.STRING);
fuzerenphone4 = sheetAt.getRow(i).getCell(14).getStringCellValue().replaceAll("\s*", "");
}
if(!"".equals(fuzerenName4)){
//
System.out.println(fuzerenName4+"，"+fuzerenphone4);
System.out.println("INSERT INTO tab_resource_people ( id, resource_id, duty_type, user_id ) VALUES
( uuid(), '"+id+"', 'zrdw100', '"+fuzerenphone4+"' );");
}
//
System.out.println("=====================================");
}
}
}
}
}

复制代码

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
/**
* 考虑到xls和xlsx兼容性的
*/
public class ReadExceljianrong {
public static void main(String[] args) throws Exception {
excel();
}
public static void excel() throws Exception {
File dir = new File("F:\myWorkFiles\XXXXXXX\2022年10月8日，导入数据\数据\临时\会议室资源 - 副本");
File[] files = dir.listFiles();
for (File file : files) {
if (!file.exists()) {
throw new Exception("文件不存在!");
}
if(file.getPath().contains("$")){
continue;
}
InputStream in = new FileInputStream(file);
if(file.getPath().endsWith(".xlsx")){
// 读取整个Excel
XSSFWorkbook sheets = new XSSFWorkbook(in);
// 获取第一个表单Sheet
XSSFSheet sheetAt = sheets.getSheetAt(0);
for (int i = 0; i < 503; i++) {
String baozhangdanwei = sheetAt.getRow(i).getCell(0).getStringCellValue().replaceAll("\s*", "");
String bangongqu = sheetAt.getRow(i).getCell(1).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(2).setCellType(CellType.STRING);
String resName = sheetAt.getRow(i).getCell(2).getStringCellValue().replaceAll("\s*", "");
String location = sheetAt.getRow(i).getCell(3).getStringCellValue().replaceAll("\s*", "");
String smallType = sheetAt.getRow(i).getCell(4).getStringCellValue().replaceAll("\s*", "");
Object remark;
if(sheetAt.getRow(i).getCell(5).getCellType().equals(CellType.NUMERIC)){
sheetAt.getRow(i).getCell(5).setCellType(CellType.NUMERIC);
remark = sheetAt.getRow(i).getCell(5).getNumericCellValue();
}else {
sheetAt.getRow(i).getCell(5).setCellType(CellType.STRING);
remark = sheetAt.getRow(i).getCell(5).getStringCellValue().replaceAll("\s*", "");
}
String fuzerenName1 = sheetAt.getRow(i).getCell(6).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(7).setCellType(CellType.STRING);
String fuzerenphone1 = sheetAt.getRow(i).getCell(7).getStringCellValue().replaceAll("\s*", "");
if(!"".equals(fuzerenName1)){
System.out.println(baozhangdanwei+"【"+bangongqu+"【"+resName+"【"+location+"【"+smallType+"【"+remark+"【"+fuzerenName1+"【"+fuzerenphone1);
}
String fuzerenName2 = sheetAt.getRow(i).getCell(8).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(9).setCellType(CellType.STRING);
String fuzerenphone2 = sheetAt.getRow(i).getCell(9).getStringCellValue().replaceAll("\s*", "");
if(!"".equals(fuzerenName2)){
System.out.println(baozhangdanwei+"【"+bangongqu+"【"+resName+"【"+location+"【"+smallType+"【"+remark+"【"+fuzerenName2+"【"+fuzerenphone2);
}
String fuzerenName3 = sheetAt.getRow(i).getCell(10).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(11).setCellType(CellType.STRING);
String fuzerenphone3 = sheetAt.getRow(i).getCell(11).getStringCellValue().replaceAll("\s*", "");
if(!"".equals(fuzerenName3)){
System.out.println(baozhangdanwei+"【"+bangongqu+"【"+resName+"【"+location+"【"+smallType+"【"+remark+"【"+fuzerenName3+"【"+fuzerenphone3);
}
String fuzerenName4 = sheetAt.getRow(i).getCell(12).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(13).setCellType(CellType.STRING);
String fuzerenphone4 = sheetAt.getRow(i).getCell(13).getStringCellValue().replaceAll("\s*", "");
if(!"".equals(fuzerenName4)){
System.out.println(baozhangdanwei+"【"+bangongqu+"【"+resName+"【"+location+"【"+smallType+"【"+remark+"【"+fuzerenName4+"【"+fuzerenphone4);
}
}
}else{
// 读取整个Excel
HSSFWorkbook sheets = new HSSFWorkbook(in);
// 获取第一个表单Sheet
HSSFSheet sheetAt = sheets.getSheetAt(0);
// 获取B23位置的填表单位
String tianbiaodanwei = sheetAt.getRow(22).getCell(1).getStringCellValue().replaceAll("\s*", "");
// 获取D2位置的负责人
String fuzeren =
sheetAt.getRow(1).getCell(3).getStringCellValue().replaceAll("\s*", "");
String[] fs = fuzeren.split("负责人");
for (String f : fs) {
if("".equals(f)){continue;}
System.out.println("【"+tianbiaodanwei+"【"+f+"【");
}
}
}
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
/**
* 考虑到xls和xlsx兼容性的
*/
public class ReadExceljianrong {
public static void main(String[] args) throws Exception {
excel();
}
public static void excel() throws Exception {
File dir = new File("F:\myWorkFiles\XXXXXXX\2022年10月8日，导入数据\数据\临时\会议室资源 - 副本");
File[] files = dir.listFiles();
for (File file : files) {
if (!file.exists()) {
throw new Exception("文件不存在!");
}
if(file.getPath().contains("$")){
continue;
}
InputStream in = new FileInputStream(file);
if(file.getPath().endsWith(".xlsx")){
// 读取整个Excel
XSSFWorkbook sheets = new XSSFWorkbook(in);
// 获取第一个表单Sheet
XSSFSheet sheetAt = sheets.getSheetAt(0);
for (int i = 0; i < 503; i++) {
String baozhangdanwei = sheetAt.getRow(i).getCell(0).getStringCellValue().replaceAll("\s*", "");
String bangongqu = sheetAt.getRow(i).getCell(1).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(2).setCellType(CellType.STRING);
String resName = sheetAt.getRow(i).getCell(2).getStringCellValue().replaceAll("\s*", "");
String location = sheetAt.getRow(i).getCell(3).getStringCellValue().replaceAll("\s*", "");
String smallType = sheetAt.getRow(i).getCell(4).getStringCellValue().replaceAll("\s*", "");
Object remark;
if(sheetAt.getRow(i).getCell(5).getCellType().equals(CellType.NUMERIC)){
sheetAt.getRow(i).getCell(5).setCellType(CellType.NUMERIC);
remark = sheetAt.getRow(i).getCell(5).getNumericCellValue();
}else {
sheetAt.getRow(i).getCell(5).setCellType(CellType.STRING);
remark = sheetAt.getRow(i).getCell(5).getStringCellValue().replaceAll("\s*", "");
}
String fuzerenName1 = sheetAt.getRow(i).getCell(6).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(7).setCellType(CellType.STRING);
String fuzerenphone1 = sheetAt.getRow(i).getCell(7).getStringCellValue().replaceAll("\s*", "");
if(!"".equals(fuzerenName1)){
System.out.println(baozhangdanwei+"【"+bangongqu+"【"+resName+"【"+location+"【"+smallType+"【"+remark+"【"+fuzerenName1+"【"+fuzerenphone1);
}
String fuzerenName2 = sheetAt.getRow(i).getCell(8).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(9).setCellType(CellType.STRING);
String fuzerenphone2 = sheetAt.getRow(i).getCell(9).getStringCellValue().replaceAll("\s*", "");
if(!"".equals(fuzerenName2)){
System.out.println(baozhangdanwei+"【"+bangongqu+"【"+resName+"【"+location+"【"+smallType+"【"+remark+"【"+fuzerenName2+"【"+fuzerenphone2);
}
String fuzerenName3 = sheetAt.getRow(i).getCell(10).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(11).setCellType(CellType.STRING);
String fuzerenphone3 = sheetAt.getRow(i).getCell(11).getStringCellValue().replaceAll("\s*", "");
if(!"".equals(fuzerenName3)){
System.out.println(baozhangdanwei+"【"+bangongqu+"【"+resName+"【"+location+"【"+smallType+"【"+remark+"【"+fuzerenName3+"【"+fuzerenphone3);
}
String fuzerenName4 = sheetAt.getRow(i).getCell(12).getStringCellValue().replaceAll("\s*", "");
sheetAt.getRow(i).getCell(13).setCellType(CellType.STRING);
String fuzerenphone4 = sheetAt.getRow(i).getCell(13).getStringCellValue().replaceAll("\s*", "");
if(!"".equals(fuzerenName4)){
System.out.println(baozhangdanwei+"【"+bangongqu+"【"+resName+"【"+location+"【"+smallType+"【"+remark+"【"+fuzerenName4+"【"+fuzerenphone4);
}
}
}else{
// 读取整个Excel
HSSFWorkbook sheets = new HSSFWorkbook(in);
// 获取第一个表单Sheet
HSSFSheet sheetAt = sheets.getSheetAt(0);
// 获取B23位置的填表单位
String tianbiaodanwei = sheetAt.getRow(22).getCell(1).getStringCellValue().replaceAll("\s*", "");
// 获取D2位置的负责人
String fuzeren =
sheetAt.getRow(1).getCell(3).getStringCellValue().replaceAll("\s*", "");
String[] fs = fuzeren.split("负责人");
for (String f : fs) {
if("".equals(f)){continue;}
System.out.println("【"+tianbiaodanwei+"【"+f+"【");
}
}
}
}
}

复制代码

import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;
import java.util.List;
/**
* 读取world中的表格
*/
public class Word {
public static void main(String[] args) {
testWord("");
}
public static void testWord(String filePath){
try{
File dir = new File("F:\myWorkFiles\XXXXXXX\2022年10月8日，导入数据\数据\临时\环境 - 副本");
File[] files = dir.listFiles();
for (File file : files) {
if(file.getPath().toString().contains("$")){
continue;
}
FileInputStream in = new FileInputStream(file.getPath());
//载入文档 如果是office2007
docx格式
if(file.getPath().toLowerCase().endsWith("docx")){
//word 2007 图片不会被读取， 表格中的数据会被放在字符串的最后
//得到word文档的信息
XWPFDocument xwpf = new XWPFDocument(in);
//得到段落信息
//
List<XWPFParagraph> listParagraphs = xwpf.getParagraphs();
//得到word中的表格
Iterator<XWPFTable> it = xwpf.getTablesIterator();
while(it.hasNext()){
XWPFTable table = it.next();
List<XWPFTableRow> rows=table.getRows();
//读取每一行数据
for (int i = 1; i < rows.size(); i++) {
XWPFTableRow
row = rows.get(i);
//读取每一列数据
List<XWPFTableCell> cells = row.getTableCells();
String s = "";
for (int j = 0; j < cells.size(); j++) {
if(j ==1 || j== 4 || j == 5){
XWPFTableCell cell=cells.get(j);
s += cell.getText() + "#";
}
}
//输出当前的单元格的数据
System.out.println(s);
}
}
}
}
}catch(Exception e) {
e.printStackTrace();
}
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;
import java.util.List;
/**
* 读取world中的表格
*/
public class Word {
public static void main(String[] args) {
testWord("");
}
public static void testWord(String filePath){
try{
File dir = new File("F:\myWorkFiles\XXXXXXX\2022年10月8日，导入数据\数据\临时\环境 - 副本");
File[] files = dir.listFiles();
for (File file : files) {
if(file.getPath().toString().contains("$")){
continue;
}
FileInputStream in = new FileInputStream(file.getPath());
//载入文档 如果是office2007
docx格式
if(file.getPath().toLowerCase().endsWith("docx")){
//word 2007 图片不会被读取， 表格中的数据会被放在字符串的最后
//得到word文档的信息
XWPFDocument xwpf = new XWPFDocument(in);
//得到段落信息
//
List<XWPFParagraph> listParagraphs = xwpf.getParagraphs();
//得到word中的表格
Iterator<XWPFTable> it = xwpf.getTablesIterator();
while(it.hasNext()){
XWPFTable table = it.next();
List<XWPFTableRow> rows=table.getRows();
//读取每一行数据
for (int i = 1; i < rows.size(); i++) {
XWPFTableRow
row = rows.get(i);
//读取每一列数据
List<XWPFTableCell> cells = row.getTableCells();
String s = "";
for (int j = 0; j < cells.size(); j++) {
if(j ==1 || j== 4 || j == 5){
XWPFTableCell cell=cells.get(j);
s += cell.getText() + "#";
}
}
//输出当前的单元格的数据
System.out.println(s);
}
}
}
}
}catch(Exception e) {
e.printStackTrace();
}
}
}