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);
}
}