【文件学习】读取excel文件

springboot使用poi解析excel

第一步导入依赖

<!-- poi解析excel -->
   	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.14</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>3.14</version>
	</dependency>

第二步骤

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;
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.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.fasterxml.jackson.databind.exc.InvalidFormatException;

public class ExcelUtil {

	private static Logger log = LoggerFactory.getLogger(ExcelUtil.class);

	public static String ImportDraftProduct() throws IOException, InvalidFormatException, EncryptedDocumentException,
			org.apache.poi.openxml4j.exceptions.InvalidFormatException {

		String filePath = "C:\\Users\\fengxiankai\\Desktop\\b.xlsx";
		// 根据文件类型，创建 HSSFWorkbook（XLS类型）或者 XSSFWorkbook（.XLSX类型）
		Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(filePath)));
		Sheet sheet = workbook.getSheetAt(0);
		StringBuilder sql = new StringBuilder(
				"insert into product_contract(userId,username,mobile,dept_name,role_p,role_c,"
						+ "create_time,update_time,del_flag) values");
		int lastRowNum = sheet.getLastRowNum();
		for (int i = 2; i <= sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			sql.append("(");
			for (Cell cell : row) {
				cell.setCellType(Cell.CELL_TYPE_STRING);
			}
			String userId = getValue(row.getCell(0));// userId
			String username = getValue(row.getCell(2));// username
			String mobile = getValue(row.getCell(3));// mobile
			String dept_name = getValue(row.getCell(4));// dept_name
			String role_p = "管理员".equals(getValue(row.getCell(5))) ? "1" : "0";
			String role_c = "普通用户".equals(getValue(row.getCell(6))) ? "1" : "0";
			sql.append("'" + userId + "','" + username + "','" + mobile + "','" + dept_name + "','" + role_p + "','"
					+ role_c);
			sql.append("','2020-04-07','2020-04-07','0'");
			sql.append("),");
		}
		workbook.close();
		if (lastRowNum > 2) {
			sql.deleteCharAt(sql.length() - 1);
			return sql.toString();
		}
		return "";
	}

	public static String getValue(final Cell cell) {
		if (cell != null) {
			cell.setCellType(Cell.CELL_TYPE_STRING);
			String value = cell.getStringCellValue().trim();
			return value;
		}
		return "";
	}

	public static void main(final String[] args) throws InvalidFormatException, EncryptedDocumentException,
			org.apache.poi.openxml4j.exceptions.InvalidFormatException, IOException {
		String result = ExcelUtil.ImportDraftProduct();
		log.info(result);
	}
}