读取EXCEL到数据库中?最好给个列子
解决方案 »
- 帮忙看下这个js
- jsp通过表单传递搜索条件,查询结果分页的问题
- 求救Hibernate 问题,急,请帮我看下我的代码,好么!
- 小弟请教高手连接数据库的问题,在线等!!谢谢了,各位大哥!
- 在JSP中如何实现页面的定时转向另一个页面?
- 的问题?急,再现等!高分!
- 使用readline()方法通过键盘输入字符串
- 用Jbuilder9的同志帮我看看这个问题。
- 问一个编译javabean的小问题,大侠帮忙!!!
- 我做了一个连接数据库的db,但在jsp上面怎么实现分页功能?
- Hibernate添加一条记录后立即查询数据库记录总数时没有发生变化?
- java或js如何监听IE的弹出窗口并发送回车事件
Workbook workbook=Workbook.getWorkbook(new File(filename));
Sheet sheet=workbook.getSheet(0);
int rows=sheet.getRows();
int columns=sheet.getColumns();
String [] content=new String [100];
for(int i=1;i {
for(int j=1;j {
Cell cell=sheet.getCell(j,i);
content[j]=cell.getContents();
}
}接下来要实现的就是把导出来的数据插入到相应的表中,这里要有一个转换比如说在excel中用汉字实现的而在数据库中是用int型的数字实现的,所以这就要在插入之前先做一下转换,还有一个问题就是要插入的字段在该表中是另一个表的外键例如在excel中存的是一个人的工种名称,但是在这个人的基本信息表中工种名称是以外健形式存在的通过两个表join来获取工种名称,所以在插入之前要把读出的工种名称转换为工种的pid然后插入到数据库中。所有这些都实现了以后就可以使用insert来插入到数据库了。
public static void main(String[] args) {
// TODO Auto-generated method stub
CouponDao dao=CouponDao.getInstance();
try {
FileInputStream fis = new FileInputStream(new File("c:/1.xls"));
jxl.Workbook rwb = Workbook.getWorkbook(fis);
Sheet rs = rwb.getSheet(0);
int rows = rs.getRows();
int columns = rs.getColumns();
int n = 0;
int c = 2;
int t = 3;
int a = 4;
for (int i = 1; i < rows; i++) {
String name = rs.getCell(n, i).getContents().trim();
String city = rs.getCell(c, i).getContents().trim();
String tag = rs.getCell(t, i).getContents().trim();
String address = rs.getCell(a, i).getContents().trim();
Coupon coupon=new Coupon();
coupon.setAddTime(new Date());
coupon.setCity(city);
coupon.setLeftCount(0);
coupon.setOriCount(0);
coupon.setOperator("");
coupon.setPlace(tag);
coupon.setName(name);
coupon.setAddress(address);
dao.saveOrUpdate(coupon);
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (DAOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
DiskFileItemFactory factory = new DiskFileItemFactory();
factory.setSizeThreshold(1024 * 1024 * 5);
ServletFileUpload upload = new ServletFileUpload(factory);
upload.setSizeMax(1024 * 1024 * 5);
List items = null;
try {
items = upload.parseRequest(req);
} catch (FileUploadException ex1) {
ex1.printStackTrace();
} InputStream uploadedStream = null;
for (Iterator iterator = items.iterator(); iterator.hasNext();) {
FileItem item = (FileItem) iterator.next();
if (item.isInMemory()) {
try {
uploadedStream = item.getInputStream();
} catch (IOException ex) {
ex.printStackTrace();
}
}
}
HSSFWorkbook workbook = new HSSFWorkbook(uploadedStream);
HSSFSheet sheet = workbook.getSheet("");
getSheetValue(sheet,i,j);
uploadedStream.close();//关闭
}protected static String getSheetValue(HSSFSheet sheet, int rowNum, int columnNum, int digit) {
HSSFCell cell = null;
String cellvalue = null;
try {
HSSFRow row = sheet.getRow(rowNum);
cell = row.getCell((short) columnNum);
} catch (NullPointerException ex) {
return null;
} if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: { // 如果当前Cell的Type为NUMERIC
if (HSSFDateUtil.isCellDateFormatted(cell)) { // 判断当前的cell是否为Date
Date date = cell.getDateCellValue(); // 如果是Date类型则,取得该Cell的Date值
cellvalue = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString(); // 把Date转换成本地格式的字符串
} else { // 如果是纯数字
cellvalue = formatTechnologyNumber(cell.getNumericCellValue());
BigDecimal num = BigDecimalUtils.setScale(digit, new BigDecimal(cellvalue));
cellvalue = String.valueOf(num);
}
break;
}
case HSSFCell.CELL_TYPE_FORMULA: {
String formula = cell.getCellFormula();
if (formula.indexOf("DATE(") >= 0) {
cellvalue = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
} else {
//包括formula.indexOf("SUM(") >= 0)的情况
cellvalue = formatTechnologyNumber(cell.getNumericCellValue());
BigDecimal num = BigDecimalUtils.setScale(digit, new BigDecimal(cellvalue));
cellvalue = String.valueOf(num);
}
break;
}
case HSSFCell.CELL_TYPE_STRING: { // 如果当前Cell的Type为STRIN
cellvalue = cell.getRichStringCellValue().getString().replaceAll("'", "''"); // 取得当前的Cell字符串
break;
}
default: // 默认的Cell值
cellvalue = "";
}
}
return cellvalue;
}
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;public class pexecl { public static ArrayList<Object[]> column(Connection getConnnection,String sql) throws Exception { // 该表的列名
ArrayList<Object[]> list = new ArrayList<Object[]>();
Connection con = getConnnection;
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
String[] str = new String[rs.getMetaData().getColumnCount()]; for (int i = 0; i < str.length; i++) {
str[i] = rs.getMetaData().getColumnName(i + 1); }
list.add(str); while (rs.next()) {
Object[] obj = new Object[rs.getMetaData().getColumnCount()]; for (int i = 0; i < obj.length; i++) {
Object sss = rs.getObject(i + 1);
if(sss == null)
{
obj[i] = "";
}
else
{
obj[i] = sss;
}
} list.add(obj);
} rs.close();
return list;
} public static void writeExcel(Connection getConnnection,String sql) throws Exception { // 保存数据到Excel里面
File file = new File("e://用户信息.xls");
OutputStream os = new FileOutputStream(file);
try {
WritableWorkbook wwb = Workbook.createWorkbook(os);
WritableSheet ws = wwb.createSheet("testSheet1", 100);
ws.setName("用户信息备份");
ws.setColumnView(30,20);
WritableFont wfc = new WritableFont(WritableFont.ARIAL, 10,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
WritableCellFormat wcfFC = new WritableCellFormat(wfc);
for (int i = 0; i < column(getConnnection,sql).size(); i++)
for (int j = 0; j <column(getConnnection,sql).get(i).length; j++) {
Label valuename = new Label(j, i, column(getConnnection,sql).get(i)[j].toString(),wcfFC);
ws.addCell(valuename);
}
System.out.println("成功导入请在E盘查看....");
wwb.write();
wwb.close();
} catch (Exception e) {
System.out.println("保存失败");
e.printStackTrace();
}
} public static void main(String[] args) throws Exception {
// pexecl.writeExcel();
// for (int i = 0; i < column().size(); i++) {
// for (int j = 0; j <column().get(i).length; j++) {
// System.out.print((column().get(i)[j]) + "\t");
// }
// System.out.println();
// }
}
}