关于excel导入导出的问题

最近简单的导出excel
<%@ page language="java" import="java.util.*" pageEncoding="gbk"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body>
   <%
       response.reset();
       String fileSaveAsName = "myexcel.xls";
        fileSaveAsName = new String(fileSaveAsName.getBytes(),"ISO-8859-1");
        response.setHeader("Content-Disposition","attachment;filename=" + fileSaveAsName);
        response.setContentType ("application/vnd.ms-excel;charset=GBK");
        getServletContext().getRequestDispatcher("/Myhtml.html").include(request, response);
    %>
</body>
</html>
Myhtml.html就是excel显示的页面，可以先到数据库里执行查询，然后放入request里，在Myhtml.html展现！

最简单的就是事先用Excel做好你要导出的模板，然后存成HTML形式的
将HTML改成JSP或者Servlet，随你喜欢
最后就是想其他的Jsp或Servlet一样写就好了
不过这个要依赖客户端必须安装Excel的，要不会无法打开的

2楼的最简单。但是一定要把css都写到你的页面上。应为有时候可能会丢失css文件
jxl和poi可以做。但是可能样式非常麻烦public static String ExportResUser(ResuserModel rsm, HttpServletRequest request){
String filename =request.getRealPath("\\Template\\"+"ResUser.xls");
//filename="D:\\RQGLWorkspace\\rqgl\\WebRoot\\Template\\ResUser.xls";

try {
Workbook wb = Workbook.getWorkbook(new File(filename));
WritableWorkbook book=
Workbook.createWorkbook(new File(filename),wb);
//System.out.println(book.getNumberOfSheets());
WritableSheet ws = book.getSheet(0);

//设置Title行字体及背景色
    WritableFont wfont = new WritableFont(WritableFont.ARIAL, 12,
       WritableFont.BOLD, false,
                                          jxl.format.UnderlineStyle.NO_UNDERLINE,
                                          jxl.format.Colour.BLACK);
    // 大标题设置
    WritableCellFormat titleFormat = new WritableCellFormat();
    titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
    Label excelTitle3 = new Label(2, 0, CmUtil.null2String(rsm.getGasPermitNumber()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(4, 0, CmUtil.null2String(rsm.getContractNumber()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(2, 1, CmUtil.null2String(rsm.getAdmissibleNumber()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(4, 1, CmUtil.null2String(rsm.getApprovalPeople()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(2, 2, CmUtil.getYYYY_MM_DD(rsm.getProcessTime()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(4, 2, CmUtil.null2String(rsm.getOldGasNumber()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(2, 3, CmUtil.null2String(rsm.getErpUserNumber()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(4, 3, CmUtil.null2String(rsm.getOpenNoticeNumber()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(2, 4, CmUtil.getYYYY_MM_DD(rsm.getGasPermitDate()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(4, 4, CmUtil.null2String(WebUtil.getYesNo(rsm.getIsBiguser())), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(2, 5, CmUtil.null2String(rsm.getUserName1()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(2, 6, CmUtil.null2String(rsm.getUserUnitAddress()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(4, 6, CmUtil.null2String(rsm.getBusinessLicense()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(2, 7, CmUtil.null2String(rsm.getResponsePeople()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(4, 7, CmUtil.null2String(rsm.getResponsePhone()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(2, 8, CmUtil.null2String(rsm.getManagePeople()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(4, 8, CmUtil.null2String(rsm.getManagePhone()), titleFormat);
    ws.addCell(excelTitle3);

    excelTitle3 = new Label(2, 9, CmUtil.null2String(WebUtil.getOrgName(rsm.getGasUnit())), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(4, 9, CmUtil.null2String(rsm.getYearGas()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(2, 10, CmUtil.null2String(rsm.getFireNumber()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(4, 10, CmUtil.null2String(rsm.getGasAddress()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(2, 11, CmUtil.null2String(WebUtil.getUsageTypeName(rsm.getUsegasType())), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(4, 11, CmUtil.null2String(rsm.getUsegasPrice()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(2, 12, CmUtil.null2String(rsm.getUserNumber()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(2, 13, CmUtil.null2String(WebUtil.getYesNo(rsm.getHasGaswatch())), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(4, 13, CmUtil.null2String(rsm.getWatchInfo()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(2, 14, CmUtil.null2String(rsm.getWatchAddress()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(4, 14, CmUtil.null2String(WebUtil.getWatchTypeString(rsm.getWatchType())), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(2, 15, CmUtil.null2String(rsm.getWatchUseyear()), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(4, 15, CmUtil.null2String(WebUtil.getYesNo(rsm.getIsYearcheck())), titleFormat);
    ws.addCell(excelTitle3);
    excelTitle3 = new Label(1, 16, CmUtil.null2String(rsm.getOhterinfo()), titleFormat);
    ws.addCell(excelTitle3);
    //写入Exel工作表
    book.write();
    //关闭Excel工作薄对象
    book.close();

} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return filename;

}要分清楚行和列。

public static String ExportResUser(ResuserModel rsm, HttpServletRequest request){
        String filename =request.getRealPath("\\Template\\"+"ResUser.xls");
        //filename="D:\\RQGLWorkspace\\rqgl\\WebRoot\\Template\\ResUser.xls";

        try {
            Workbook wb = Workbook.getWorkbook(new File(filename));
            WritableWorkbook book=
                Workbook.createWorkbook(new File(filename),wb);
            //System.out.println(book.getNumberOfSheets());
            WritableSheet ws = book.getSheet(0);



            //设置Title行字体及背景色
            WritableFont wfont = new WritableFont(WritableFont.ARIAL, 12,
                                                  WritableFont.BOLD, false,
                                                  jxl.format.UnderlineStyle.NO_UNDERLINE,
                                                  jxl.format.Colour.BLACK);
            // 大标题设置
            WritableCellFormat titleFormat = new WritableCellFormat();
            titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
            Label excelTitle3 = new Label(2, 0, CmUtil.null2String(rsm.getGasPermitNumber()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(4, 0, CmUtil.null2String(rsm.getContractNumber()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(2, 1, CmUtil.null2String(rsm.getAdmissibleNumber()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(4, 1, CmUtil.null2String(rsm.getApprovalPeople()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(2, 2, CmUtil.getYYYY_MM_DD(rsm.getProcessTime()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(4, 2, CmUtil.null2String(rsm.getOldGasNumber()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(2, 3, CmUtil.null2String(rsm.getErpUserNumber()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(4, 3, CmUtil.null2String(rsm.getOpenNoticeNumber()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(2, 4, CmUtil.getYYYY_MM_DD(rsm.getGasPermitDate()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(4, 4, CmUtil.null2String(WebUtil.getYesNo(rsm.getIsBiguser())), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(2, 5, CmUtil.null2String(rsm.getUserName1()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(2, 6, CmUtil.null2String(rsm.getUserUnitAddress()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(4, 6, CmUtil.null2String(rsm.getBusinessLicense()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(2, 7, CmUtil.null2String(rsm.getResponsePeople()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(4, 7, CmUtil.null2String(rsm.getResponsePhone()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(2, 8, CmUtil.null2String(rsm.getManagePeople()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(4, 8, CmUtil.null2String(rsm.getManagePhone()), titleFormat);
            ws.addCell(excelTitle3);

            excelTitle3 = new Label(2, 9, CmUtil.null2String(WebUtil.getOrgName(rsm.getGasUnit())), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(4, 9, CmUtil.null2String(rsm.getYearGas()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(2, 10, CmUtil.null2String(rsm.getFireNumber()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(4, 10, CmUtil.null2String(rsm.getGasAddress()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(2, 11, CmUtil.null2String(WebUtil.getUsageTypeName(rsm.getUsegasType())), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(4, 11, CmUtil.null2String(rsm.getUsegasPrice()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(2, 12, CmUtil.null2String(rsm.getUserNumber()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(2, 13, CmUtil.null2String(WebUtil.getYesNo(rsm.getHasGaswatch())), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(4, 13, CmUtil.null2String(rsm.getWatchInfo()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(2, 14, CmUtil.null2String(rsm.getWatchAddress()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(4, 14, CmUtil.null2String(WebUtil.getWatchTypeString(rsm.getWatchType())), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(2, 15, CmUtil.null2String(rsm.getWatchUseyear()), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(4, 15, CmUtil.null2String(WebUtil.getYesNo(rsm.getIsYearcheck())), titleFormat);
            ws.addCell(excelTitle3);
            excelTitle3 = new Label(1, 16, CmUtil.null2String(rsm.getOhterinfo()), titleFormat);
            ws.addCell(excelTitle3);
            //写入Exel工作表
            book.write();
            //关闭Excel工作薄对象
            book.close();

        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (WriteException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }catch (BiffException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return filename;

    }

共享了一份 excel 导入 jsp 源码；一份 jsp 生成 excel 源码；一份 excel 导入数据库源码
至我的 CSDN 资源，下载后即可使用，注释的还是很详细的。楼主可以去看看，参考下

看看有没有帮助
public ActionForward importBillListOutExcel(ActionMapping mapping,
ActionForm form, HttpServletRequest request,
HttpServletResponse response) {

String beginTime = request.getParameter("beginTime");
String endTime = request.getParameter("endTime");
String warehouseId=request.getParameter("warehouseId");
String operatorId=request.getParameter("operatorId");
ImportNotifyBusiness importNotifyBusiness = new ImportNotifyBusiness();
List importBills = importNotifyBusiness.queryImportBillByWarehouseDate(warehouseId,
beginTime, endTime,operatorId);

try    {
            //  打开文件
File file=new File("test.xls");
            WritableWorkbook book  =  Workbook.createWorkbook(file);
            //  生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet  =  book.createSheet( " 第一页 " ,  0 );
            //表头

           Label[] h=new Label[6];
            h[1]=new Label(0,0,"单据ID号");
            h[2]=new Label(1,0,"单据类型");
            h[3]=new Label(2,0,"单据日期");
            h[4]=new Label(3,0,"操作员");
            h[5]=new Label(4,0,"执行标志");


            for(int i=1;i<=5;i++){
             sheet.addCell(h[i]);
            }
            //表体

            for(int i=0;i<importBills.size();i++)
            {
             ImportNotifyForm header=(ImportNotifyForm)importBills.get(i);

             Label[] ds=new Label[6];
           ds[1]=   new  Label(0,i+1,header.getImportId());
           ds[2]=   new  Label(1,i+1,header.getBillTypeForm().getBill_type_name());
           ds[3]=   new  Label(2,i+1,header.getImportDate());
           ds[4]=   new  Label(3,i+1,header.getSysUserByImportOperator().getUser_name());
           String executeFlag="";
            if(header.getImportExecuteFlag().equals("0")){
             executeFlag="未执行";
            }else if(header.getImportExecuteFlag().equals("1")){
             executeFlag="正在执行";
            }else if(header.getImportExecuteFlag().equals("2")){
             executeFlag="未执行";
            }
            ds[5]=   new  Label(4,i+1,executeFlag);
            //  将定义好的单元格添加到工作表中
           for(int col=1;col<=5;col++){
            sheet.addCell(ds[col]);
           }

            }
            book.write();
            book.close();            response.setHeader(" Content-disposition" ," attachment;   filename=test.xls");
            response.setHeader(" Content-Type" ,  " application/octet-stream" );

            BufferedInputStream  bis  =  null; //读excel
            BufferedOutputStream  bos  =  null; //输出

            try{
                    //读取excel文件
                    bis  =  new  BufferedInputStream(new  FileInputStream(file));
                    //写入response的输出流中
                    bos=new  java.io.BufferedOutputStream(response.getOutputStream());
                    byte[]  buff  =  new  byte[2048]; /*设置缓存*/
                    int  bytesRead;
                    while(-1!=  (bytesRead  =  bis.read(buff,  0,  buff.length))){
                            bos.write(buff,  0,  bytesRead);
                    }
            } catch(Exception  e){
                    e.printStackTrace();
            } finally{
                    if  (bis  !=  null)
                            try  {
                                    bis.close();
                            }   catch  (IOException  e)  {
                                    e.printStackTrace();
                            }
                    if  (bos  !=  null)
                            try  {
                                    bos.close();
                            }   catch  (IOException  e)  {
                                    e.printStackTrace();
                            }
            }
       }   catch  (Exception e)   {
           e.printStackTrace();
       } return null;
}

非常感谢楼上的各位朋友，现在我的根据大家的方法已经把导入做完了，现在把代码贴出，以方便以后的朋友来看
int rows = 0;
ArrayList list = null;// 每一行的通讯录
HashMap map = new HashMap();
ServletContext sc = null;
WebApplicationContext ctx = null;
DataSource ds = null;
Connection conn = null;
PreparedStatement pstmt = null;
SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
String[] tempDate = null;
Calendar cal = null;
Date newDate = null;
int year = 0;
int month = 0;
int date = 0;
String user_id = request.getParameter("user_id");
String unit_id = request.getParameter("unit_id");
String group_id = request.getParameter("group_id"); String sql = "INSERT INTO ADDRESS(ADD_ID,PSN_NAME,SEX,NICK_NAME,BIRTHDAY,MINISTRATION,MATE,CHILD,DEPT_NAME,ADD_DEPT,POST_NO_DEPT,TEL_NO_DEPT,FAX_NO_DEPT,ADD_HOME,POST_NO_HOME,TEL_NO_HOME,MOBIL_NO,BP_NO,EMAIL,OICQ_NO,ICQ_NO,NOTES,USER_ID,UNIT_ID,GROUP_ID) VALUES(NEXT VALUE FOR SEQ_ADDRESS,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; try {
if (upload != null && upload.size() > 0) {
sc = ServletActionContext.getServletContext();
ctx = WebApplicationContextUtils.getWebApplicationContext(sc);
ds = (DataSource) ctx.getBean("dataSource");
conn = ds.getConnection();
pstmt = conn.prepareStatement(sql); Workbook rwb = Workbook.getWorkbook(upload.get(0));// 通过上传找到工作簿 Sheet rs = rwb.getSheet(0);// 读取第一个工作簿 rows = rs.getRows() - 1; Cell c00;
for (int i = 1; i <= rows; i++) {//行
list = new ArrayList();
for (int j = 0; j < 21; j++) {
c00 = rs.getCell(j, i);
String strc00 = c00.getContents();
list.add(strc00);
if (j == 1) {
String sex = (String) list.get(j);
if (sex.equals("男")) {
sex = "1";
} else {
sex = "0";
} pstmt.setString(j + 1, sex);
} else if (j == 3) {
String date1 = (String) list.get(j); tempDate = date1.split("/");
date = Integer.parseInt(tempDate[0]);
month = Integer.parseInt(tempDate[1]);
year = Integer.parseInt(tempDate[2]);
String shijian = year + "-" + month + "-" + date; pstmt.setDate(j + 1, BannerUtil
.StringToSqlDate(shijian));
} else {
pstmt.setString(j + 1, (String) list.get(j));
} }
pstmt.setString(22, user_id);
pstmt.setString(23, unit_id);
pstmt.setString(24, group_id);
pstmt.addBatch(); int ii = pstmt.executeUpdate();
System.out.println("iiiiiiiiiiiiiiiiiiiiiiiiiii: " + ii); } rwb.close();
} } catch (Exception e) { e.printStackTrace();
}finally{
try{
pstmt.close();
conn.close();
}catch(SQLException e){
e.printStackTrace();
}
} return "success";

调试易

关于excel导入导出的问题

解决方案 »