将Excel表格导入到数据库中

一. 引言

  最近的工作中接触到了将Excel表格中数据更新到数据库中，因为数据量很大，手动更新会很费时间，而且还容易出错。所以利用Java开发工具，先将Excel转换为对象再存到数据库表中。通过以往导入表格的案例，我发现基本都是按照行和列的索引来取到对应信息的，这样如果遇到另一种形式的表格就不适用了，只能修改表格索引位置，面对这个问题，我打算尝试通过表格头信息来获取对应字段的值。

二. 分析

  按普通的数据存储方式来看，除了标题以外，第一行往往代表着本列的一个类别，例如：

姓名	性别	年龄
张三	男	18
李四	女	20

  张三和李四代表着姓名，男和女表示性别，18和20 代表着年龄，首先找到表格字段属性这一行，将字段属性与数据分开单独保存，这时就已经知道了每行要遍历列的个数了。通过遍历每行，在遍历固定数量的列来获取每个单元格内的值并保存。
  获取的Excel表数据我打算存储到Map中，json数据形式为：

{
    "headData":[
        "XM",
        "XB",
        "NL"
    ],
    "bodyData":[
        {
            "XM":"张三",
            "XB":"男",
            "NL":"18"
        },
        {
            "XM":"李四",
            "XB":"女",
            "NL":"20"
        },
        ...
    ]
}

三. 保存表格头字段信息

  首先要将表格头部信息单独保存，在保存时，考虑到汉字不适合作为索引的关键字，我打算将汉字转换成首字母大写拼接的形式，例如姓名改写成XM，性别改写成XB。这里需要用到一个工具类：pinyin4j.jar

下载链接：http://central.maven.org/maven2/com/belerweb/pinyin4j/2.5.1/pinyin4j-2.5.1.jar

  pom文件：

<dependency>
    <groupId>com.belerweb</groupId>
    <artifactId>pinyin4j</artifactId>
    <version>2.5.1</version>
</dependency>

  使用方式这里我在网上找了一篇文章，我是直接调用这里的方法来进行转换

JAVA 汉子转化中文拼音链接：https://blog.csdn.net/qq_40083897/article/details/85779162

  所用到的方法为：

/**
	 * 获取汉字串拼音首字母，英文字符不变
	 * @param chinese 汉字串
	 * @return 汉语拼音首字母
	 */
public static String getFirstSpell(String chinese) {
		// 用StringBuffer（字符串缓冲）来接收处理的数据
		StringBuffer sb = new StringBuffer();
		//字符串转换为字截数组
		char[] arr = chinese.toCharArray();
		//创建转换对象
		HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
		//转换类型（大写or小写）
		defaultFormat.setCaseType(HanyuPinyinCaseType.LOWERCASE);
		//定义中文声调的输出格式
		defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
		for (int i = 0; i < arr.length; i++) {
			//判断是否是汉子字符
			if (arr[i] > 128) {
				try {
					// 提取汉字的首字母
					String[] temp = PinyinHelper.toHanyuPinyinStringArray(arr[i], defaultFormat);
					if (temp != null) {
						sb.append(temp[0].charAt(0));
					}
				} catch (BadHanyuPinyinOutputFormatCombination e) {
					e.printStackTrace();
				}
			} else {
				// 如果不是汉字字符，直接拼接
				sb.append(arr[i]);
			}
		}
		return sb.toString().replaceAll("\\W", "").trim();

  这里的转换类型我打算使用大写字母，所以将HanyuPinyinCaseType.LOWERCASE修改为HanyuPinyinCaseType.UPPERCASE
  Java操作Excel表格我使用的工具类为Apache的POI
  pom文件：

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.9</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.9</version>
        </dependency>
        

  这些最好都添加上，因为Excel的格式有.xls和.xlsx之分，有些类只支持其中一种格式导入。

四. 编写导入Excel工具类

 1. 创建工具类文件

  ExcelImportUtils：

public class ExcelImportUtils {
    private int headRowNum = 0;//表格头所在的行
    private int bodyRowNum = 0;//表格体所在的行
    private Map<String, Object> map = new HashMap<String, Object>();//封装的数据

    public ExcelImportUtils() {

    }

    public ExcelImportUtils(Integer headRowNum, Integer bodyRowNum) {
        if (headRowNum != null) this.headRowNum = headRowNum;
        if (bodyRowNum != null) this.bodyRowNum = bodyRowNum;
    }
}

  这里创建了三个属性，headRowNum代表着表格头所在的行号，bodyRowNum代表着表格第一行数据所在的行号；接着创建了两个构造器，在有参构造器中可以指定表头的表第一行数据的行号，为空时默认。

 2.创建获取表格对象方法

/**
     * 获取Excel表格页
     *
     * @param file
     * @param page
     * @return
     * @throws Exception
     */
    public Sheet getExcel(File file, int page) throws Exception {
        Workbook wb = WorkbookFactory.create(file);
        Sheet sheet = wb.getSheetAt(page);
        return sheet;
    }

  通过Workbook创建Excel文档对象获取Excel，接着获取Excel的表单。

 3. 通过表单对象获取表头数据

/**
     * 封装表格头信息
     *
     * @param sheet
     * @param rowNum
     */
    public void getHeadData(Sheet sheet, int rowNum) {
        this.headRowNum = rowNum;
        List head = new ArrayList();
        Row row = sheet.getRow(rowNum);
        if (row != null) {
            for (int i = 0; ; i++) {
                String headVal = this.getValue(row.getCell(i), "1");
                if (!"".equals(headVal)) {
                    String capitalize = ChineseToSpell.getFirstSpell(headVal);
                    head.add(capitalize);
                } else {
                    break;
                }
            }
        }
        map.put("headData", head);
    }

  根据输入的行号来遍历改行的所有不为空的字段，并转为首字母大写的值封装成headData。

 4. 通过表单对象获取表格数据

/**
     * 封装表格体信息
     *
     * @param sheet
     * @param startRow
     */
    public void getBodyData(Sheet sheet, Integer startRow) {
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        List<String> headData = (List) this.map.get("headData");
        if (startRow == null)
            startRow = this.headRowNum + 1;

        for (int i = startRow; ; i++) {
            Map<String, String> rowMap = new HashMap<String, String>();
            Row row = sheet.getRow(i);
            if (row != null) {
                int cellNum = row.getLastCellNum();

                for (int j = 0; j < cellNum; j++) {
                    String cellVal = "";
                    Cell cell = row.getCell(j);
                    if (cell == null)
                        break;
                    int cellType = cell.getCellType();//获取该列的类型
                    cellVal = this.getValue(cell, "1");//类型为字符串
                    if (j < headData.size())
                        rowMap.put(headData.get(j), cellVal);
                }
                if (rowMap.size() > 0)
                    list.add(rowMap);
            } else {
                break;
            }
        }
        map.put("bodyData", list);
    }

  从输入的表格体行号开始向下遍历，按照表格头顺序依次获取值并存储。在获取每个单元内的值时通过调用getValue方法按照不同类型获取值，当类型为“1”时表示为字符串类型，可以通过cell.getCellType()来获取当前列的类型。
  getValue方法为：

//按类型获取值
    public String getValue(Cell cell, String sformat) {
        if (cell == null)
            return "";
        String value = "";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                // 如果为时间格式的内容
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    value = sdf
                            .format(HSSFDateUtil.getJavaDate(cell
                                    .getNumericCellValue())).toString().trim();
                    break;
                } else {
                    value = new DecimalFormat(sformat).format(cell
                            .getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                value = cell.getStringCellValue().trim();
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                value = cell.getBooleanCellValue() + "";
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                value = cell.getCellFormula() + "";
                break;
            case Cell.CELL_TYPE_BLANK: // 空值
                value = "";
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                value = "非法字符";
                break;
            default:
                value = "未知类型";
                break;
        }
        return value;
    }

五. 连接数据库测试

  以上工具类写好后，下面来进行测试。
  Test.java:

//创建文件对象
File file = new File("E://01.xlsx");
//创建工具类对象
//表格头行号为0，表格数据第一行行号为1
ExcelImportUtils demoUtils = new ExcelImportUtils(0, 1);
//这里我将上面工具类的方法进行封装
//直接取到封装后的数据
Map<String, Object> map = demoUtils.getExcelData(file);
//通过存储的表格头部信息，获取表格数据
List<Map<String, String>> bodyData = (List) map.get("bodyData");
for (Map b : bodyData) {
   String xm = (String) b.get("XM");
   String xb = (String) b.get("XB");
   String nl = (String) b.get("NL");
   System.out.printf(xm + ",");
   System.out.printf(xb + ",");
   System.out.printf(nl );
}
//数据库插入语句略。。。

  到此就可以通过存储的字段名来获取表格数据的值了，显示输出的内容为：

张三,男,18
李四,女,20

六. 总结

  编写这个的目的是想不用通过坐标来获取数据，虽然在存储数据对象时还是通过遍历坐标来获取，但将表头和表数据单独存放的这种方式可以用于大多数的Excel表格，避免了重写的麻烦。这里有很多问题和不足之处我还没有发现，还请看过后加以指点。