数据导入excel表
导入excel表,需要添加jar包 jxl.jar
百度网盘下载链接:https://pan.baidu.com/s/1vZVr-GzPWlIKgrr-SOnhSA
提取码:2u0k
public static void data2Excel(List<Og> list) {
try {
// 创建可写入的Excel工作簿
String fileName = "C:\\Users\\Administrator\\Desktop\\111.xlsx";
File file = new File(fileName);
if (!file.exists()) {
file.createNewFile();
}
//以fileName为文件名来创建一个Workbook
WritableWorkbook wwb = jxl.Workbook.createWorkbook(file);
// 创建工作表
WritableSheet ws = wwb.createSheet("Test Shee 1", 0);
//要插入到的Excel表格的行号,默认从0开始
Label labelId = new Label(0, 0, "编号(id)");
Label labelOg = new Label(1, 0, "名称(og)");
Label labelCount = new Label(2, 0, "数量(count)");
ws.addCell(labelId);
ws.addCell(labelOg);
ws.addCell(labelCount);
for (int i = 0; i < list.size(); i++) {
Label id = new Label(0, i + 1, list.get(i).getId() + "");
Label og = new Label(1, i + 1, list.get(i).getOg());
Label count = new Label(2, i + 1, list.get(i).getCount() + "");
ws.addCell(id);
ws.addCell(og);
ws.addCell(count);
}
//写进文档
wwb.write();
// 关闭Excel工作簿对象
wwb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//注意:存入excel时,所有单元格均是以字符串的形式存入的(尽管原字段类型是int类型)
//注意:存入excel时,所有单元格均是以字符串的形式存入的(尽管原字段类型是int类型)
excel导入数据库
先从excel表获取的集合ogList
再将集合ogList,批量插入数据库
//先从excel表获取的集合ogList
public static List<Og> excel2Data(String excelPath) throws Exception {
try {
excelPath = "C:\\Users\\Administrator\\Desktop\\111.xlsx";
//String encoding = "GBK";
File excel = new File(excelPath);
String[] split = excel.getName().split("\\."); //.是特殊字符,需要转义!!!!!
Workbook wb = null;
InputStream fis = new FileInputStream(excel); //文件流对象
//根据文件后缀(xls/xlsx)进行判断
if ("xls".equals(split[1])) {
wb = new HSSFWorkbook(fis);
} else if ("xlsx".equals(split[1])) {
wb = new XSSFWorkbook(fis);
} else {
throw new Exception("上传文件不是excel文件!");
}
//开始解析
Sheet sheet = wb.getSheetAt(0); //读取sheet 0
int firstRowIndex = sheet.getFirstRowNum() + 1; //第一行是列名,所以不读
int lastRowIndex = sheet.getLastRowNum();
List<Og> ogList = new ArrayList<>();
String id = null;
String ogString = null;
String count = null;
//属性字段行
Row title = sheet.getRow(firstRowIndex - 1);
for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) { //遍历行
Row row = sheet.getRow(rIndex);
if (row != null) {
Og og = new Og();
int firstCellIndex = title.getFirstCellNum();
int lastCellIndex = title.getLastCellNum();
for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) { //遍历列
Cell cell = row.getCell(cIndex);
//获取空值的单元格
if (cell == null || "".equals(cell.toString().trim())) {
Integer rr = (rIndex + 1);
Integer cc = (cIndex + 1);
String ss = (char) (cc + 64) + "";
String asd = ss + rr;
throw new Exception("excel文件导入数据库失败:" + asd + " 单元格空值!");
} else {
switch (cIndex) {
case 0:
id = cell.toString();
break;
case 1:
ogString = cell.toString();
break;
case 2:
count = cell.toString();
break;
default:
break;
}
}
}
//从excel获取数字时,例如 10 会获取变成字符串“10.0”,需要转换
int i1 = id.lastIndexOf(".");
String substring1 = id.substring(0, i1);
Integer aa1 = Integer.valueOf(substring1);
og.setId(aa1);
int i = count.lastIndexOf(".");
String substring = count.substring(0, i);
Integer aa = Integer.valueOf(substring);
og.setCount(aa);
og.setOg(ogString);
ogList.add(og);
}
}
return ogList;
} catch (Exception e) {
e.printStackTrace();
throw new Exception("excel文件导入数据库失败");
}
}
//最后将从excel表获取的集合ogList,批量插入数据库
//最后将从excel表获取的集合ogList,批量插入数据库