java struts jxl 导入导出Excel（无模板）

jar包：

import javax.servlet.http.HttpServletResponse;

import java.io.OutputStream;

import java.io.File;

import jxl.DateCell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.format.Alignment;

import jxl.format.Border;

import jxl.format.BorderLineStyle;

import jxl.format.VerticalAlignment;

import jxl.write.Label;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

一、导入

jsp：

<%@page import="com.sdfrdj.share.Constant"%>

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>

<%@ taglib prefix="s" uri="/struts-tags" %>

<html >

<head>

<title></title>

<link href="<%=request.getContextPath()%>/website/enroll/images/style.css" rel="stylesheet" type="text/css" />

<link href="<%=request.getContextPath()%>/comm_images/basic.css" rel="stylesheet" type="text/css"/>

<script type="text/javascript" src="<%=request.getContextPath()%>/js/jquery.min.js"></script>

<script src="<%=request.getContextPath() %>/js/main.js" type="text/javascript"></script>

</head>

<script type="text/javascript">

    function tips(){

        document.getElementById("tips").style.display="";

        return true;

    }

</script>

<body>

<s:form action="createTableAction!importTableDataList.action" theme="simple" id="commentForm" method="post" enctype="multipart/form-data" onsubmit="tips()" >

<table width="100%" border="0" cellspacing="0" cellpadding="0"  class="bg">

  <tr>

    <td ><table width="100%" border="0" cellspacing="0" cellpadding="0"  >

      <tr>

        <td id="qtKey" height="10"></td>

      </tr>

      <tr>

        <td><table width="98%" border="0" align="center" cellpadding="0" cellspacing="0" class="work_bg">

          <tr>

            <td class="titlebg"><table width="98%" border="0" align="center" cellpadding="0" cellspacing="0">

              <tr>

                <td class="font_title" align="center">导入</td>

              </tr>

            </table></td>

          </tr>

          <tr>

            <td  ><table width="100%" border="0" cellspacing="0" cellpadding="0">

              <tr>

                <td>

                    <table width="100%" border="0" cellspacing="0" cellpadding="0" class="tab_bg1">

                      <tr>

                          <td align="right" class="tab_bg2"><span class="font_red">*</span><label for="gender">自定义表信息导入</label>：</td>

                          <td align="left" >

                            <s:file type="file" name="excel"  cssClass="input02"></s:file>

                            <input name="createTableColumn.tableName" id="tableName" type="hidden" value="<s:property value ="createTableColumn.tableName"/>"/>

                          </td>

                       </tr>

                    </table>

                </td>

              </tr>

              <tr id="tips" style="display: none;">

                <td class="gray_bg" align="center" height="10">

                    <font color="red">正在导入中...时间较长,请耐心等待！</font>

                   </td>

              </tr>

              <tr>

                <td class="gray_bg" align="center" height="50"><input type="submit"  id="button3" value="提交" class="btn_blue"  />

                  &nbsp;

                  <input type="submit" name="button" id="button" value="关闭" class="btn_blue"  onclick="window.close();"/> </td>

              </tr>

                 <tr>

                <td style="color: red;">

                 <s:actionerror/>

                </td>

              </tr>

            </table></td>

          </tr>

        </table></td>

      </tr>

      <tr>

        <td height="10"></td>

      </tr>

    </table></td>

  </tr>

</table>

</s:form>

</body>

</html>

java：

private File excel;//导入文件

public File getExcel() {

    return excel;

}

public void setExcel(File excel) {

    this.excel = excel;

}

/**

 * 导入表数据

 * @return

 */

public String importTableDataList() {

    try {

        if (excel != null) {

            //获取已有的字段

            createTableColumnList = createTableService.selectCreateTableColumnInfo(createTableColumn);

            Map<String, String> columnMap = new HashMap<>();

            Map<String, String> dataTypeMap = new HashMap<>();

            for (CreateTableColumn createTableColumn : createTableColumnList) {

                columnMap.put(createTableColumn.getColumnComments(), createTableColumn.getColumnName());

                dataTypeMap.put(createTableColumn.getColumnComments(), createTableColumn.getDataType());

            }

            Workbook book = Workbook.getWorkbook(excel);

            Sheet sheet = book.getSheet(0);

            int clos = sheet.getColumns();//得到所有的列

            int rows = sheet.getRows();//得到所有的行

            if (clos < 1 || rows < 2) {

                this.getRequest().setAttribute("message","文件里面没有数据或者表头，请修改后再导入!");

                return "failJsp";

            }

            //得到表头

            String [] titles=new String[sheet.getColumns()];

            for (int j = 0; j < clos; j++) {

                titles[j] = sheet.getCell(j, 0).getContents();

            }

            //获取上传文件表头对应的数据库列名

            StringBuilder errorStr = new StringBuilder();

            StringBuilder columnNameSql = new StringBuilder();

            columnNameSql.append("COLUMN_ID");

            for (String title : titles) {

                String columnName = columnMap.get(title);

                if (StringUtil.isEmp(columnName)) {

                    errorStr.append("文件表头[").append(title).append("],");

                } else {

                    columnNameSql.append(",").append(columnName);

                }

            }

            if (errorStr.length() > 0) {

                errorStr.deleteCharAt(errorStr.length() - 1);

                errorStr.append("与数据库设置的字段名不对应，请修改后再导入!");

                this.getRequest().setAttribute("message",errorStr.toString());

                return "failJsp";

            } else {

                StringBuilder saveSql = null;

                StringBuilder columnValueSql = null;

                SimpleDateFormat dataFormat = new SimpleDateFormat("yyyy-MM-dd");

                for (int i = 1; i < rows; i++) {

                    saveSql = new StringBuilder();

                    saveSql.append("INSERT INTO ").append(createTableColumn.getTableName()).append("(");

                    columnValueSql = new StringBuilder();

                    columnValueSql.append("SEQ_CREATE_TABLE.NEXTVAL");

                    for (int j = 0; j < clos; j++) {

                        String columnValue = sheet.getCell(j, i).getContents();//第一个是列数，第二个是行数

                        if (StringUtil.isEmp(columnValue)) {

                            columnValueSql.append(",NULL");

                        } else {

                            if ("DATE".equals(dataTypeMap.get(titles[j]))) {

                                try {

                                    columnValue = dataFormat.format(((DateCell) sheet.getCell(j, i)).getDate());

                                    columnValueSql.append(",TO_DATE('").append(columnValue).append("','YYYY-MM-DD')");

                                } catch (Exception e) {

                                    columnValueSql.append(",TO_DATE('").append(columnValue).append("','YYYY-MM-DD')");

                                }

                            } else {

                                columnValueSql.append(",'").append(columnValue).append("'");

                            }

                        }

                    }

                    saveSql.append(columnNameSql).append(") VALUES(").append(columnValueSql).append(")");

                    Map<String, String> saveSqlMap = new HashMap<>();

                    saveSqlMap.put("CREATE_SQL", saveSql.toString());

                    createTableService.createTable(saveSqlMap);

                }

            }

        } else {

            this.getRequest().setAttribute("message",Message.TI_SHI_FAIL);

            return "failJsp";

        }

    } catch (Exception e) {

        e.printStackTrace();

        this.getRequest().setAttribute("message","保存失败，请检查数据的有效性！");

        return "failJsp";

    }

    this.getRequest().setAttribute("message", Message.TI_SHI_SUCCESS);

    return "successJsp";

}

二、导出

java：

/**

 * 导出表数据

 * @return

 */

public String exportTable() {

    try {

        HttpServletResponse response = ServletActionContext.getResponse();

        createTableService.selectExportTableData(createTableColumn, response);

    } catch (Exception e) {

        e.printStackTrace();

    }

    return null;

}

@Override

public List<Map<String, Object>> selectExportTableData(CreateTableColumn createTableColumn,

        HttpServletResponse response) throws Exception {

    CreateTable queryCreateTable = new CreateTable();

    queryCreateTable.setTableName(createTableColumn.getTableName());

    CreateTable createTable = createTableDao.selectCreateTable(queryCreateTable);

    String excelName = createTable.getTableComments() + "信息";

    List<CreateTableColumn> createTableColumnList = createTableDao.selectCreateTableColumnInfo(createTableColumn);

    Map<String, Object> paramsMap = new HashMap<>();

    paramsMap.put("tableName", createTableColumn.getTableName());

    List<Map<String, Object>> tableDataList = createTableDao.selectTableDataList(paramsMap);

    response.setHeader("Content-disposition",

            "attachment; filename=" + java.net.URLEncoder.encode(excelName+".xls", "utf-8") + ""); // 设定输出文件头

    response.setContentType("application/msexcel");// 定义输出类型

    OutputStream os = response.getOutputStream();

    this.createExcel(os, createTableColumnList, tableDataList, excelName);

    return tableDataList;

}

private void createExcel(OutputStream os, List<CreateTableColumn> createTableColumnList,

        List<Map<String, Object>> tableDataList, String excelName) {

    try {

        WritableWorkbook book = Workbook.createWorkbook(os);

        WritableFont font = new WritableFont(WritableFont.createFont("宋体_GB2312"), 9, WritableFont.NO_BOLD);// 带有自行的对象

        WritableCellFormat format = new WritableCellFormat(font);

        format.setAlignment(Alignment.CENTRE);// 设置水平对齐方式

        format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐

        format.setBorder(Border.ALL, BorderLineStyle.THIN);// 设置边框All代表所有和线条的样式

        format.setWrap(true);// 是否换行

        WritableSheet sheet = null;// 定义一个工作表

        sheet = book.createSheet(excelName, 0);// 创建一个工作表定义名称第一个

        sheet.setColumnView(0, 7);

        if (createTableColumnList != null && createTableColumnList.size() > 0) {

            for (int i = 0; i < createTableColumnList.size(); i++) {

                sheet.setColumnView(i + 1, 30);

            }

        }

        this.createTitle(sheet, createTableColumnList);

        if (tableDataList != null && tableDataList.size() > 0) {

            for (int i = 0; i < tableDataList.size(); i++) {

                Map<String, Object> map = tableDataList.get(i);

                sheet.addCell(new jxl.write.Number(0, i + 1, i + 1, format));// 序列（Number(列数,行数,序号,format)）

                int c = 0;

                for (CreateTableColumn createTableColumn : createTableColumnList) {

                    c++;

                    if (StringUtil.isEmp(map.get(createTableColumn.getColumnName()))) {

                        sheet.addCell(new Label(c, i + 1, "", format));

                    } else {

                        if ("DATE".equals(createTableColumn.getDataType())) {

                            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

                            sheet.addCell(new Label(c, i + 1, dateFormat.format(map.get(createTableColumn.getColumnName())), format));

                        } else {

                            sheet.addCell(new Label(c, i + 1, (map.get(createTableColumn.getColumnName())).toString(), format));

                        }

                    }

                }

            }

        }

        book.write();

        book.close();

    } catch (Exception e) {

        e.printStackTrace();

    }

}

private void createTitle(WritableSheet sheet, List<CreateTableColumn> createTableColumnList) throws  RowsExceededException, WriteException {

    //行说明标题

    WritableFont headFont=new WritableFont(WritableFont.createFont("宋体_GB2312"),12,WritableFont.BOLD);

    WritableCellFormat headFormat=new WritableCellFormat(headFont);

    headFormat.setAlignment(Alignment.CENTRE);

    headFormat.setVerticalAlignment(VerticalAlignment.CENTRE);

    headFormat.setBorder(Border.ALL,BorderLineStyle.THIN);

    headFormat.setWrap(true);

    headFormat.setVerticalAlignment(VerticalAlignment.CENTRE);

    Label label=new Label(0,0,"序号",headFormat);

    sheet.addCell(label);

    int i = 0;

    for (CreateTableColumn createTableColumn : createTableColumnList) {

        i++;

        label=new Label(i,0,createTableColumn.getColumnComments(),headFormat);

        sheet.addCell(label);

    }

}

巴特西

java struts jxl 导入导出Excel（无模板）

最新文章

热门文章