这篇文章是介绍java中的poi技术读取Excel数据,然后保存到MySQL数据中。
------------------------------jsp页面-----------------------------
<script type="text/javascript">
//点击上传时判断是否选择文件
var fileSizeValidate=function()
{
var p1 = document.getElementById("file").value;
if(p1==""){
return false;
}
return true;
}
function input(){
if(!fileSizeValidate()){
alert("请先选择文件!");
return ;
}else{
document.getElementById("inputFile").submit();
}
}
</script>
<s:form action="upload_upExcel.action" method="post" enctype="multipart/form-data" id="inputFile">
<table >
<tr>
<!-- 上传文件标签定义 -->
<td class="font_colors03"><label>指定导入文件: </label> <s:file
name="file" id="file"></s:file></td>
<td><label>
<input id="upsubmit" type="button" value="导入" οnclick="input();"
style="width:50px;height:25px" />
</label>
</td>
</tr>
</table>
</s:form>
---------------------------------------------------action-----------------------------------------------
// 上传文件集合
private File file;
// 上传文件名集合
private List<String> fileFileName;
public String upExcel() throws LogisticException {
try {
if (file != null) {
InputStream in = new FileInputStream(file);
String pathString = "D:/upload";
String fileNameString = peopleCode
+ "-"
+ this.getFileFileName().get(0);
File upLoadFile = new File(pathString, fileNameString);
System.out.println("导入文件:" + fileNameString);
OutputStream out = new FileOutputStream(upLoadFile);
byte[] buffer = new byte[1024 * 1024];
int len = 0;
while ((len = in.read(buffer)) > 0) {
out.write(buffer, 0, len);
}
jieXiExcel(fileNameString);
in.close();
out.close();
}
} catch (Exception e) {
e.printStackTrace();
}
/**
* 解析文件方法
*
* @param fileName
* @return map
*/
public Map<String, String> jieXiExcel(String fileName) {
Map<String, String> map = new HashMap<String, String>();
try {
// 通过Workbook的静态方法getWorkbook选取Excel文件
String parhString = "D:/upload";
Workbook workbook = Workbook.getWorkbook(new File(parhString + "/"
+ fileName));
// 通过Workbook的getSheet方法选择第一个工作簿(从0开始)
Sheet sheet = workbook.getSheet(0);
int rows = sheet.getRows();
int cols = sheet.getColumns();
Cell cells[][] = new Cell[cols][rows];
Date date = null;
for (int r = 0; r < rows; r++) {
for (int c = 1; c < cols - 1; c++) {
cells[c][r] = sheet.getCell(c, r);
// String value = "";
String cellVal = "";
cellVal = cells[c][r].getContents();
map.put("cells[" + c + "][" + r + "]", cellVal);
}
}
for (int rr = 0; rr < rows; rr++) {
Backdetail backdetail = new Backdetail();
backdetail.setSceneType(0);
..............
for (int cc = 1; cc < cols - 1; cc++) {
String val = map.get("cells[" + cc + "][" + rr + "]");
if (cc == 1) {
backdetail.setBackdate(val);
}
if (cc == 2) {
backdetail.setBackscene(val);
}
if (cc == 3) {
backdetail.setInscene(val);
}
............. }
// System.out.println(map.get("cell["+cc+"]["+rr+"]"));
}
// 保存数据 到库
backDetailImpl.save(backdetail);
}
System.out.println("导入数据完成!");
workbook.close();
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return map;
}