java利用poi技术导入批量excel数据，并且分段存入数...-poi写入excel,poi向excel中写入数据

jxl，可以实现，开源的jar包很多，目前我使用的是jxl
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
e799bee5baa6e79fa5e98193e78988e69d83338String id =req.getParameter("id");
String fileName =req.getParameter("fileName");
File f = new File(req.getSession().getServletContext().getRealPath("/printTemplateExcel/"+fileName));
WritableWorkbook wwb=null;
WritableSheet wws=null;
FileOutputStream out =null;
Label label = null;
Workbook wb=null;
try {
FesOrders fesOrders =new FesOrders();
fesOrders.setId(Long.parseLong(id));
List<FesOrders> fesOrdersList = fesOrderService.findList(fesOrders, (SysUser)req.getSession().getAttribute(SESSION_USER));
for(FesOrders a : fesOrdersList){
System.out.println(a.getId());
}
WritableFont headerFont =new WritableFont(WritableFont.createFont("新宋体"), 10, WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat dataFormat=new WritableCellFormat (headerFont);
dataFormat.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.BLACK);//设置边框
WritableCellFormat wf = new WritableCellFormat(dataFormat);
wb = Workbook.getWorkbook(f);
out = new FileOutputStream(new File(req.getSession().getServletContext().getRealPath("/printExcel/"+fileName)));
wwb = Workbook.createWorkbook(out, wb);
wws = wwb.getSheet("出货单");
String date=sdf.format(new Date());
for(int i=0;i<wws.getRows();i++){
for(int j=0;j<wws.getColumns();j++){
Cell cell = wws.getCell(j, i);
if(cell.getContents().trim().indexOf("${year}")>0){
label = (Label)cell;
label.setString(cell.getContents().replace("${year}", date.subSequence(0, 4)));
}
if(cell.getContents().trim().indexOf("${mouth}")>0){
label = (Label)cell;
label.setString(cell.getContents().replace("${mouth}", date.substring(5, 7)));
}
if(cell.getContents().trim().indexOf("${day}")>0){
label = (Label)cell;
label.setString(cell.getContents().replace("${day}", date.substring(8, 10)));
}
}
}
System.out.println(fesOrdersList.size());
for(int i=0;i<fesOrdersList.size();i++){
FesOrders fesOrder= fesOrdersList.get(i);
List<FesSendOrders> fesSendOrders=fesOrder.getFesSendOrderses();
wws.addCell(new Label(0,i+2,i+1+"",wf));
wws.addCell(new Label(1,i+2,fesOrder.getSysUser().getCnName(),wf));
wws.addCell(new Label(2,i+2,fesOrder.getInnerSendNo(),wf));
wws.addCell(new Label(3,i+2,fesSendOrders.get(0).getFesTransportModes().getName(),wf));
if(fesSendOrders.get(0).getOpWeight()!=null){
wws.addCell(new Label(4,i+2,fesSendOrders.get(0).getOpWeight().toString(),wf));
}
wws.addCell(new Label(5,i+2,"",wf));
wws.addCell(new Label(6,i+2,fesSendOrders.get(0).getFesAddresseesBySenderId().getAddress(),wf));
wws.addCell(new Label(7,i+2,"",wf));
wws.addCell(new Label(8,i+2,fesOrder.getOrderNo(),wf));
wws.addCell(new Label(9,i+2,"",wf));
}
wwb.write();
wwb.close();
out.flush();
out.close();
download(req,res,fileName);
这个是我写的简单的导出excel

注意引入的都是poi的包，使用Cell，excel2003的.xls对应是HSSFCell，而之后的xlsx对应的则是XSSFCell，但是他们都继承于Cell，所以使用Cell就可以使用两种格式的excel导入了，下面解决excel中数据的各种格式
[java] view plaincopy
//读取excel
try {
request.setCharacterEncoding("gbk");
response.setContentType("text/html;charset=gbk");
// 1. 创建工厂类
DiskFileItemFactory factory = new DiskFileItemFactory();
// 2. 创建FileUpload对象
ServletFileUpload upload = new ServletFileUpload(factory);

// 3. 判断是否是上传表单
// boolean b = upload.isMultipartContent(request);
// 设置上传文件最大值
upload.setSizeMax(25 * 1024 * 1024);
// 是文件上传表单
// 4. 解析request，获得FileItem项
List<FileItem> fileitems = upload.parseRequest(request);
// 5. 遍历集合
for (FileItem item : fileitems) {
// 判断是不是普通字段
if (!item.isFormField()) {
// 获得流，读取数据写入文7a686964616fe58685e5aeb9333件
InputStream in = item.getInputStream();
Workbook book = createWorkBook(in,item.getName());
// 获得第一个工作表对象
Sheet sheet = book.getSheetAt(0);
if(0==sheet.getLastRowNum()){
//如果没有数据
request.setAttribute("message", "excel的sheet0中不存在数据");
request.getRequestDispatcher("/cc/util/excelToData.jsp").forward(request, response);
}

// 第一行为标题，从第二行开始录入
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
//标题行，用来对比方便得到数据
Row titleRow = sheet.getRow(0);
//数据行
Row row = sheet.getRow(i);
//获得值
String value_temp= this.getValue((Cell) row.getCell(2));
}
}
}

} catch (Exception e) {
e.printStackTrace();
message="导入失败<br/>"+message;
request.setAttribute("message",message);
request.getRequestDispatcher("/cc/util/excelToData.jsp").forward(request, response);

}
HSSFWorkbook wb = new HSSFWorkbook();
属 HSSFSheet sheet = wb.createSheet("new sheet");

HSSFRow row = sheet.createRow((short) 1);
HSSFCell cell = row.createCell((short) 1);

HSSFRichTextString content = new HSSFRichTextString("This is a test of message");
cell.setCellValue(content);

sheet.addMergedRegion(new Region(1,(short)1,1,(short)2));

FileOutputStream fileOut = new FileOutputStream("d:/Demo_02_05.xls");
wb.write(fileOut);
fileOut.close();

public static void drawExcel(HSSFWorkbook wb, String sheetName, String title, int n, List<?> exlList, int[] index){
e79fa5e98193e78988e69d83330List<Object[]> exList =(List<Object[]>)exlList;
int len = exList.get(0).length;
// 创建一个sheet表单
HSSFSheet sheet = wb.createSheet(sheetName);
Region region = null;
//样式
HSSFCellStyle cellStyle1 = setStyleBorder(wb);
HSSFCellStyle cellStyle2 = setStyleFontBorder(wb);
// 创建标题行
HSSFRow row = sheet.createRow(0);
row.setHeight((short)500);
// 创建单元格
HSSFCell cell = null;
if(title != null && !"".equals(title)){
region = new Region(0, (short)0, 0, (short)(len- 1));
sheet.addMergedRegion(region);

cell = row.createCell(0);
// 标题写入单元格
cell.setCellValue(title);
cell.setCellStyle(setStyleFontSize(wb, 18));
}else{
n = n - 1;
}

NumberFormat formatter = NumberFormat.getNumberInstance();
formatter.setMaximumFractionDigits(8);
//合计信息
Double[] sum = new Double[len];
Object[] s = null;
for(int i = 0; i < exList.size(); i++){
s = exList.get(i);
row = sheet.createRow(i + n);
// 创建数据行
for(int j = 0; j < s.length; j++){
cell = row.createCell(j);
/***
* jobin create
*/
if(s[j] instanceof Integer || s[j] instanceof Float){
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
}else{
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
}
if(!StringUtils.isNotEmpty(s[j])){
cell.setCellStyle(cellStyle1);
continue;
}
String[] rs = null;
int l = 3000;
if(i == 0 && s[j].toString().indexOf(",") > 0){
rs = s[j].toString().split(",");
cell.setCellValue(rs[0]);
l = Integer.parseInt(rs[1]);
}else{
cell.setCellValue(s[j].toString());
}
if(i == 0){
cell.setCellStyle(cellStyle2);
//设置列宽
// if(j == 0){
sheet.setColumnWidth(j, l);
// }else if(j == s.length -1){
// sheet.setColumnWidth(j, 5000);
// }else{
// sheet.setColumnWidth(j, 3000);
// }
}else{
cell.setCellStyle(cellStyle1);
//合计统计
if(index != null){
for(int in : index){
if(in == j){
if(sum[in] == null) sum[in] = 0.0;
sum[in] += Double.parseDouble(s[j].toString());
}
}
}
}
}
}
//合计信息
if(index != null){
region = new Region(exList.size() + 1, (short)0, exList.size() + 1, (short)(index[0]-1));
sheet.addMergedRegion(region);
row = sheet.createRow(exList.size() + n);
for(int i = 0; i < sum.length; i++){
cell = row.createCell(i);
cell.setCellStyle(cellStyle2);
if(i == 0){
cell.setCellValue("合计");
}else if(sum[i] != null){
cell.setCellValue(formatter.format(sum[i]).replace(",", ""));
}
}
}

}
核心代码，我项目上使用的