pom
读写excel主要的dependency：
<!-- 引入poi，解析workbook视图 -->
<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.14</version>
</dependency>
<!-- 处理excel和上面功能是一样的-->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.10</version>
</dependency>json格式读写使用fastjson
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.47</version>
</dependency>完整的pom.xml文件,这里使用的是springboot整合
<?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 https://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.2.1.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>test</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
<lombok.version>1.18.4</lombok.version>
<druid.version>1.1.13</druid.version>
<mybatisplus.version>3.0.7.1</mybatisplus.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
<version>${mybatisplus.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatisplus.version}</version>
<exclusions>
<exclusion>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpclient</artifactId>
<version>4.3.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.47</version>
</dependency>
<!-- 引入poi，解析workbook视图 -->
<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.14</version>
</dependency>
<!-- 处理excel和上面功能是一样的-->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.10</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
<version>${lombok.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
在test测试代码中写入excel，将20行20列写入xls中
@Test
void contextLoads2(){
/**
* 写入Excel
*/
//创建工作簿
HSSFWorkbook hssfWorkbook=new HSSFWorkbook();
//创建工作表
HSSFSheet hssfSheet=hssfWorkbook.createSheet("test1");
for(int row=0;row<20;row++){
//创建行
HSSFRow hssfRow=hssfSheet.createRow(row);
for(int col=0;col<20;col++){
//写入单元格
if(row==0){
hssfRow.createCell(col).setCellValue("col"+col);
}else {
hssfRow.createCell(col).setCellValue(row+""+col);
}
}
}
File file=new File("d://Excel.xls");
FileOutputStream fileOutputStream;
try {
fileOutputStream = new FileOutputStream(file);
hssfWorkbook.write(fileOutputStream);
fileOutputStream.close();
}catch (IOException E){
E.printStackTrace();
}
}打开excel
在test测试代码中读取刚刚写入的excel的表格并转成json
@Test
void contextLoads3(){
/**
*读取Excel
* */
File file=new File("d://Excel.xls");
//创建工作簿工作空间
Workbook workbook;
try {
workbook= WorkbookFactory.create(file);
Sheet sheet= workbook.getSheet("test1");
//System.out.println("sheet.getLastRowNum():"+sheet.getLastRowNum());
//System.out.println("sheet.getFirstRowNum():"+sheet.getFirstRowNum());
//List<JSONObject> jsonObjectList=new ArrayList<>();
JSONArray jsonObjectList=new JSONArray();
//先获取到最后一行的行数,因为行数从0开始所以再加1
for(int row=0;row<sheet.getLastRowNum()+1;row++){
Row sheetrow=sheet.getRow(row);
Row firstrow=sheet.getRow(0);
//取得第一行里的单元格数量，即列数
int cols=firstrow.getPhysicalNumberOfCells();
for (int col=0;col<cols;col++){
JSONObject jsonObject=new JSONObject();
String key=firstrow.getCell(col).getStringCellValue();
if(row!=0){
String value=sheetrow.getCell(col).getStringCellValue();
jsonObject.put(key,value);
jsonObjectList.add(jsonObject);
}
//System.out.print(sheetrow.getCell(col).getStringCellValue()+" ");
}
//System.out.println("");
}
//格式化输出
String jsonObjectList_result= JSONArray.toJSONString(jsonObjectList, SerializerFeature.PrettyFormat,SerializerFeature.WriteMapNullValue,SerializerFeature.WriteDateUseDateFormat);
System.out.println(jsonObjectList_result);
}catch (IOException E){
E.printStackTrace();
}catch (InvalidFormatException e){
e.printStackTrace();
}
}读取转成json结果：
[
{
"col0":"10"
},
{
"col1":"11"
},
{
"col2":"12"
},
{
"col3":"13"
},
{
"col4":"14"
},
{
"col5":"15"
},
{
"col6":"16"
},
{
"col7":"17"
},
{
"col8":"18"
},
{
"col9":"19"
},
{
"col10":"110"
},
{
"col11":"111"
},
{
"col12":"112"
},
{
"col13":"113"
},
{
"col14":"114"
},
{
"col15":"115"
},
{
"col16":"116"
},
{
"col17":"117"
},
{
"col18":"118"
},
{
"col19":"119"
},
{
"col0":"20"
},
{
"col1":"21"
},
{
"col2":"22"
},
{
"col3":"23"
},
{
"col4":"24"
},
{
"col5":"25"
},
{
"col6":"26"
},
{
"col7":"27"
},
{
"col8":"28"
},
{
"col9":"29"
},
{
"col10":"210"
},
{
"col11":"211"
},
{
"col12":"212"
},
{
"col13":"213"
},
{
"col14":"214"
},
{
"col15":"215"
},
{
"col16":"216"
},
{
"col17":"217"
},
{
"col18":"218"
},
{
"col19":"219"
},
{
"col0":"30"
},
{
"col1":"31"
},
{
"col2":"32"
},
{
"col3":"33"
},
{
"col4":"34"
},
{
"col5":"35"
},
{
"col6":"36"
},
{
"col7":"37"
},
{
"col8":"38"
},
{
"col9":"39"
},
{
"col10":"310"
},
{
"col11":"311"
},
{
"col12":"312"
},
{
"col13":"313"
},
{
"col14":"314"
},
{
"col15":"315"
},
{
"col16":"316"
},
{
"col17":"317"
},
{
"col18":"318"
},
{
"col19":"319"
},
{
"col0":"40"
},
{
"col1":"41"
},
{
"col2":"42"
},
{
"col3":"43"
},
{
"col4":"44"
},
{
"col5":"45"
},
{
"col6":"46"
},
{
"col7":"47"
},
{
"col8":"48"
},
{
"col9":"49"
},
{
"col10":"410"
},
{
"col11":"411"
},
{
"col12":"412"
},
{
"col13":"413"
},
{
"col14":"414"
},
{
"col15":"415"
},
{
"col16":"416"
},
{
"col17":"417"
},
{
"col18":"418"
},
{
"col19":"419"
},
{
"col0":"50"
},
{
"col1":"51"
},
{
"col2":"52"
},
{
"col3":"53"
},
{
"col4":"54"
},
{
"col5":"55"
},
{
"col6":"56"
},
{
"col7":"57"
},
{
"col8":"58"
},
{
"col9":"59"
},
{
"col10":"510"
},
{
"col11":"511"
},
{
"col12":"512"
},
{
"col13":"513"
},
{
"col14":"514"
},
{
"col15":"515"
},
{
"col16":"516"
},
{
"col17":"517"
},
{
"col18":"518"
},
{
"col19":"519"
},
{
"col0":"60"
},
{
"col1":"61"
},
{
"col2":"62"
},
{
"col3":"63"
},
{
"col4":"64"
},
{
"col5":"65"
},
{
"col6":"66"
},
{
"col7":"67"
},
{
"col8":"68"
},
{
"col9":"69"
},
{
"col10":"610"
},
{
"col11":"611"
},
{
"col12":"612"
},
{
"col13":"613"
},
{
"col14":"614"
},
{
"col15":"615"
},
{
"col16":"616"
},
{
"col17":"617"
},
{
"col18":"618"
},
{
"col19":"619"
},
{
"col0":"70"
},
{
"col1":"71"
},
{
"col2":"72"
},
{
"col3":"73"
},
{
"col4":"74"
},
{
"col5":"75"
},
{
"col6":"76"
},
{
"col7":"77"
},
{
"col8":"78"
},
{
"col9":"79"
},
{
"col10":"710"
},
{
"col11":"711"
},
{
"col12":"712"
},
{
"col13":"713"
},
{
"col14":"714"
},
{
"col15":"715"
},
{
"col16":"716"
},
{
"col17":"717"
},
{
"col18":"718"
},
{
"col19":"719"
},
{
"col0":"80"
},
{
"col1":"81"
},
{
"col2":"82"
},
{
"col3":"83"
},
{
"col4":"84"
},
{
"col5":"85"
},
{
"col6":"86"
},
{
"col7":"87"
},
{
"col8":"88"
},
{
"col9":"89"
},
{
"col10":"810"
},
{
"col11":"811"
},
{
"col12":"812"
},
{
"col13":"813"
},
{
"col14":"814"
},
{
"col15":"815"
},
{
"col16":"816"
},
{
"col17":"817"
},
{
"col18":"818"
},
{
"col19":"819"
},
{
"col0":"90"
},
{
"col1":"91"
},
{
"col2":"92"
},
{
"col3":"93"
},
{
"col4":"94"
},
{
"col5":"95"
},
{
"col6":"96"
},
{
"col7":"97"
},
{
"col8":"98"
},
{
"col9":"99"
},
{
"col10":"910"
},
{
"col11":"911"
},
{
"col12":"912"
},
{
"col13":"913"
},
{
"col14":"914"
},
{
"col15":"915"
},
{
"col16":"916"
},
{
"col17":"917"
},
{
"col18":"918"
},
{
"col19":"919"
},
{
"col0":"100"
},
{
"col1":"101"
},
{
"col2":"102"
},
{
"col3":"103"
},
{
"col4":"104"
},
{
"col5":"105"
},
{
"col6":"106"
},
{
"col7":"107"
},
{
"col8":"108"
},
{
"col9":"109"
},
{
"col10":"1010"
},
{
"col11":"1011"
},
{
"col12":"1012"
},
{
"col13":"1013"
},
{
"col14":"1014"
},
{
"col15":"1015"
},
{
"col16":"1016"
},
{
"col17":"1017"
},
{
"col18":"1018"
},
{
"col19":"1019"
},
{
"col0":"110"
},
{
"col1":"111"
},
{
"col2":"112"
},
{
"col3":"113"
},
{
"col4":"114"
},
{
"col5":"115"
},
{
"col6":"116"
},
{
"col7":"117"
},
{
"col8":"118"
},
{
"col9":"119"
},
{
"col10":"1110"
},
{
"col11":"1111"
},
{
"col12":"1112"
},
{
"col13":"1113"
},
{
"col14":"1114"
},
{
"col15":"1115"
},
{
"col16":"1116"
},
{
"col17":"1117"
},
{
"col18":"1118"
},
{
"col19":"1119"
},
{
"col0":"120"
},
{
"col1":"121"
},
{
"col2":"122"
},
{
"col3":"123"
},
{
"col4":"124"
},
{
"col5":"125"
},
{
"col6":"126"
},
{
"col7":"127"
},
{
"col8":"128"
},
{
"col9":"129"
},
{
"col10":"1210"
},
{
"col11":"1211"
},
{
"col12":"1212"
},
{
"col13":"1213"
},
{
"col14":"1214"
},
{
"col15":"1215"
},
{
"col16":"1216"
},
{
"col17":"1217"
},
{
"col18":"1218"
},
{
"col19":"1219"
},
{
"col0":"130"
},
{
"col1":"131"
},
{
"col2":"132"
},
{
"col3":"133"
},
{
"col4":"134"
},
{
"col5":"135"
},
{
"col6":"136"
},
{
"col7":"137"
},
{
"col8":"138"
},
{
"col9":"139"
},
{
"col10":"1310"
},
{
"col11":"1311"
},
{
"col12":"1312"
},
{
"col13":"1313"
},
{
"col14":"1314"
},
{
"col15":"1315"
},
{
"col16":"1316"
},
{
"col17":"1317"
},
{
"col18":"1318"
},
{
"col19":"1319"
},
{
"col0":"140"
},
{
"col1":"141"
},
{
"col2":"142"
},
{
"col3":"143"
},
{
"col4":"144"
},
{
"col5":"145"
},
{
"col6":"146"
},
{
"col7":"147"
},
{
"col8":"148"
},
{
"col9":"149"
},
{
"col10":"1410"
},
{
"col11":"1411"
},
{
"col12":"1412"
},
{
"col13":"1413"
},
{
"col14":"1414"
},
{
"col15":"1415"
},
{
"col16":"1416"
},
{
"col17":"1417"
},
{
"col18":"1418"
},
{
"col19":"1419"
},
{
"col0":"150"
},
{
"col1":"151"
},
{
"col2":"152"
},
{
"col3":"153"
},
{
"col4":"154"
},
{
"col5":"155"
},
{
"col6":"156"
},
{
"col7":"157"
},
{
"col8":"158"
},
{
"col9":"159"
},
{
"col10":"1510"
},
{
"col11":"1511"
},
{
"col12":"1512"
},
{
"col13":"1513"
},
{
"col14":"1514"
},
{
"col15":"1515"
},
{
"col16":"1516"
},
{
"col17":"1517"
},
{
"col18":"1518"
},
{
"col19":"1519"
},
{
"col0":"160"
},
{
"col1":"161"
},
{
"col2":"162"
},
{
"col3":"163"
},
{
"col4":"164"
},
{
"col5":"165"
},
{
"col6":"166"
},
{
"col7":"167"
},
{
"col8":"168"
},
{
"col9":"169"
},
{
"col10":"1610"
},
{
"col11":"1611"
},
{
"col12":"1612"
},
{
"col13":"1613"
},
{
"col14":"1614"
},
{
"col15":"1615"
},
{
"col16":"1616"
},
{
"col17":"1617"
},
{
"col18":"1618"
},
{
"col19":"1619"
},
{
"col0":"170"
},
{
"col1":"171"
},
{
"col2":"172"
},
{
"col3":"173"
},
{
"col4":"174"
},
{
"col5":"175"
},
{
"col6":"176"
},
{
"col7":"177"
},
{
"col8":"178"
},
{
"col9":"179"
},
{
"col10":"1710"
},
{
"col11":"1711"
},
{
"col12":"1712"
},
{
"col13":"1713"
},
{
"col14":"1714"
},
{
"col15":"1715"
},
{
"col16":"1716"
},
{
"col17":"1717"
},
{
"col18":"1718"
},
{
"col19":"1719"
},
{
"col0":"180"
},
{
"col1":"181"
},
{
"col2":"182"
},
{
"col3":"183"
},
{
"col4":"184"
},
{
"col5":"185"
},
{
"col6":"186"
},
{
"col7":"187"
},
{
"col8":"188"
},
{
"col9":"189"
},
{
"col10":"1810"
},
{
"col11":"1811"
},
{
"col12":"1812"
},
{
"col13":"1813"
},
{
"col14":"1814"
},
{
"col15":"1815"
},
{
"col16":"1816"
},
{
"col17":"1817"
},
{
"col18":"1818"
},
{
"col19":"1819"
},
{
"col0":"190"
},
{
"col1":"191"
},
{
"col2":"192"
},
{
"col3":"193"
},
{
"col4":"194"
},
{
"col5":"195"
},
{
"col6":"196"
},
{
"col7":"197"
},
{
"col8":"198"
},
{
"col9":"199"
},
{
"col10":"1910"
},
{
"col11":"1911"
},
{
"col12":"1912"
},
{
"col13":"1913"
},
{
"col14":"1914"
},
{
"col15":"1915"
},
{
"col16":"1916"
},
{
"col17":"1917"
},
{
"col18":"1918"
},
{
"col19":"1919"
}
]将格式化的json写入json文件中存入磁盘
@Test
void contextLoads3(){
/**
*读取Excel
* */
File file=new File("d://Excel.xls");
//创建工作簿工作空间
Workbook workbook;
try {
workbook= WorkbookFactory.create(file);
Sheet sheet= workbook.getSheet("test1");
//System.out.println("sheet.getLastRowNum():"+sheet.getLastRowNum());
//System.out.println("sheet.getFirstRowNum():"+sheet.getFirstRowNum());
//List<JSONObject> jsonObjectList=new ArrayList<>();
JSONArray jsonObjectList=new JSONArray();
//先获取到最后一行的行数,因为行数从0开始所以再加1
for(int row=0;row<sheet.getLastRowNum()+1;row++){
Row sheetrow=sheet.getRow(row);
Row firstrow=sheet.getRow(0);
//取得第一行里的单元格数量，即列数
int cols=firstrow.getPhysicalNumberOfCells();
for (int col=0;col<cols;col++){
JSONObject jsonObject=new JSONObject();
String key=firstrow.getCell(col).getStringCellValue();
if(row!=0){
String value=sheetrow.getCell(col).getStringCellValue();
jsonObject.put(key,value);
jsonObjectList.add(jsonObject);
}
//System.out.print(sheetrow.getCell(col).getStringCellValue()+" ");
}
//System.out.println("");
}
//格式化输出
String jsonObjectList_result= JSONArray.toJSONString(jsonObjectList, SerializerFeature.PrettyFormat,SerializerFeature.WriteMapNullValue,SerializerFeature.WriteDateUseDateFormat);
System.out.println(jsonObjectList_result);
File json_file=new File("d://json1.json");
FileOutputStream fileOutputStream=new FileOutputStream(json_file);
//第一种
//fileOutputStream.write(jsonObjectList_result.getBytes());
//第二种
OutputStreamWriter outputStreamWriter=new OutputStreamWriter(fileOutputStream,"UTF-8");
outputStreamWriter.write(jsonObjectList_result);
//fileOutputStream.close();
outputStreamWriter.close();
}catch (IOException E){
E.printStackTrace();
}catch (InvalidFormatException e){
e.printStackTrace();
}
}写入json文件的结果
4555
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
