将excel表格中的数据，转换为sql语句，并存储到txt文档中。

最新推荐文章于 2024-04-26 17:00:35 发布

zcouy

最新推荐文章于 2024-04-26 17:00:35 发布

阅读量1.3k

点赞数

分类专栏： java 文章标签： excel 转换文件

本文链接：https://blog.csdn.net/zcouy/article/details/54377502

版权

java 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

前台页面：

<form action="${ctx}/test/result"  method="post" enctype="multipart/form-data">

    <input type="file" value="选择文件" name="multipartFile" id="excel"/>
    <input type="submit" class="submit" value="导出excel" />

</form>

----------

package com.capinfo.examsystem.web;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.logging.Logger;

/**
 * Created by zhuochen on 2016/10/21.
 */
@Controller
@RequestMapping("test")
public class TestController {
    private static final Logger logger = Logger.getLogger("TestController.class");
    @RequestMapping("result")
    public String testController(String path, MultipartFile multipartFile) throws IOException {

        InputStream in = multipartFile.getInputStream();
        logger.info(in+"测试样本");
       /*
         FileInputStream fileIn = new FileInputStream(*/
       /* FileInputStream fileIn = new FileInputStream("D:\\idea_workspace1\\demosign\\target\\classes\\doc\\居住证复核20161024.xls");
        InputStreamReader isr = new InputStreamReader(fileIn, "GBK");

        System.out.println("==============");*/
        Workbook wb = new HSSFWorkbook(new POIFSFileSystem(in));
        //System.out.println(wb+":wb");
        int numOfSheets = wb.getNumberOfSheets();
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        String cellValue = "";
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        Map map = null;
        File savefile = new File("C:\\Users\\zhuochen\\Desktop\\doc");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        Date date = new Date();
        String simpleDateFormat = new SimpleDateFormat("yyyy年MM月dd日mm秒").format(date);
        System.out.println(simpleDateFormat+"当前时间");
        String s = "C:\\Users\\zhuochen\\Desktop\\doc\\小客车查询数据"+simpleDateFormat+".txt";
        //System.out.println(s+"当前时间");
        FileWriter fileWrite = new FileWriter(s);
        // 循环遍历
        // System.out.println(numOfSheets+"=====");
        //for (int indexOfSheet=1; indexOfSheet < numOfSheets; indexOfSheet++) {
        System.out.println("==="+savefile);
        sheet = wb.getSheetAt(0);
        //logger.info("数据："+sheet);
        //直接从内容开始。
        for (int indexOfRowNum = sheet.getFirstRowNum(), rowNum = sheet
                .getLastRowNum(); indexOfRowNum <= rowNum; indexOfRowNum++) {

            //System.out.println("行" + indexOfRowNum);
            row = sheet.getRow(indexOfRowNum);
            int lastCellNum = (int) row.getLastCellNum();
            //System.out.println(lastCellNum + "？short数字");
            if (row != null) {
                if (row.getCell(0).toString().isEmpty()) {
                    continue;
                } else {
                    map = new LinkedHashMap<String, String>();

                    for (int indexOfCell = 0; indexOfCell < lastCellNum; indexOfCell++) {
                       // System.out.println("列" + indexOfCell);
                        cell = row.getCell(indexOfCell);
                        //logger.info("每行数据："+getCellValue(cell));
                        if (cell != null) {
                            cellValue = getCellValue(cell);
                            if (row.getCell(0).toString().equals(cellValue)) {

                                int i = cellValue.lastIndexOf(".");
                                String cellValueNum = cellValue.substring(0, i);
                                fileWrite.write("select " + "\'" + cellValueNum + "\'" + " id,");
                                map.put(indexOfCell + "", cellValue);
                            } else {
                                //System.out.println("================" + cellValue + "cellValue");
                                //我只有四列需要转换  转换后形式是“select '1' id,'xxx' name,'xxxxxxxxxx' personcode,'201203070092' cardid from dual union all”
                                switch (indexOfCell) {
                                    case 1:
                                        fileWrite.write("\'" + cellValue + "\'" + " name,");
                                        break;
                                    case 2:
                                        fileWrite.write("\'" + cellValue + "\'" + " personcode,");
                                        break;
                                    case 3:
                                        fileWrite.write("\'" + cellValue + "\'" + " cardid from dual union all");
                                        break;
                                    case 4:
                                        break;
                                }
                                map.put(indexOfCell + "", cellValue);
                            }
                        }

                    }
                    fileWrite.write("\r\n");
                    if (!map.isEmpty()) {
                        list.add(map);
                    }
                }
            }
        }
        //  }
        //  DbUtil.insertDataItem(conn, list);
        //System.out.println(list);
        // 关闭流
        fileWrite.flush();
        fileWrite.close();
        in.close();
        //fileIn.close();
        return "excelTest";
    }
    public static String getCellValue(Cell cell) {
        int cellType = cell.getCellType();
        String cellValue = "";
        switch (cellType) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;

            case HSSFCell.CELL_TYPE_FORMULA:
                try {
                    cellValue = cell.getStringCellValue();
                } catch (IllegalStateException e) {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;

            default:
                cellValue = cell.getStringCellValue();
        }

        return cellValue.trim();
    }

}