使用jxl对excel进行修改和删除

来源：互联网发布：优化社区党群服务中心编辑：程序博客网时间：2024/06/03 11:28

jsp页面代码：

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <script type="text/javascript" src="jquery-1.1.3.pack.js"></script>
</head>
<style type="text/css">
    .textTable{border:none;height:100%;}
</style>
<body>
    <div style="display:none">
        <table>
        <tr id="trBody">
            <td style="display:none" id="trCount"></td>
            <td id="tdBody1">0</td>
            <td id="tdBody2">1</td>
            <td id="tdBody3">2</td>
            <td id="tdBody4">3</td>
            <td id="tdBody5">4</td>
            <td id="tdBody6">5</td>
            <td id="tdBody7">6</td>
            <td id="tdBody8">7</td>
            <td id="tdBody9">删除</td>
        </tr>
        </table>
    </div>
    <table id="tableBody" border="1">
        <tr id="" style="">
            <th>0</th>
            <th>1</th>
            <th>2</th>
            <th>3</th>
            <th>4</th>
            <th>5</th>
            <th>6</th>
            <th>7</th>
            <th>操作</th>
        </tr>
    </table>
</body>
<script type="text/javascript">
        $(document).ready(function(){
            $.ajax({
                type:'POST',
                url:'<%=request.getContextPath()%>/ex_getExcelData.action',//访问后台方法获取数据
                success:function(msg){
                    //alert(msg);//后台传递过来的数据（数据格式：1,2,3,4,5,6,7,8:）

//使用text的ID来表示当前TEXT所对应的列
                    var array = msg.split(":");
                    for(var i=0;i<array.length;i++){
                        var trBody = $("#trBody").clone();
                        var array1 = array[i].split(",");
                        trBody.find("#trCount").html(i); //当前为第几行
                        trBody.find("#tdBody1").html("<input type='text' value='"+array1[0]+"' class='textTable' id='0' onfocus='getData(this)' onblur='update(this)'>");
                        trBody.find("#tdBody2").html("<input type='text' value='"+array1[1]+"' class='textTable' id='1' onfocus='getData(this)' onblur='update(this)'>");
                        trBody.find("#tdBody3").html("<input type='text' value='"+array1[2]+"' class='textTable' id='2' onfocus='getData(this)' onblur='update(this)'>");
                        trBody.find("#tdBody4").html("<input type='text' value='"+array1[3]+"' class='textTable' id='3' onfocus='getData(this)' onblur='update(this)'>");
                        trBody.find("#tdBody5").html("<input type='text' value='"+array1[4]+"' class='textTable' id='4' onfocus='getData(this)' onblur='update(this)'>");
                        trBody.find("#tdBody6").html("<input type='text' value='"+array1[5]+"' class='textTable' id='5' onfocus='getData(this)' onblur='update(this)'>");
                        trBody.find("#tdBody7").html("<input type='text' value='"+array1[6]+"' class='textTable' id='6' onfocus='getData(this)' onblur='update(this)'>");
                        trBody.find("#tdBody8").html("<input type='text' value='"+array1[7]+"' class='textTable' id='7' onfocus='getData(this)' onblur='update(this)'>");
                        trBody.find("#tdBody9").text("删除").bind("click",function(){del(this);});
                        trBody.attr("id","trBodyOne");
                        trBody.appendTo("#tableBody");
                    }
                }
            });


        });
        var textData = "";
        function getData(elem){
            textData = $(elem).val();
        }
        function update(elem){
            var closData = $(elem).val()
            if(closData != textData){
                var closnum = $(elem).attr("id");
                var rowsnum = $(elem).parent().parent().find("#trCount").html();
                if(closnum == ''){
                    closnum = '0';
                }
                if(rowsnum == ''){
                    rowsnum = '0';
                }
                //alert(closnum+"____"+rowsnum);
                $.ajax({
                    type:'POST',
                    url:'<%=request.getContextPath()%>/ex_updateData.action',//访问后台方法获取数据
                    data:'closnum='+closnum+'&rowsnum='+rowsnum+'&closData='+closData,
                    success:function(msg){

                    }
                });
            }
        }

        function del(elem){
            var num = $(elem).parent().find("#trCount").html();
            if(num == ''){
                num = '0';
            }
            $.ajax({
                type:'POST',
                url:'<%=request.getContextPath()%>/ex_deleteData.action',//访问后台方法获取数据
                data:'num='+num,
                success:function(msg){

                }
            });
            $(elem).parent().remove();
        }
</script>
</html>

//java类代码

package com.ot.excel.action;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.struts2.ServletActionContext;

public class ExcelAction {
    public String getExcelData() throws IOException
    {
        ServletActionContext.getResponse().setCharacterEncoding("utf-8");
        ServletActionContext.getResponse().setContentType("text/html;charset=utf-8");
        PrintWriter out = ServletActionContext.getResponse().getWriter();
        System.out.println(ServletActionContext.getRequest().getSession().getServletContext().getRealPath
                ("/"));
        String path = ServletActionContext.getRequest().getSession().getServletContext().getRealPath
        ("/")+"excel/00014.xls";//��excel�ļ�
        try {
           InputStream is = new FileInputStream(path);

           Workbook wb = Workbook.getWorkbook(is);

           Sheet st = wb.getSheet(0);

           System.out.println("�ñ文件有多少行：" + st.getRows() + "��");
           System.out.println("�ñ文件有多少列：" + st.getColumns() + "��");

           // System.out.println("��ȡ��ݣ�");

           boolean frist_o = true, frist_t = true, frist_s = true;
           StringBuffer excelContents = new StringBuffer("");
           for (int j = 0; j < st.getRows(); j++) {

            List list = new ArrayList();

            // int next = 0;
            // System.out.println();
            // System.out.println("��"+j+"�е��ǣ�");

            for (int i = 0; i < st.getColumns(); i++) {

             if (frist_o) {
              System.out.println("��ʼ��...");
              frist_o = false;
             }

             Cell cell = st.getCell(i, j);
             String contents = cell.getContents().trim();
             excelContents.append(contents+",");
            }
            excelContents.setCharAt(excelContents.length()-1, ':');
           }
           excelContents.setCharAt(excelContents.length()-1, ' ');
           String excelData = excelContents.toString();
           System.out.println(excelData);
           out.print(excelData);
           out.flush();
           out.close();
           is.close();
        }catch(Exception e){
            e.printStackTrace();
        }finally{

        }
        return null;
    }

    //删除excel表中的数据
    public String deleteData()
    {
        String path = ServletActionContext.getRequest().getSession().getServletContext().getRealPath
        ("/")+"excel/00014.xls";
        String num = (String) ServletActionContext.getRequest().getParameter("num");
        System.out.println(num);
        try {
            InputStream is = new FileInputStream(path);

            Workbook wb = Workbook.getWorkbook(is);//原xls文件
            WritableWorkbook wwb = Workbook.createWorkbook(new File(path), wb);//临时xls文件
            WritableSheet sheet = wwb.getSheet("00014");//工作表
            sheet.removeRow(Integer.parseInt(num));
            wwb.write();
            wwb.close();
            wb.close();
            is.close();
            System.out.println("删除完成.");
        } catch (Exception e) { // TODO Auto-generated
            e.printStackTrace();
        }
        return null;
    }

    //对excel表中的数据进行修改
    public String updateData()
    {
        String path = ServletActionContext.getRequest().getSession().getServletContext().getRealPath
        ("/")+"excel/00014.xls";
        String rowsnum = (String) ServletActionContext.getRequest().getParameter("rowsnum");
        String closnum = (String) ServletActionContext.getRequest().getParameter("closnum");
        String closData = (String) ServletActionContext.getRequest().getParameter("closData");
        System.out.println(rowsnum+"++++"+closnum);
        System.out.println(closData);
        try {
            Workbook wb = Workbook.getWorkbook(new File(path));//原xls文件
            WritableWorkbook wwb = Workbook.createWorkbook(new File(path), wb);//打开一个文件的副本，并且指定数据写回到原文件
            WritableSheet sheet = wwb.getSheet("00014");//工作表

            sheet.addCell(new   Label(Integer.parseInt(closnum),Integer.parseInt(rowsnum),closData));
            wwb.write();
            wwb.close();
            wb.close();
            System.out.println("更新完成.");
        } catch (Exception e) { // TODO Auto-generated
            e.printStackTrace();
        }
        return null;
    }
}

//需要jxl.jar的包，使用的是struts2框架