pom
讀寫excel主要的dependency
? <dependency> <groupId>org.apache.poigroupId> <artifactId>poiartifactId> <version>3.16version> dependency> <dependency> <groupId>org.apache.poigroupId> <artifactId>poi-ooxmlartifactId> <version>3.14version> dependency> <dependency> <groupId>net.sourceforge.jexcelapigroupId> <artifactId>jxlartifactId> <version>2.6.10version>??dependency>json格式讀寫使用fastjson
<dependency> <groupId>com.alibabagroupId> <artifactId>fastjsonartifactId> <version>1.2.47version>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.0modelVersion> <parent> <groupId>org.springframework.bootgroupId> <artifactId>spring-boot-starter-parentartifactId> <version>2.2.1.RELEASEversion> <relativePath/> parent> <groupId>com.examplegroupId> <artifactId>demoartifactId> <version>0.0.1-SNAPSHOTversion> <name>testname> <description>Demo project for Spring Bootdescription> <properties> <java.version>1.8java.version> <lombok.version>1.18.4lombok.version> <druid.version>1.1.13druid.version> <mybatisplus.version>3.0.7.1mybatisplus.version> properties> <dependencies> <dependency> <groupId>org.springframework.bootgroupId> <artifactId>spring-boot-starter-webartifactId> dependency> <dependency> <groupId>org.springframework.bootgroupId> <artifactId>spring-boot-devtoolsartifactId> <scope>runtimescope> <optional>trueoptional> dependency> <dependency> <groupId>org.springframework.bootgroupId> <artifactId>spring-boot-starter-jdbcartifactId> dependency> <dependency> <groupId>com.baomidougroupId> <artifactId>mybatis-plusartifactId> <version>${mybatisplus.version}version> dependency> <dependency> <groupId>mysqlgroupId> <artifactId>mysql-connector-javaartifactId> <scope>runtimescope> dependency> <dependency> <groupId>com.baomidougroupId> <artifactId>mybatis-plus-boot-starterartifactId> <version>${mybatisplus.version}version> <exclusions> <exclusion> <groupId>com.baomidougroupId> <artifactId>mybatis-plus-generatorartifactId> exclusion> exclusions> dependency> <dependency> <groupId>org.apache.httpcomponentsgroupId> <artifactId>httpclientartifactId> <version>4.3.1version> dependency> <dependency> <groupId>com.alibabagroupId> <artifactId>druid-spring-boot-starterartifactId> <version>${druid.version}version> dependency> <dependency> <groupId>com.alibabagroupId> <artifactId>fastjsonartifactId> <version>1.2.47version> dependency> <dependency> <groupId>org.apache.poigroupId> <artifactId>poiartifactId> <version>3.16version> dependency> <dependency> <groupId>org.apache.poigroupId> <artifactId>poi-ooxmlartifactId> <version>3.14version> dependency> <dependency> <groupId>net.sourceforge.jexcelapigroupId> <artifactId>jxlartifactId> <version>2.6.10version> dependency> <dependency> <groupId>org.projectlombokgroupId> <artifactId>lombokartifactId> <optional>trueoptional> <version>${lombok.version}version> dependency> <dependency> <groupId>org.springframework.bootgroupId> <artifactId>spring-boot-starter-testartifactId> <scope>testscope> <exclusions> <exclusion> <groupId>org.junit.vintagegroupId> <artifactId>junit-vintage-engineartifactId> exclusion> exclusions> dependency> dependencies> <build> <plugins> <plugin> <groupId>org.springframework.bootgroupId> <artifactId>spring-boot-maven-pluginartifactId> plugin> plugins> build>project>Java代碼
在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(); }????}打開剛剛寫入的xls文件
在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 jsonObjectList=new ArrayList<>(); JSONArray jsonObjectList=new JSONArray(); //先獲取到最后一行的行數,因為行數從0開始所以再加1 for(int row=0;row1;row++){ Row sheetrow=sheet.getRow(row); Row firstrow=sheet.getRow(0); //取得第一行里的單元格數量，即列數 int cols=firstrow.getPhysicalNumberOfCells(); for (int col=0;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 jsonObjectList=new ArrayList<>(); JSONArray jsonObjectList=new JSONArray(); //先獲取到最后一行的行數,因為行數從0開始所以再加1 for(int row=0;row1;row++){ Row sheetrow=sheet.getRow(row); Row firstrow=sheet.getRow(0); //取得第一行里的單元格數量，即列數 int cols=firstrow.getPhysicalNumberOfCells(); for (int col=0;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文件的結果
_Visual Studio Express | Teraz Visual Studio Community)
 JSON-RPC 2.0 規范(中文版))
