【文件学习】读取excel文件

今天药忘吃喽~ 2023-07-23 11:54 41阅读 0赞

#### `springboot`使用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);
    	}
    }