使用jxl，poi读取excel文件

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

作用：在java后台添加一个方法，读取导入的excel内容，根据需要返回相应的sql语句，以完成对临时表的插入操作。

使用jxl读取excel文件

package com.sixthf.bi.sapp.util;import java.io.IOException;
import java.io.InputStream;import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;import org.apache.struts.upload.FormFile;import com.got.util.uid.UUIDGener;public class PhoneUtil{ /**
* 读取xls文件内容
*
* @param file 想要读取的文件对象,importfilesn;
* @return sql语句
*/
public static String readPhoneExcel(FormFile formFile,String importfilesn) throws IOException, BiffException {
InputStream stream = formFile.getInputStream();
Workbook rwb = Workbook.getWorkbook(stream);
Sheet[] sheets = rwb.getSheets();
StringBuffer SBbuffer = new StringBuffer(); for(int i=0;i<sheets.length;i++) {
Sheet rs = rwb.getSheet(i);
String period = rs.getCell(0, 1).getContents().replace("年", "-").replace("月", "");//得到当前工作表的第二行第一列即账务周期
for(int j=1;j<rs.getRows();j++) {
String first = rs.getCell(0, j).getContents();
String phone = rs.getCell(1, j).getContents();//得到当前工作表的当前行的第二列即接入号码
if(!first.contains("合计")) {
if(!phone.equals("") && !phone.equals("小计:")) {
SBbuffer
.append("insert into CMMU_PAPERANSWER_TMP (SUBIMPORTFILESN,Importfilesn,answer1,answer2, answer5)values(")
.append("'")
.append(UUIDGener.getUUID().replaceAll("-", ""))
.append("','")
.append(importfilesn)
.append("','")
.append(period)
.append("','")
.append(phone)
.append("','"); for(int k=j+1;k<rs.getRows(); k++) {
phone = rs.getCell(1, k).getContents();
if(phone.contains("小计")) {
String costnum = rs.getCell(3, k).getContents();//得到当前工作表的当前行的第四列即费用 SBbuffer
.append(costnum)
.append("');")
.append("\n");
j = k;
break;
}
else continue;
}
}
else continue;
}
else break;
}
}
String string = SBbuffer.toString();
return string;
} }

使用poi读取excel

package com.sixthf.bi.sapp.util;import java.io.IOException;
import java.io.InputStream;import jxl.read.biff.BiffException;import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.struts.upload.FormFile;import com.got.util.uid.UUIDGener;public class PhoneUtil{ /**
* 读取xls文件内容
*
* @param file 想要读取的文件对象,importfilesn;
* @return sql语句
*/
public static String readPhoneExcel(FormFile formFile,String importfilesn) throws IOException, BiffException {
InputStream stream = formFile.getInputStream();
HSSFWorkbook rwb = new HSSFWorkbook(stream);
int numSheet = rwb.getNumberOfSheets();
StringBuffer SBbuffer = new StringBuffer(); for(int i=0;i<numSheet;i++) {
HSSFSheet rs = rwb.getSheetAt(i);
String period = rs.getRow(1).getCell((short)0).toString().replace("年", "-").replace("月", "");
for(int j=1;j<rs.getPhysicalNumberOfRows();j++) {
String first = rs.getRow(j).getCell((short) 0).toString();
String phone = rs.getRow(j).getCell((short) 1).toString();//得到当前工作表的当前行的第二列即接入号码
if(!first.contains("合计")) {
if(!phone.equals("") && !phone.equals("小计:")) {
SBbuffer
.append("insert into CMMU_PAPERANSWER_TMP (SUBIMPORTFILESN,Importfilesn,answer1,answer2, answer5)values(")
.append("'")
.append(UUIDGener.getUUID().replaceAll("-", ""))
.append("','")
.append(importfilesn)
.append("','")
.append(period)
.append("','")
.append(phone)
.append("','"); for(int k=j+1;k<rs.getPhysicalNumberOfRows(); k++) {
phone = rs.getRow(k).getCell((short) 1).toString();
if(phone.contains("小计")) {
String costnum = rs.getRow(k).getCell((short) 3).toString();//得到当前工作表的当前行的第四列即费用 SBbuffer
.append(costnum)
.append("');")
.append("\n");
j = k;
System.out.println(k);
break;
}
else continue;
}
}
else continue;
}
else break;
}
}
String string = SBbuffer.toString();
return string;
} }