csv文件怎么转成excel_Java读写excel,excel转成json写入磁盘文件

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文件的结果