java利用poi导入excel文件（xlsx)到sqlserver数据库

最新推荐文章于 2021-03-04 22:19:54 发布

qq_42678477

最新推荐文章于 2021-03-04 22:19:54 发布

阅读量1.1k

点赞数

分类专栏： SQLSERVER Java

本文链接：https://blog.csdn.net/qq_42678477/article/details/101102262

版权

SQLSERVER 同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

Java

4 篇文章 0 订阅

订阅专栏

Client.java

package cn.appkit.cpmis;

public class Client {
	public static void main(String[] args) throws Exception {
		long start = System.currentTimeMillis();

		String filename = "C:\\Users\\Administrator\\Desktop\\看板\\GT\\GT\\Config\\ImportConfig.xml";
		String tablename = "YHQXB";
		TableStruct ts = new TableStruct(filename, tablename);
		Filter filter = new Filter(filename, tablename);

		filename = "C:\\Users\\Administrator\\Desktop\\看板内容\\权限分配情况.xlsx";
		Db db = new Db(ts, filter);
		db.setSt(db.getConn().prepareStatement(ts.GenerateInsertSql()));

		ImportXlsx iy = new ImportXlsx(filename, db);
		iy.process();
		db.execute();

		long end = System.currentTimeMillis();
		System.out.println("耗时:" + (end - start) / 1000 + " 秒。");

	}
}

TableStruct.java

package cn.appkit.cpmis;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

public class TableStruct {
	private String tableName;
	List<FieldSetting> cols;

	public List<FieldSetting> getCols() {
		return cols;
	}

	public void setCols(List<FieldSetting> cols) {
		this.cols = cols;
	}

	@Override
	public String toString() {
		List<String> ls = new ArrayList<String>();
		for (int i = 0; i < this.cols.size(); i++) {
			ls.add(this.cols.get(i).toString());
		}
		return "TableStruct [cols=\r\n" + String.join("\r\n", ls.toArray(new String[0])) + "\r\n] \r\n"
				+ this.GenerateInsertSql();
	}

	public TableStruct(String configFilename, String tableName) throws Exception {
		this.tableName = tableName;
		cols = new ArrayList<FieldSetting>();

		DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
		DocumentBuilder db = dbf.newDocumentBuilder();
		File f = new File(configFilename);
		Document doc = db.parse(f);

		XPathFactory factory = XPathFactory.newInstance();
		XPath xpath = factory.newXPath();

		String expression;
		Node node;
		NodeList nodeList;

		expression = "/configs/config[@tableName='" + this.tableName + "']/columnSettings/columnSetting";
		nodeList = (NodeList) xpath.evaluate(expression, doc, XPathConstants.NODESET);
		if (nodeList.getLength() > 0) {
			cols.add(new FieldSetting(-1, "OBJECT_ID", "字符串", "标识符", 0));
		}
		for (int i = 0; i < nodeList.getLength(); i++) {
			Element ele = (Element) nodeList.item(i);
			String str = ele.getAttribute("content");
			String[] strs = str.split(",");
			if (!strs[2].equalsIgnoreCase("-"))
				cols.add(new FieldSetting(this.letterToNumber(strs[2]) - 1, strs[0], strs[1], strs[5],
						Integer.parseInt(strs[4])));
			// System.out.println(str + " " + strs.length);
		}

		// System.out.println(this);
	}

	// 字母转数字 A-Z ：1-26
	public int letterToNumber(String letter) {
		int length = letter.length();
		int num = 0;
		int number = 0;
		for (int i = 0; i < length; i++) {
			char ch = letter.charAt(length - i - 1);
			num = (int) (ch - 'A' + 1);
			num *= Math.pow(26, i);
			number += num;
		}
		return number;
	}

	// 数字转字母 1-26 ： A-Z
	private String numberToLetter(int num) {
		if (num <= 0) {
			return null;
		}
		String letter = "";
		num--;
		do {
			if (letter.length() > 0) {
				num--;
			}
			letter = ((char) (num % 26 + (int) 'A')) + letter;
			num = (int) ((num - num % 26) / 26);
		} while (num > 0);

		return letter;
	}

	public String GenerateInsertSql() {
		List<String> m_fields = new ArrayList<String>();
		List<String> m_values = new ArrayList<String>();
		for (int i = 0; i < this.cols.size(); i++) {
			m_fields.add(this.cols.get(i).getFieldName());
			m_values.add("?");
		}

		return String.format("Insert into %s (%s) values (%s)", this.tableName,
				String.join(",", m_fields.toArray(new String[0])), String.join(",", m_values.toArray(new String[0])));
	}

}

Filter.java

package cn.appkit.cpmis;

import java.io.File;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

public class Filter {
	private int startLine = -1;
	private int keyColumn = -1;
	private int noColumn = -1;
	private int onlyColumn = -1;
	private String noColumnValue;
	private String noColumnReg;
	private String onlyColumnValue;
	private String onlyColumnReg;
	private String tableName;

	public Filter(String configFilename, String tableName) throws Exception {
		this.tableName = tableName;

		DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
		DocumentBuilder db = dbf.newDocumentBuilder();
		File f = new File(configFilename);
		Document doc = db.parse(f);

		XPathFactory factory = XPathFactory.newInstance();
		XPath xpath = factory.newXPath();

		String expression;
		Node node;
		NodeList nodeList;

		expression = "/configs/config[@tableName='" + this.tableName + "']/contentConfig";
		node = (Node) xpath.evaluate(expression, doc, XPathConstants.NODE);
		if (node == null)
			return;
		Element ele = (Element) node;

		String strStartLine = ele.getAttribute("startLine");
		if (!"".equals(strStartLine))
			this.startLine = Integer.parseInt(strStartLine);

		String strKeyColumn = ele.getAttribute("keyColumn");
		if (!"".equals(strKeyColumn))
			this.keyColumn = Integer.parseInt(strKeyColumn);

		String strNoColumn = ele.getAttribute("noColumn");
		if (!"".equals(strNoColumn))
			this.noColumn = Integer.parseInt(strNoColumn);

		String strOnlyColumn = ele.getAttribute("onlyColumn");
		if (!"".equals(strOnlyColumn))
			this.onlyColumn = Integer.parseInt(strOnlyColumn);

		String strNoColumnValue = ele.getAttribute("noColumnValue");
		if (!"".equals(strNoColumnValue))
			this.noColumnValue = strNoColumnValue;

		String strNoColumnReg = ele.getAttribute("noColumnReg");
		if (!"".equals(strNoColumnReg))
			this.noColumnReg = strNoColumnReg;

		String strOnlyColumnValue = ele.getAttribute("onlyColumnValue");
		if (!"".equals(strOnlyColumnValue))
			this.onlyColumnValue = strOnlyColumnValue;

		String strOnlyColumnReg = ele.getAttribute("onlyColumnReg");
		if (!"".equals(strOnlyColumnReg))
			this.onlyColumnReg = strOnlyColumnReg;

		System.out.println(this);

	}

	@Override
	public String toString() {
		return "Filter [startLine=" + startLine + ", keyColumn=" + keyColumn + ", noColumn=" + noColumn
				+ ", onlyColumn=" + onlyColumn + ", noColumnValue=" + noColumnValue + ", noColumnReg=" + noColumnReg
				+ ", onlyColumnValue=" + onlyColumnValue + ", onlyColumnReg=" + onlyColumnReg + ", tableName="
				+ tableName + "]";
	}

	public boolean doFilter(Line line) {
		// 开始行
		if (line.getNum() < (startLine - 1))
			return false;

		// 关键列
		if (-1 != this.keyColumn) {
			// 关键列不存在
			if (!line.getCells().containsKey(this.keyColumn))
				return false;

			// 关键列为null或空串
			if (null == line.getCells().get(this.keyColumn) || line.getCells().get(this.keyColumn).equals(""))
				return false;
		}

		// noColumn
		if (-1 != this.noColumn) {
			// noColumn不存在
			if (!line.getCells().containsKey(this.noColumn))
				return false;

			if (null != this.noColumnValue && line.getCells().get(this.noColumn).equals(this.noColumnValue))
				return false;

			if (null != this.noColumnReg && line.getCells().get(this.noColumn).matches(this.noColumnReg))
				return false;
		}

		// only Column
		if (-1 != this.onlyColumn) {
			// noColumn不存在
			if (!line.getCells().containsKey(this.onlyColumn))
				return false;

			if (null != this.onlyColumnValue && !line.getCells().get(this.onlyColumn).equals(this.onlyColumnValue))
				return false;

			if (null != this.onlyColumnReg && !line.getCells().get(this.onlyColumn).matches(this.onlyColumnReg))
				return false;
		}

		return true;
	}
}

Db.java

package cn.appkit.cpmis;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.UUID;

public class Db {

	public Db(TableStruct tableStruct, Filter filter) throws Exception {
		String url = "jdbc:sqlserver://127.0.0.1\\SQLExpress:1433;databaseName=ZwzxkbMain";
		String user = "sa";
		String pwd = "xxxxxx";

		this.conn = DriverManager.getConnection(url, user, pwd);

		this.tableStruct = tableStruct;
		this.filter = filter;
	}

	private Connection conn;

	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}

	private PreparedStatement st;

	public void setSt(PreparedStatement st) {
		this.st = st;
	}

	private int count;
	private TableStruct tableStruct;
	private Filter filter;

	public void addBatch() throws Exception {

		st.addBatch();

		if (count > 0 && count % 5000 == 0) {
			st.executeBatch();

			count = 0;
		} else
			count++;
	}

	public void execute() throws Exception {
		if (count > 0)
			st.executeBatch();
	}

	public void setContent(Line line) throws Exception {
		if (!filter.doFilter(line))
			return;

		st.setString(1, "{" + UUID.randomUUID().toString() + "}");
		int length = tableStruct.getCols().size();
		for (int i = 1; i < length; i++) {
			FieldType ft = tableStruct.getCols().get(i).getfType();

			int colNum = tableStruct.getCols().get(i).getNum();
			// System.out.println(ft + " " + line.getCells().get(colNum));

			if (line.getCells().containsKey(colNum)) {
				String content = line.getCells().get(colNum);
				if (FieldType.STRING == ft)
					st.setString(i + 1, content);
				else if (FieldType.INT == ft)
					st.setInt(i + 1, Integer.parseInt(content));
				else if (FieldType.DATE == ft) {
					st.setDate(i + 1, convertToDate(content));
				} else if (FieldType.NUMBER == ft) {
					if (content.endsWith("%")) {
						content = content.substring(0, content.length() - 1);
						st.setBigDecimal(i + 1,
								(new java.math.BigDecimal(content).divide(new java.math.BigDecimal(100))));
					} else
						st.setBigDecimal(i + 1, new java.math.BigDecimal(content));
				} else
					throw new Exception("setContent 请补充类型" + ft + " " + content);

			} else {
				st.setObject(i + 1, null);
			}
		}
		// if (count < 10)
		this.addBatch();
	}

	private java.sql.Date convertToDate(String str) {
		java.util.Date d = null;

		if (str.matches("\\d{1,2}/\\d{1,2}/\\d{2}")) {
			SimpleDateFormat fmt = new SimpleDateFormat("MM/dd/yy");
			try {
				d = fmt.parse(str);
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}

		if (null == d)
			return null;
		else
			return new java.sql.Date(d.getTime());
	}

}

ImportXlsx.java

package cn.appkit.cpmis;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

import javax.xml.parsers.ParserConfigurationException;

import org.apache.poi.ooxml.util.SAXHelper;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.model.SharedStrings;
import org.apache.poi.xssf.model.Styles;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

/**
 * 导入xlsx数据
 * 
 * @author Administrator
 *
 */
public class ImportXlsx {

	public ImportXlsx(String xlsxFilename, Db db) throws Exception {
		this.db = db;
		this.minColumns = -1;
		this.xlsxPackage = OPCPackage.open(xlsxFilename, PackageAccess.READ);
	}

	private Db db;

	private Line line;
	private Map<Integer, String> cells;

	private final OPCPackage xlsxPackage;
	private final int minColumns;

	public void process() throws IOException, OpenXML4JException, SAXException {
		ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
		XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
		StylesTable styles = xssfReader.getStylesTable();
		XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
		int index = 0;
		while (iter.hasNext()) {
			try (InputStream stream = iter.next()) {
				String sheetName = iter.getSheetName();
				System.out.println(sheetName + " [index=" + index + "]:");
				processSheet(styles, strings, new SheetToCSV(), stream);
			}
			if (index == 0)
				break;
			++index;
		}
	}

	public void processSheet(Styles styles, SharedStrings strings, SheetContentsHandler sheetHandler,
			InputStream sheetInputStream) throws IOException, SAXException {
		DataFormatter formatter = new DataFormatter();
		InputSource sheetSource = new InputSource(sheetInputStream);
		try {
			XMLReader sheetParser = SAXHelper.newXMLReader();
			ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, sheetHandler, formatter, false);
			sheetParser.setContentHandler(handler);
			sheetParser.parse(sheetSource);
		} catch (ParserConfigurationException e) {
			throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
		}
	}

	private class SheetToCSV implements SheetContentsHandler {

		@Override
		public void startRow(int rowNum) {
			line = new Line();
			line.setNum(rowNum);
			cells = new HashMap<Integer, String>();
		}

		@Override
		public void endRow(int rowNum) {
			line.setCells(cells);

			try {
				db.setContent(line);
			} catch (Exception e) {
				e.printStackTrace();
			}
			// System.out.println(line);
		}

		@Override
		public void cell(String cellReference, String formattedValue, XSSFComment comment) {
			int thisCol = (new CellReference(cellReference)).getCol();
			cells.put(thisCol, formattedValue);
		}
	}

}

Line.java

package cn.appkit.cpmis;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class Line {
	private int num;

	public int getNum() {
		return num;
	}

	public void setNum(int num) {
		this.num = num;
	}

	private Map<Integer, String> cells;

	public Map<Integer, String> getCells() {
		return cells;
	}

	public void setCells(Map<Integer, String> cells) {
		this.cells = cells;
	}

	@Override
	public String toString() {
		List<String> ls = new ArrayList<String>();
		ls.add("Line No:" + this.num);
		for (Map.Entry<Integer, String> entry : cells.entrySet()) {
			ls.add(" " + entry.getKey() + "/" + entry.getValue());
		}

		return String.join("", ls);
	}

}

FieldSetting.java

package cn.appkit.cpmis;

/**
 *
 * 字段设置
 * 
 * @author Administrator
 *
 */
public class FieldSetting {
	private int num;
	private FieldType fType;
	private String fieldName;

	public int getNum() {
		return num;
	}

	public void setNum(int num) {
		this.num = num;
	}

	public FieldType getfType() {
		return fType;
	}

	public void setfType(FieldType fType) {
		this.fType = fType;
	}

	public String getFieldName() {
		return fieldName;
	}

	public void setFieldName(String fieldName) {
		this.fieldName = fieldName;
	}

	public String getFieldDispName() {
		return fieldDispName;
	}

	public void setFieldDispName(String fieldDispName) {
		this.fieldDispName = fieldDispName;
	}

	private String fieldDispName;

	public FieldSetting(int num, String fieldName, String strType, String fieldDispName, int decimalDigits)
			throws Exception {
		super();
		this.num = num;
		this.fType = this.ConvertType(strType, decimalDigits);
		this.fieldName = fieldName;
		this.fieldDispName = fieldDispName;
	}

	private FieldType ConvertType(String strType, int decimalDigits) throws Exception {
		if (strType.equalsIgnoreCase("字符串")) {
			return FieldType.STRING;
		} else if (strType.equalsIgnoreCase("数值型") && decimalDigits == 0) {
			return FieldType.INT;
		} else if (strType.equalsIgnoreCase("数值型") && decimalDigits != 0) {
			return FieldType.NUMBER;
		} else if (strType.equalsIgnoreCase("日期型")) {
			return FieldType.DATE;
		} else
			throw new Exception("FieldSetting: 数据类型转换失败！" + strType);

	}

	@Override
	public String toString() {
		return "FieldSetting [num=" + num + ", fType=" + fType + ", fieldName=" + fieldName + ", fieldDispName="
				+ fieldDispName + "]";
	}

}

FieldType.java

package cn.appkit.cpmis;

public enum FieldType {
	INT, NUMBER, STRING, DATE
}

ImportConfig.xml

<?xml version="1.0" encoding="utf-8"?>
<configs dbType="Sql" dbName="ZwzxkbMain" server=".\sqlexpress" database="ZwzxkbMain" usr="" pwd="">
  <config tableName="YHQXB" dispName="用户权限表" primaryKey="GH">
    <contentConfig startLine="2" maxLine="" keyColumn="" offsetRow="" offsetColumn="" noColumn="3" noColumnValue="" noColumnReg="" onlyColumn="" onlyColumnValue="" onlyColumnReg="" isWritingLineNo="true" />
    <contentConfig flagRow="" flagCol="" flagValue="" flagValueReg="" />
    <columnSettings>
      <columnSetting content="GH,字符串,A,10,0,工号" />
      <columnSetting content="XM,字符串,B,10,0,姓名" />
      <columnSetting content="JSBM,字符串,C,10,0,角色编码" />
      <columnSetting content="JS,字符串,D,10,0,角色" />
      <columnSetting content="XZ,字符串,E,10,0,小组" />
      <columnSetting content="PQ,字符串,F,10,0,片区" />
      <columnSetting content="WJM,字符串,-,100,0,文件名" />
      <columnSetting content="HH,数值型,-,10,0,行号" />
    </columnSettings>
  </config>
</configs>