Question

jsp页面table导入excel数据

jsp页面现有这样的一个表格：
想实现的是：将和这个表格格式相同的execl文件的数据导入到该页面显示，确认无误后将之存入到数据库。该怎么实现啊？急急急！！！！！

Answer 1

jsp页面中可以通过动态生成excel的方式把table数据导入并保存。
以下是例子：
public static void crExcel(List list, String absoluteFileString)
throws Exception {
// 字体格式
WritableFont wfc0 = new WritableFont(WritableFont.ARIAL, 22,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
WritableCellFormat wcfFC0 = new WritableCellFormat(wfc0);
wcfFC0.setAlignment(jxl.format.Alignment.CENTRE);
wcfFC0.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcfFC0.setBorder(Border.ALL, BorderLineStyle.THIN,jxl.format.Colour.GRAY_25);
// 表头字体
WritableFont wfc8 = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
WritableCellFormat wcfFC8 = new WritableCellFormat(wfc8);
wcfFC8.setAlignment(jxl.format.Alignment.CENTRE);
wcfFC8.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcfFC8.setBorder(Border.ALL, BorderLineStyle.THIN,jxl.format.Colour.GRAY_25);
WritableFont wfc1 = new WritableFont(WritableFont.ARIAL, 10,WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
WritableCellFormat wcfFC1 = new WritableCellFormat(wfc1);
wcfFC1.setAlignment(jxl.format.Alignment.CENTRE);
wcfFC1.setBorder(Border.ALL, BorderLineStyle.THIN,jxl.format.Colour.GRAY_25);

// 创建excel
OutputStream os = new FileOutputStream(absoluteFileString);

String readPath = I18nMessages.getText("efine.excel.dir");

InputStream input = new FileInputStream(readPath
+ "FKBalanceReport.xls");

Workbook workbook = Workbook.getWorkbook(input);
WritableWorkbook book = Workbook.createWorkbook(os, workbook);
WritableSheet sheet = book.getSheet(0);

// 数据写入
Label label = null;
// Title
//label = new jxl.write.Label(3, 0, "到期付款信息表", wcfFC0);
//sheet.addCell(label);
/*
* label = new jxl.write.Label(0, 3, "备注", wcfFC4);
* sheet.addCell(label);
*/

label = new jxl.write.Label(0, 0, "到期日", wcfFC8);
sheet.addCell(label);
label = new jxl.write.Label(1, 0, "部门名称", wcfFC8);
sheet.addCell(label);
label = new jxl.write.Label(2, 0, "币种", wcfFC8);
sheet.addCell(label);
label = new jxl.write.Label(3, 0, "银行", wcfFC8);
sheet.addCell(label);
label = new jxl.write.Label(4, 0, "金额", wcfFC8);
sheet.addCell(label);
label = new jxl.write.Label(5, 0, "业务类型", wcfFC8);
sheet.addCell(label);
label = new jxl.write.Label(6, 0, "摘要", wcfFC8);
sheet.addCell(label);
sheet.setName("到期付款信息查询");

int j = 1;
for (int i = 0; i <list.size(); i++) {
DNCM02 dto = (DNCM02) list.get(i);

label = new Label(0, i + j, CommonUtil.dateToStr(dto.getEndDay()),
wcfFC1);
sheet.addCell(label);
label = new Label(1, i + j, dto.getEndDeptName(), wcfFC1);
sheet.addCell(label);
label = new Label(2, i + j, dto.getCurrencyName(), wcfFC1);
sheet.addCell(label);
label = new Label(3, i + j, dto.getEndBankName(), wcfFC1);
sheet.addCell(label);
label = new Label(4, i + j, CommonUtil.formatString(dto
.getEndAmount().toString()), wcfFC1);
sheet.addCell(label);
label = new Label(5, i + j, dto.getEndTypeName(), wcfFC1);
sheet.addCell(label);
label = new Label(6, i + j, dto.getRemark(), wcfFC1);
sheet.addCell(label);
}
book.write();
book.close();
os.close();
}
然后指定excel的保存路径即可。

Answer 2

思路：用poi或者JXL技术读取上传的Excel文件，将数据保存到数据库中；返回到页面时将数据从数据读出，显示到页面上；对有误的数据进行修改，保存。
下面是poi处理Excel的思路：
//用上传的Excel文件创建文件输入流对象
InputStream input = new FileInputStream(excelfile);
//用文件输入流对象创建POI工具类
POIFSFileSystem fs = new POIFSFileSystem(input);
//创建workbook,可以认为就是已经上传的Excel文件
HSSFWorkbook hwb = new HSSFWorkbook(fs);
//依次循环Excel文件的每个sheet
for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
HSSFSheet sheet = hwb.getSheetAt(i);// 获取第i个sheet
Iterator<Row> rows = sheet.rowIterator();// 获取所有含有信息的行
while (rows.hasNext()) {// 循环行
HSSFRow row = (HSSFRow) rows.next();
Iterator<Cell> cells = row.cellIterator();// 获取一行中的所有含有值的单元格
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();// 获取一个单元格
int columnIndex = cell.getColumnIndex();// 获取单元格的顺序号（从0开始）
String value = ExcelUtil.getCellValue(cell);// 获取单元格的值
//将值付给实体对象，保存到数据库
后面省略
}
}

Answer 3

用apache的POI开发包就可以实现了

追问

能给个实例么？poi没用过