JAVA使用JXL操作Excel

~~

 

code:

 

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class JxlTest
{

    public static void main(String[] args)
    {
        List<String[]> list = readExcel("bbb.xls");

        for (String[] s : list)
        {
            // System.out.println(Arrays.asList(s).toString());
        }

        toExcelTest("ccc.xls");

    }

    /**
     * 用COMMON UPLOAD进行EXCEL文件上传,得到fileItem对象，这里 进行解析，返回集合对象。该方法适合在WEB项目中使用。
     * 
     * @param fileItem
     * @param beginIndex
     *            正式数据的起始行 例如EXCEL文件 有大标题和小标题和列标题，那么该参数应为 4
     * @return
     * @throws BiffException
     * @throws IOException
     */
    public static List<String[]> readExcel(String fileName)
    {

        int beginIndex = 1;
        // 保存结果集
        List<String[]> result = null;
        // 保存EXCEL每行的所有单元格中的数据
        String[] temp = null;
        try
        {
            File file = new File(fileName);
            Workbook workBook = Workbook.getWorkbook(file);
            Sheet sheet = workBook.getSheet(0);
            Cell cell = null;
            int rowSize = sheet.getRows();
            int colSize = sheet.getColumns();

            result = new ArrayList<String[]>();
            for (int i = beginIndex - 1; i < rowSize; i++)
            {
                // 保存EXCEL每行的所有单元格中的数据
                temp = new String[colSize];
                for (int t = 0; t < colSize; t++)
                {
                    cell = sheet.getCell(t, i);
                    String content = "";
                    if (cell.getContents() != null)
                    {
                        // 去空格，特殊字符和回车键盘
                        content = cell.getContents().replace("%", "").replace("|", "").replace(" ", "").replaceAll(
                                "\\n", "").replaceAll("\\r", "").trim();

                    }
                    temp[t] = content;
                }
                // 将每列的的数据存入结果集中
                result.add(temp);
            }
        } catch (Exception ex)
        {
            ex.printStackTrace();
        }
        return result;
    }

    /**
     * 
     * 生成excel
     * 
     * @param fileName
     * @author
     * @date
     */
    public static void toExcelTest(String fileName)
    {

        // 初始化工作表
        WritableWorkbook workbook = null;
        try
        {

            // 设置弹出对话框

            // 创建工作表
            workbook = Workbook.createWorkbook(new File(fileName));

            // 定义工作表 sheet 标题
            WritableSheet ws = workbook.createSheet("Mapping", 0);
            ws.getSettings().setShowGridLines(true);
            ws.getSettings().setProtected(false);

            // 控制列的宽度,如果你要不给一样的宽度,就单独写,i代表的是列的下标,从0开始 ,从左到右
            for (int i = 0; i < 7; i++)
            {
                ws.setColumnView(i, 20);
            }

            // 創建标题列名称
            Label titleLabel = null;
            titleLabel = new Label(0, 0, "StuNo", getHeadFormat());
            ws.addCell(titleLabel);
            titleLabel = new Label(1, 0, "StuName", getHeadFormat());
            ws.addCell(titleLabel);
            titleLabel = new Label(2, 0, "StuMoniter", getHeadFormat());
            ws.addCell(titleLabel);
            titleLabel = new Label(3, 0, "StuAddr", getHeadFormat());
            ws.addCell(titleLabel);
            titleLabel = new Label(4, 0, "Stu TELL", getHeadFormat());
            ws.addCell(titleLabel);
            titleLabel = new Label(5, 0, "Stu sex", getHeadFormat());
            ws.addCell(titleLabel);
            titleLabel = new Label(6, 0, "Stu classNo", getHeadFormat());
            ws.addCell(titleLabel);

            // 循环添加对象数据
            for (int i = 0; i < 2; i++)
            {

                Label stuNoLabel = new Label(0, i + 1, "stuNo");
                Label stuNameLabel = new Label(1, i + 1, "stuName");
                Label stuminterLabel = new Label(2, i + 1, "stuminter");
                Label stuAddrLable = new Label(3, i + 1, "stuAddr");
                // Label stuTellLabel = new Label(4, i + 1, stutell);
                // 如果用这个导出后会有数值和文本的区别
                jxl.write.Number tellNumber = new jxl.write.Number(4, i + 1, Double.parseDouble("23"));
                Label stusexLabel = new Label(5, i + 1, "stusex");
                // Label classNoLabel = new Label(6, i + 1,stuClassno);
                // 一下要注意了,必须要转成double类型的数据
                jxl.write.Number classNoNumber = new jxl.write.Number(6, i + 1, Double.parseDouble("36"));

                ws.addCell(stuNoLabel);
                ws.addCell(stuNameLabel);
                ws.addCell(stuminterLabel);
                ws.addCell(stuAddrLable);
                ws.addCell(tellNumber);
                ws.addCell(stusexLabel);
                ws.addCell(classNoNumber);
            }

            workbook.write();
            workbook.close();
        } catch (Exception e)
        {
            System.out.println(e.getCause());
            System.out.println(e.getMessage());
        }
    }

    /**
     * 设置单元格样式
     * 
     * @return
     * @throws Exception
     */
    public static WritableCellFormat getHeadFormat() throws Exception
    {
        // 设置字体
        WritableFont wf = new WritableFont(WritableFont.ARIAL, 8, WritableFont.BOLD);

        // 创建单元格FORMAT
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setAlignment(Alignment.CENTRE);
        wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
        wcf.setLocked(true);
        wcf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
        wcf.setBackground(Colour.GREY_25_PERCENT);
        return wcf;
    }
}