jxl的简单使用

项目中做统计的时候有时需要生成统计execl报表，java生成execl可以通过jxl包来实现。很简单的说明一下其使用
    

package com.gdapp.oa.app;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
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.CellFormat;
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;

public class ExcelAPP
{
    private static void createTitle(WritableSheet wsheet) throws Exception
    {
        wsheet.addCell(new Label(0, 2, "信件号"));
        wsheet.addCell(new Label(1, 2, "信件类型"));
        wsheet.addCell(new Label(2, 2, "信件标题"));
        wsheet.addCell(new Label(3, 2, "信件内容"));
        wsheet.addCell(new Label(4, 2, "发信人"));
        wsheet.addCell(new Label(5, 2, "邮政编码"));
        wsheet.addCell(new Label(6, 2, "发信人地址"));
        wsheet.addCell(new Label(7, 2, "电话"));
        wsheet.addCell(new Label(8, 2, "发信人信箱"));
        wsheet.addCell(new Label(9, 2, "信件状态"));
        wsheet.addCell(new Label(10, 2, "转发/办理部门"));
        wsheet.addCell(new Label(11, 2, "来信时间"));
        wsheet.addCell(new Label(12, 2, "分办时间"));
        wsheet.addCell(new Label(13, 2, "办回时间"));
    }

    private static String toUtf8String(String s)
    {
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < s.length(); i++)
        {
            char c = s.charAt(i);
            if (c >= 0 && c <= 255)
            {
                sb.append(c);
            } else
            {
                byte[] b;
                try
                {
                    b = Character.toString(c).getBytes("utf-8");
                } catch (Exception ex)
                {
                    System.out.println(ex);
                    b = new byte[0];
                }
                for (int j = 0; j < b.length; j++)
                {
                    int k = b[j];
                    if (k < 0)
                        k += 256;
                    sb.append("%" + Integer.toHexString(k).toUpperCase());
                }
            }
        }
        return sb.toString();
    }

    //直接在本地生成文件
    static void writeXls()
    {

        System.out.println("writing exls.");
        WritableWorkbook wwb = null;
        WritableSheet sheet = null;
        try
        {
            String xlsName = "c:\\excel2.xls";
            File afile = new File(xlsName);
            if (!afile.exists())
            {
                afile.createNewFile();
            }
            wwb = Workbook.createWorkbook(afile);
            sheet = wwb.createSheet("bicashy的测试", 0); //创建标签页
            WritableFont wf = new WritableFont(WritableFont.TIMES, 10,
                    WritableFont.BOLD, false);
//          WritableFont wf = new WritableFont(WritableFont.ARIAL, 16,
//                  WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
//                  Colour.BLACK);
            WritableCellFormat format = new WritableCellFormat(wf);  //添加格式化样式
            format.setAlignment(Alignment.CENTRE);                   //设置水平对齐方式
            format.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置垂直对齐方式
            //remember,the first parameter is column index.
            Label cell = new Label(0, 0, "row 1,column 1", format);//set the value of cell (0,0)
            sheet.addCell(cell);
            cell = new Label(1, 0, "row 1,column 2", format);
            sheet.addCell(cell);
            cell = new Label(0, 1, "row 2,column 1", format);
            sheet.addCell(cell);
            createTitle(sheet);
            //将第四行合并前三列，行列默认都是从0开始的
            sheet.mergeCells(0,3,2,3);
            cell = new Label(0,3,"合并了三个单元格", format);
            sheet.addCell(cell);
            sheet.mergeCells(0,4,0,6);
            cell = new Label(0,4,"合并三列", format);
            sheet.addCell(cell);
            wwb.write();
            System.out.println("finished.");

        } catch (WriteException e)
        {
            e.printStackTrace();
        } catch (Exception e)
        {
            e.printStackTrace();
        } finally
        {
            if (wwb != null)
            {
                try
                {
                    wwb.close();
                } catch (Exception e1)
                {
                    e1.printStackTrace();
                }
            }
        }
    }

    //直接在本地生成统计文件
    static void writeXlsTJ()
    {
//       准备设置excel工作表的标题
        String[] title = {"编号","产品名称","产品价格","产品数量","生产日期","产地","是否出口"};
        try {
            // 获得开始时间
            long start = System.currentTimeMillis();
            // 输出的excel的路径
            String filePath = "c:\\test.xls";
            // 创建Excel工作薄
            WritableWorkbook wwb;
            // 新建立一个jxl文件,即在C盘下生成test.xls
            OutputStream os = new FileOutputStream(filePath);
            wwb=Workbook.createWorkbook(os);
            // 添加第一个工作表并设置第一个Sheet的名字
            WritableSheet sheet = wwb.createSheet("产品清单", 0);
            Label label;
            for(int i=0;i
                // Label(x,y,z)其中x代表单元格的第x+1列，第y+1行, 单元格的内容是y
                // 在Label对象的子对象中指明单元格的位置和内容
                label = new Label(i,0,title[i]);
                // 将定义好的单元格添加到工作表中
                sheet.addCell(label);
            }
            // 下面是填充数据
            /*
             * 保存数字到单元格，需要使用jxl.write.Number
             * 必须使用其完整路径，否则会出现错误
             * */
            // 填充产品编号
            jxl.write.Number number = new jxl.write.Number(0,1,20071001);
            sheet.addCell(number);
            // 填充产品名称
            label = new Label(1,1,"盐水鸭");
            sheet.addCell(label);
            /*
             * 定义对于显示金额的公共格式
             * jxl会自动实现四舍五入
             * 例如 22.456会被格式化为22.46,22.454会被格式化为22.45
             * */
            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
            jxl.write.WritableCellFormat wcf = new jxl.write.WritableCellFormat(nf);
            // 填充产品价格
            jxl.write.Number nb = new jxl.write.Number(2,1,22.45,wcf);
            sheet.addCell(nb);
            // 填充产品数量
            jxl.write.Number numb = new jxl.write.Number(3,1,200);
            sheet.addCell(numb);
            /*
             * 定义显示日期的公共格式
             * 如:yyyy-MM-dd hh:mm
             * */
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String newdate = sdf.format(new Date());
            // 填充出产日期
            label = new Label(4,1,newdate);
            sheet.addCell(label);
            // 填充产地
            label = new Label(5,1,"南京");
            sheet.addCell(label);
            /*
             * 显示布尔值
             * */
            jxl.write.Boolean bool = new jxl.write.Boolean(6,1,true);
            sheet.addCell(bool);
            /*
             * 合并单元格
             * 通过writablesheet.mergeCells(int x,int y,int m,int n);来实现的
             * 表示将从第x+1列，y+1行到m+1列，n+1行合并
             *
             * */
            sheet.mergeCells(0,3,2,3);
            label = new Label(0,3,"合并了三个单元格");
            sheet.addCell(label);
            /*
             *
             * 定义公共字体格式
             * 通过获取一个字体的样式来作为模板
             * 首先通过web.getSheet(0)获得第一个sheet
             * 然后取得第一个sheet的第二列，第一行也就是"产品名称"的字体
             * */
            CellFormat cf = wwb.getSheet(0).getCell(1, 0).getCellFormat();
            WritableCellFormat wc = new WritableCellFormat();
            // 设置居中
            wc.setAlignment(Alignment.CENTRE);
            // 设置边框线
            wc.setBorder(Border.ALL, BorderLineStyle.THIN);
            // 设置单元格的背景颜色
            wc.setBackground(jxl.format.Colour.RED);
            label = new Label(1,5,"字体",wc);
            sheet.addCell(label);

            // 设置字体
            jxl.write.WritableFont wfont = new jxl.write.WritableFont(WritableFont.createFont("隶书"),20);
            WritableCellFormat font = new WritableCellFormat(wfont);
            label = new Label(2,6,"隶书",font);
            sheet.addCell(label);

            // 写入数据
            wwb.write();
            // 关闭文件
            wwb.close();
            long end = System.currentTimeMillis();
            System.out.println("操作共用的时间:"+(end-start)+"毫秒");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //生成流，以便进行下载（用时可将流返回）
    static void writeXlsStream()
    {
        try
        {
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            WritableWorkbook wbook = Workbook.createWorkbook(baos);
            wbook.write();
            wbook.close();
            byte[] bs = baos.toByteArray();
            baos.close();
            //inputstream 可以用于返回
            InputStream inputstream = new ByteArrayInputStream(bs);
            String fileName = toUtf8String("数据统计.xls");
        } catch (WriteException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    //读本地文件
    static void readXls()
    {
        try
        {
            String filePath = "c:\\excel2.xls";
            InputStream is = new  FileInputStream(filePath);
            Workbook wb = Workbook.getWorkbook(is);   //得到工作薄
            Sheet sheet = wb.getSheet(0); //得到工作薄中的第一个工作表
            Cell cell = sheet.getCell(0,0);//得到工作表的第一个单元格,即A1
            String content=cell.getContents();//getContents()将Cell中的字符转为字符串
            System.out.println("A1内容=="+content);

            System.out.println(sheet.getCell(0, 1).getContents());
            System.out.println("总行数=="+sheet.getRows());
            System.out.println("总列数=="+sheet.getColumns());
            wb.close();//关闭工作薄
            is.close();//关闭输入流

        } catch (Exception e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void main(String[] args)
    {
        //writeXls();
        readXls();
    }
}

package com.gdapp.oa.app;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
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.CellFormat;
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;

public class ExcelAPP
{
	private static void createTitle(WritableSheet wsheet) throws Exception
	{
		wsheet.addCell(new Label(0, 2, "信件号"));
		wsheet.addCell(new Label(1, 2, "信件类型"));
		wsheet.addCell(new Label(2, 2, "信件标题"));
		wsheet.addCell(new Label(3, 2, "信件内容"));
		wsheet.addCell(new Label(4, 2, "发信人"));
		wsheet.addCell(new Label(5, 2, "邮政编码"));
		wsheet.addCell(new Label(6, 2, "发信人地址"));
		wsheet.addCell(new Label(7, 2, "电话"));
		wsheet.addCell(new Label(8, 2, "发信人信箱"));
		wsheet.addCell(new Label(9, 2, "信件状态"));
		wsheet.addCell(new Label(10, 2, "转发/办理部门"));
		wsheet.addCell(new Label(11, 2, "来信时间"));
		wsheet.addCell(new Label(12, 2, "分办时间"));
		wsheet.addCell(new Label(13, 2, "办回时间"));
	}

	private static String toUtf8String(String s)
	{
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < s.length(); i++)
		{
			char c = s.charAt(i);
			if (c >= 0 && c <= 255)
			{
				sb.append(c);
			} else
			{
				byte[] b;
				try
				{
					b = Character.toString(c).getBytes("utf-8");
				} catch (Exception ex)
				{
					System.out.println(ex);
					b = new byte[0];
				}
				for (int j = 0; j < b.length; j++)
				{
					int k = b[j];
					if (k < 0)
						k += 256;
					sb.append("%" + Integer.toHexString(k).toUpperCase());
				}
			}
		}
		return sb.toString();
	}

	//直接在本地生成文件
	static void writeXls()
	{

		System.out.println("writing exls.");
		WritableWorkbook wwb = null;
		WritableSheet sheet = null;
		try
		{
			String xlsName = "c:\\excel2.xls";
			File afile = new File(xlsName);
			if (!afile.exists())
			{
				afile.createNewFile();
			}
			wwb = Workbook.createWorkbook(afile);
			sheet = wwb.createSheet("bicashy的测试", 0); //创建标签页
			WritableFont wf = new WritableFont(WritableFont.TIMES, 10,
					WritableFont.BOLD, false);
//			WritableFont wf = new WritableFont(WritableFont.ARIAL, 16,
//					WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
//					Colour.BLACK);
			WritableCellFormat format = new WritableCellFormat(wf);  //添加格式化样式
			format.setAlignment(Alignment.CENTRE);                   //设置水平对齐方式
			format.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置垂直对齐方式
			//remember,the first parameter is column index.
			Label cell = new Label(0, 0, "row 1,column 1", format);//set the value of cell (0,0)
			sheet.addCell(cell);
			cell = new Label(1, 0, "row 1,column 2", format);
			sheet.addCell(cell);
			cell = new Label(0, 1, "row 2,column 1", format);
			sheet.addCell(cell);
			createTitle(sheet);
			//将第四行合并前三列，行列默认都是从0开始的
			sheet.mergeCells(0,3,2,3);  
			cell = new Label(0,3,"合并了三个单元格", format); 
			sheet.addCell(cell);
			sheet.mergeCells(0,4,0,6);  
			cell = new Label(0,4,"合并三列", format); 
            sheet.addCell(cell);  
			wwb.write();
			System.out.println("finished.");

		} catch (WriteException e)
		{
			e.printStackTrace();
		} catch (Exception e)
		{
			e.printStackTrace();
		} finally
		{
			if (wwb != null)
			{
				try
				{
					wwb.close();
				} catch (Exception e1)
				{
					e1.printStackTrace();
				}
			}
		}
	}

    //直接在本地生成统计文件
	static void writeXlsTJ()
	{
//		 准备设置excel工作表的标题
		String[] title = {"编号","产品名称","产品价格","产品数量","生产日期","产地","是否出口"};
		try {
			// 获得开始时间
			long start = System.currentTimeMillis();
			// 输出的excel的路径
			String filePath = "c:\\test.xls";
			// 创建Excel工作薄
			WritableWorkbook wwb;
			// 新建立一个jxl文件,即在C盘下生成test.xls
			OutputStream os = new FileOutputStream(filePath);
			wwb=Workbook.createWorkbook(os); 
			// 添加第一个工作表并设置第一个Sheet的名字
			WritableSheet sheet = wwb.createSheet("产品清单", 0);
			Label label;
			for(int i=0;iwritablesheet.mergeCells(int x,int y,int m,int n);来实现的
			 * 表示将从第x+1列，y+1行到m+1列，n+1行合并
			 * 
			 * */
			sheet.mergeCells(0,3,2,3);
			label = new Label(0,3,"合并了三个单元格");
			sheet.addCell(label);
			/*
			 * 
			 * 定义公共字体格式
			 * 通过获取一个字体的样式来作为模板
			 * 首先通过web.getSheet(0)获得第一个sheet
			 * 然后取得第一个sheet的第二列，第一行也就是"产品名称"的字体 
			 * */
			CellFormat cf = wwb.getSheet(0).getCell(1, 0).getCellFormat();
			WritableCellFormat wc = new WritableCellFormat();
			// 设置居中
			wc.setAlignment(Alignment.CENTRE);
			// 设置边框线
			wc.setBorder(Border.ALL, BorderLineStyle.THIN);
			// 设置单元格的背景颜色
			wc.setBackground(jxl.format.Colour.RED);
			label = new Label(1,5,"字体",wc);
			sheet.addCell(label);

			// 设置字体
			jxl.write.WritableFont wfont = new jxl.write.WritableFont(WritableFont.createFont("隶书"),20);
			WritableCellFormat font = new WritableCellFormat(wfont);
			label = new Label(2,6,"隶书",font);
			sheet.addCell(label);
			
			// 写入数据
			wwb.write();
			// 关闭文件
			wwb.close();
			long end = System.currentTimeMillis();
			System.out.println("操作共用的时间:"+(end-start)+"毫秒");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//生成流，以便进行下载（用时可将流返回）
	static void writeXlsStream()
	{
		try
		{
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			WritableWorkbook wbook = Workbook.createWorkbook(baos);
			wbook.write();
			wbook.close();
			byte[] bs = baos.toByteArray();
			baos.close();
			//inputstream 可以用于返回
			InputStream inputstream = new ByteArrayInputStream(bs);
			String fileName = toUtf8String("数据统计.xls");
		} catch (WriteException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//读本地文件
	static void readXls()
	{
		try
		{
			String filePath = "c:\\excel2.xls";
			InputStream is = new  FileInputStream(filePath);
			Workbook wb = Workbook.getWorkbook(is);   //得到工作薄  
			Sheet sheet = wb.getSheet(0); //得到工作薄中的第一个工作表
			Cell cell = sheet.getCell(0,0);//得到工作表的第一个单元格,即A1
			String content=cell.getContents();//getContents()将Cell中的字符转为字符串
			System.out.println("A1内容=="+content);
			
			System.out.println(sheet.getCell(0, 1).getContents());
			System.out.println("总行数=="+sheet.getRows());
			System.out.println("总列数=="+sheet.getColumns());
			wb.close();//关闭工作薄
			is.close();//关闭输入流
			
		} catch (Exception e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args)
	{
		//writeXls();
		readXls();
	}
}

需要的jxl.ar
   

• (669.4 KB)
• 下载次数: 18