读取Excel数据到数据库

项目用到的jar包：

	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi</artifactId>
	    <version>3.17</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.17</version>
	</dependency>
	
	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi-ooxml</artifactId>
	    <version>3.17</version>
	</dependency>
	
	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-examples -->
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi-examples</artifactId>
	    <version>3.9</version>
	</dependency>
	
	<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
	<dependency>
	    <groupId>mysql</groupId>
	    <artifactId>mysql-connector-java</artifactId>
	    <version>8.0.25</version>
	</dependency>
	
		<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
	<dependency>
	    <groupId>com.alibaba</groupId>
	    <artifactId>druid</artifactId>
	    <version>1.1.10</version>
	</dependency>

主要是POI的jar包，然后是连接数据库的连接包，阿里的数据连接池。
直接贴代码：

package cn.whuhhh.www.data;

import java.io.IOException;
import java.io.InputStream;
import java.text.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import cn.whuhhh.www.dataStructure.*;

public class Import {
	public static List<AdmissionTicket> importFromExcel(String fileName) throws IOException{
		List<AdmissionTicket> admissionTickets = new ArrayList<AdmissionTicket>();
		
		InputStream in = Import.class.getClassLoader().getResourceAsStream(fileName);
		//整个文档对象
		XSSFWorkbook workbook = new XSSFWorkbook(in);
		//单个sheet对象
		XSSFSheet sheet = workbook.getSheetAt(0);
		
		int lastRowNum = sheet.getLastRowNum();
		for (int i=1; i<=lastRowNum; i++) {
			//一行对象
			XSSFRow row = sheet.getRow(i);
			//XSSFCell cell = row.getCell(0);
			String name = getStringValue(row.getCell(0));
			String candidate = getStringValue(row.getCell(1));
			String idnumber = getStringValue(row.getCell(2));
			String gender = getStringValue(row.getCell(3));
			String subject = getStringValue(row.getCell(4));
			String address = getStringValue(row.getCell(5));
			String time = getStringValue(row.getCell(6));
			String seat = getStringValue(row.getCell(7));
			String email = getStringValue(row.getCell(8));
			AdmissionTicket admissionTicket = new AdmissionTicket(name, candidate, idnumber, gender,subject, address, time, seat, email);
			admissionTickets.add(admissionTicket);
		}
		workbook.close();
		
		return admissionTickets;
	}
	
	private static String getStringValue(Cell cell) {
		if(cell == null) {
			return "";
		}
		String cellValue = "";
		if(cell.getCellTypeEnum() == CellType.NUMERIC){
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				Date d =HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
				DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm");
				return df.format(d);
			} else {
			NumberFormat nf = NumberFormat.getInstance();
			cellValue = String.valueOf(nf.format(cell.getNumericCellValue())).replace(",", "");
			}
			}else if(cell.getCellTypeEnum() == CellType.STRING){
			cellValue = String.valueOf(cell.getStringCellValue());
			}else if(cell.getCellTypeEnum() == CellType.BOOLEAN){
			cellValue = String.valueOf(cell.getBooleanCellValue());
			}else if(cell.getCellTypeEnum() == CellType.ERROR){
			cellValue = "错误类型";
			}else {
			cellValue = "";
			}
			
		return cellValue;
	}
}

这里用的是POI的3.17版本，注意其一些方法与3.15版本还是有很多的不一致，注意这个getStringValue方法的写法。

连接数据库这部分参考我之前的写的blog。贴一部分插入的代码。

	/**
	 * 批量添加准考证到数据库
	 * @param tickets
	 * @return 
	 */
	public static void addAll(List<AdmissionTicket> tickets) {
		Connection conn = null;
		try {
			conn = ConnectionFactory.getConnection();
			//防止恶意SQL注入，PreparedStatement
			conn.setAutoCommit(false);
			//构建数据库执行者
			PreparedStatement p = conn.prepareStatement("INSERT INTO admissionticket (StudentName, candidateNumber, idNumber, gender, subjects_, addrass, time, seat, email) "+
			"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);");
			for (AdmissionTicket at : tickets) {
				p.setString(1, at.getName());
				p.setString(2, at.getCandidateNumber());
				p.setString(3, at.getIdNumber());
				p.setString(4, at.getGender());
				p.setString(5, at.getSubject());
				p.setString(6, at.getAddress());
				p.setString(7, at.getTime());
				p.setString(8, at.getSeat());
				p.setString(9, at.getEmail());
				//结构类似就一起执行
				p.addBatch();
			}
			p.executeBatch();
			conn.commit();
			p.close();
		}catch(Exception e1) {
			e1.printStackTrace();
			try {
				//回滚事务？？？
				conn.rollback();
			}
			catch(SQLException e) {
				e.printStackTrace();
			}
		}
		finally {
			try {
				if(null != conn) {
					conn.close();
				}
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
	}

由模板制作准考证（word文档处理）

jar包依赖：

	<!-- https://mvnrepository.com/artifact/com.google.zxing/core -->
	<dependency>
	    <groupId>com.google.zxing</groupId>
	    <artifactId>core</artifactId>
	    <version>3.3.2</version>
	</dependency>
	
	<!-- https://mvnrepository.com/artifact/com.google.zxing/javase -->
	<dependency>
	    <groupId>com.google.zxing</groupId>
	    <artifactId>javase</artifactId>
	    <version>3.3.2</version>
	</dependency>
	
	<!-- https://mvnrepository.com/artifact/fr.opensagres.xdocreport/org.apache.poi.xwpf.converter.pdf -->
	<dependency>
	    <groupId>fr.opensagres.xdocreport</groupId>
	    <artifactId>org.apache.poi.xwpf.converter.pdf</artifactId>
	    <version>1.0.6</version>
	</dependency>
	
	<dependency>
	    <groupId>fr.opensagres.xdocreport</groupId>
	    <artifactId>org.apache.poi.xwpf.converter.xhtml</artifactId>
	    <version>1.0.6</version>
	</dependency>

任务描述：

• 依据准考证的模板，读取数据库文件，完成文字替换，批量生成准考证。
• 制作二维码，在docx中插入图片。

准考证模板实例：

原理就是，读取这个模板文件，替换掉那些信息，顺便制作二维码插入到${pic}位置。

package cn.whuhhh.www.fileMaker;

import fr.opensagres.xdocreport.itext.extension.font.IFontProvider;
import cn.whuhhh.www.dataStructure.AdmissionTicket;
import cn.whuhhh.www.dataStructure.ImageData;
import cn.whuhhh.www.fileMaker.QRCodeUtils;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.util.Units;
import org.apache.poi.xwpf.converter.pdf.PdfConverter;
import org.apache.poi.xwpf.converter.pdf.PdfOptions;
import org.apache.poi.xwpf.usermodel.*;

import com.lowagie.text.Font;
import com.lowagie.text.pdf.BaseFont;

import java.io.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class DocxTemplateToPdfImpl {
	public static void generateAll(List<AdmissionTicket> tickets) throws IOException {
		for(AdmissionTicket ticket : tickets) {
			generateDocxAndPdf(ticket);
		}
	}
	
	public static void generateDocxAndPdf(AdmissionTicket admissionTicket) throws IOException {
		Map<String,Object> params = new HashMap<String, Object>();
		params.put("${can}", admissionTicket.getCandidateNumber());
		params.put("${add}", admissionTicket.getAddress());
		params.put("${name}", admissionTicket.getName());
		params.put("${gender}", admissionTicket.getGender());
		params.put("${subject}", admissionTicket.getSubject());
		params.put("${time}", admissionTicket.getTime());
		params.put("${seat}", admissionTicket.getSeat());
		String resource = "docx_template.docx";
		
		Map<String, ImageData> picParams = new HashMap<String, ImageData>();
		byte[] zxingqrCode = QRCodeUtils.createZxingqrCode(admissionTicket.getIdNumber());
		ImageData qrCode = new ImageData();
		qrCode.setData(zxingqrCode);
		picParams.put("${pic}", qrCode);
		
		try(InputStream inputStream = DocxTemplateToPdfImpl.class.getClassLoader().getResourceAsStream(resource)){
			
			XWPFDocument doc = new XWPFDocument(inputStream);//导入模板文件
			List<IBodyElement> ibes = doc.getBodyElements();
			for(IBodyElement ib:ibes) {
				if(ib.getElementType() == BodyElementType.TABLE) {
					replaceTable(ib, params, picParams, doc);
				}
			}
			PdfOptions options = PdfOptions.create();
			//中文字体处理
			options.fontProvider(new IFontProvider(){
				
				public Font getFont(String familyName, String encoding, float size, int stytle, java.awt.Color color) {
					try {
						BaseFont bfChinese = BaseFont.createFont(
								"C:\Windows\Fonts\STSONG.TTF",
								BaseFont.IDENTITY_H,BaseFont.EMBEDDED);
						Font fontChinese = new Font(bfChinese);
						if(familyName != null) {
							fontChinese.setFamily(familyName);	
						}
						return fontChinese;
					}catch(Exception e) {
						e.printStackTrace();
						return null;
					}
				}
			});
			//FileOutputStream os = new FileOutputStream(new File(admissionTicket.getCandidateNumber()+"_"+admissionTicket.getName()+".pdf"));
			//FileOutputStream os = new FileOutputStream(admissionTicket.getCandidateNumber()+"_"+admissionTicket.getName()+".pdf");
			//PdfConverter.getInstance().convert(doc, os, options);
			doc.write(new FileOutputStream(new File(admissionTicket.getCandidateNumber()+"_"+admissionTicket.getName()+".docx")));
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void replaceTable(IBodyElement para, Map<String, Object> params, Map<String, ImageData> picParams, XWPFDocument indoc) throws Exception {
		Matcher matcher;
		XWPFTable table;
		List<XWPFTableRow> rows;
		List<XWPFTableCell> cells;
		
		table = (XWPFTable) para;
		rows = table.getRows();
		for(XWPFTableRow row : rows) {
			cells = row.getTableCells();
			int cellSize = cells.size();
			int cellCount = 0;
			for(cellCount = 0; cellCount<cellSize; cellCount++) {
				XWPFTableCell cell = cells.get(cellCount);
				String runtext = "";
				List<XWPFParagraph> ps = cell.getParagraphs();
				for(XWPFParagraph p:ps) {
					//表示一段文字？
					for(XWPFRun run:p.getRuns()) {
						runtext = run.text();
						matcher = matcher(runtext);
						if(matcher.find()) {
							if(picParams != null) {
								//遍历图片的键值对表
								for(String picKey : picParams.keySet()) {
									if(matcher.group().equals(picKey)) {
										run.setText("",0);
										replacePic(run, picParams.get(picKey),indoc);
									}
								}
							}
							if(params != null) {
								for(String picKey:params.keySet()) {
									if(matcher.group().equals(picKey)) {
										run.setText(params.get(picKey)+"", 0);
									}
								}
							}
						}
					}
				}
			}
		}
	}
	
	/**
	 * 匹配到相应的位置
	 */
	private static Matcher matcher(String str) {
		Pattern pattern = Pattern.compile("\$\{(.+?)\}", Pattern.CASE_INSENSITIVE);
		Matcher matcher = pattern.matcher(str);
		return matcher;
	}
	
	public static void replacePic(XWPFRun run, ImageData data, XWPFDocument doc) throws Exception{
		int format = Document.PICTURE_TYPE_PNG;
		ByteArrayInputStream inputStream = new ByteArrayInputStream(data.getData());
		run.addPicture(inputStream, format, "rpic", Units.toEMU(100), Units.toEMU(100));
	}
}

二维码制作方法：

package cn.whuhhh.www.fileMaker;

import com.google.zxing.*;
import com.google.zxing.common.BitMatrix;
import com.google.zxing.common.HybridBinarizer;
import com.google.zxing.qrcode.decoder.ErrorCorrectionLevel;
import com.google.zxing.client.j2se.BufferedImageLuminanceSource;
import com.google.zxing.client.j2se.MatrixToImageWriter;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.nio.file.Path;
import java.util.HashMap;
import java.util.Map;

public class QRCodeUtils {
	/*
	 * 定义二维码的宽高
	 */
	private static int WIDTH = 150;
	private static int HEIGHT = 150;
	private static String FORMAT = "png";
	
	// 生成二维码
	public static void createZxingqrCode(File file, String content) {
		//定义二维码参数
		Map<EncodeHintType, Object> hints = new HashMap<EncodeHintType, Object>();
		
		hints.put(EncodeHintType.CHARACTER_SET, "utf-8");//设置编码
		hints.put(EncodeHintType.ERROR_CORRECTION, ErrorCorrectionLevel.M);//设置容错等级
		hints.put(EncodeHintType.MARGIN, 2);//设置默认边距
		
		try {
			BitMatrix bitMatrix = new MultiFormatWriter().encode(content, BarcodeFormat.QR_CODE, WIDTH, HEIGHT, hints);
			Path path = file.toPath();
			//写文件是写在该项目的根目录上。
			MatrixToImageWriter.writeToPath(bitMatrix, FORMAT, path);
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	//生成二维码
	public static byte[] createZxingqrCode(String content) {
		//定义二维码参数
		Map<EncodeHintType, Object> hints = new HashMap<EncodeHintType, Object>();
		
		hints.put(EncodeHintType.CHARACTER_SET, "utf-8");//设置编码
		hints.put(EncodeHintType.ERROR_CORRECTION, ErrorCorrectionLevel.M);//设置容错等级
		hints.put(EncodeHintType.MARGIN, 2);//设置默认边距
		
		try (ByteArrayOutputStream os = new ByteArrayOutputStream()) {
			BitMatrix bitMatrix = new MultiFormatWriter().encode(content, BarcodeFormat.QR_CODE, WIDTH, HEIGHT, hints);
			//Path path = file.toPath();
			MatrixToImageWriter.writeToStream(bitMatrix, FORMAT, os);
			return os.toByteArray();
		}catch(Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public static void readZxingQrCode(File file) {
		MultiFormatReader reader = new MultiFormatReader();
		try {
			BufferedImage image = ImageIO.read(file);
			BinaryBitmap binaryBitmap = new BinaryBitmap(new HybridBinarizer(new BufferedImageLuminanceSource(image)));
			Map<DecodeHintType, Object> hints = new HashMap<>();
			Result result = reader.decode(binaryBitmap, hints);
			System.out.println("解析结果："+result.toString());
			System.out.println("二维码格式："+result.getBarcodeFormat());
			System.out.println("二维码文本内容:"+result.getText());
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		createZxingqrCode(new File("hhh.png"),"hhh is handsome");
		readZxingQrCode(new File("hhh.png"));
	}
}

总结

简单记录，多敲代码，哪怕现在照着别人的代码来敲，受益匪浅。java真的好繁琐，python就好很多了。

最后

以上就是微笑皮皮虾为你收集整理的demo1—java读取Excel文件到数据库、java制作准考证读取Excel数据到数据库由模板制作准考证（word文档处理）总结的全部内容，希望文章能够帮你解决demo1—java读取Excel文件到数据库、java制作准考证读取Excel数据到数据库由模板制作准考证（word文档处理）总结所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。