Excel文件合并

毕业论文数据操作

创建maven

1、file新建文件，命名pom.xml
2、点击pom.xml文件上，右键 Add as Maven Project
3、复制下面代码，修改一下artifactId为自己的项目名`

<?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>
    <artifactId>改为自己的项目名</artifactId>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.5.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <!-- Lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

4、点击右侧maven工具栏刷新完成创建

修改细节

1、字符串去除空格
去除收尾空格

String str=" hsm "
System.out.println(str.trim());

去除所有的空格

String str = "Hello hsm  ";
String str2 = str.replaceAll(" ","");
System.out.println(str2);

2、实现小demo-将2000-2009年的10张表合并为一张

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

import jxl.read.biff.BiffException;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;

/**
 * @author hsm
 * @data 2022-02-19 19:38
 * 操作excel
 */
public class MergeExcel {
    private static POIFSFileSystem fs;
    private static HSSFWorkbook wb;
    private static HSSFSheet sheet;
    private static HSSFRow row;
    static List<Map<String, Object>> list = new ArrayList();
    public static void main(String[] args) throws IOException {

        // 设置标题,即表格第一行,存入集合
        Map<String, Object> map = new HashMap<>();
        map.put("sq", "县");
        map.put("nf", "年份");
        map.put("xt", "小学教师数量");
        map.put("zt", "中学教师数量");
        map.put("tn", "教师数量");
        map.put("xs", "小学学生数量");
        map.put("zs", "中学学生数量");
        map.put("sn", "学生数量");
        list.add(map);
        // 本地存放excel文件的包路径
        String path = "C:\\Users\\12090\\Desktop\\09";
        // 读取包下所有excel数据传到集合里
        readFile(path);
        // 创建并且将集合数据写入表格
        write();
    }
    /**
     * 搜索所有的excel，并且循环调用excel数据处理方法
     *
     * @param path
     * @throws BiffException
     * @throws IOException
     */
    public static void readFile(String path) throws IOException {
        File file = new File(path);
        // 获取包下所有excel文件的名称集合
        String[] filelist = file.list();
        for (int i = 0; i < filelist.length; i++) {
            // 获取excel文件
            InputStream is = new FileInputStream(path + "\\" + filelist[i]);
            // 传行入excel文件进处理
            readExcelContent(is);
        }
    }

    /**
     * excel数据处理（读取excel数据并存入list）
     * @return
     * @return Map 包含单元格数据内容的Map对象
     * @throws IOException
     */
    public static void readExcelContent(InputStream is) throws IOException {
        fs = new POIFSFileSystem(is);
        wb = new HSSFWorkbook(fs);
        sheet = wb.getSheetAt(0);
        // excel总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        //第一行是标题，数据从第二行开始读取
        for (int i = 4; i <= rowNum; i++) {
            row = sheet.getRow(i);
            //判断是否为空行，空行跳出，删除空行
            if (row==null){
                continue;
            }
            //第一个单元格
            HSSFCell cell=row.getCell(0);
            cell.setCellType(CellType.STRING);
            //第三个单元格
            HSSFCell cell2=row.getCell(2);
            cell2.setCellType(CellType.STRING);
            //将市区大表头删去
            if("县(市、区)".equals(cell.getStringCellValue().replaceAll(" ",""))|| cell.getStringCellValue().contains("Basic")|| cell.getStringCellValue().contains("continued")||
            cell.getStringCellValue().contains("（")){
                continue;
            }
            //中间有一个为空值,跳出当前循环，删除有空的单元格的行
            if("".equals(cell.getStringCellValue())|| "".equals(cell2.getStringCellValue())){
                continue;
            }
            //每次都是存的新的map，一个map就是一行数据
            Map<String, Object> map = new HashMap<>();
            //map自定义key-value数据，存入list
            map.put("sq", row.getCell(0));
            map.put("xs", row.getCell(3));
            map.put("xt", row.getCell(4));
            map.put("zs", row.getCell(6));
            map.put("zt", row.getCell(7));
            list.add(map);
        }
    }

    /**
     * 创建表格并且数据写入表格
     * @throws IOException
     */
    public static void write() throws IOException {
        // 创建表格
        HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
        // 单元创建
        HSSFSheet hHSSFSheet = hSSFWorkbook.createSheet("数据整合");
        int year=2000;
        for (int i = 0; i < list.size(); i++) {
            HSSFRow row = hHSSFSheet.createRow(i);
            //进行表头的设置
            if (i==0){
                row.createCell(0).setCellValue(list.get(0).get("sq").toString().trim());
                row.createCell(1).setCellValue(list.get(0).get("nf").toString());
                row.createCell(2).setCellValue(list.get(0).get("xt").toString());
                row.createCell(3).setCellValue(list.get(0).get("zt").toString());
                row.createCell(4).setCellValue(list.get(0).get("tn").toString());
                row.createCell(5).setCellValue(list.get(0).get("xs").toString());
                row.createCell(6).setCellValue(list.get(0).get("zs").toString());
                row.createCell(7).setCellValue(list.get(0).get("sn").toString());
                continue;
            }
            // map自定义key-value数据插入excel
            row.createCell(0).setCellValue(list.get(i).get("sq").toString().trim());
            row.createCell(1).setCellValue(year);
            row.createCell(2).setCellValue(list.get(i).get("xt").toString().trim());
            row.createCell(3).setCellValue(list.get(i).get("zt").toString().trim());
            row.createCell(5).setCellValue(list.get(i).get("xs").toString().replaceAll(" ",""));
            row.createCell(6).setCellValue(list.get(i).get("zs").toString().trim());
            if("雷波县".equals(list.get(i).get("sq").toString().trim())){
                year=year+1;
            }
        }

        FileOutputStream file = new FileOutputStream("C:\\Users\\12090\\Desktop\\09\\00-09整合.xls");
        hSSFWorkbook.write(file);
        file.flush();
        System.out.println("hello 臭宝 done");
    }
}

效果图

将2000-2009年的10张表合并为一张