bicashy

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<title.length;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();
	}
}