excel 生成与解析工具类

最新推荐文章于 2024-04-19 10:06:26 发布

flanet

最新推荐文章于 2024-04-19 10:06:26 发布

阅读量441

点赞数

分类专栏： java 文章标签： excel exception string calendar iterator path

本文链接：https://blog.csdn.net/flanet/article/details/8052739

版权

java 专栏收录该内容

34 篇文章 0 订阅

订阅专栏

public class CvsUtils {

	/**
	 * function 生成账务明细报表
	 * 
	 * @param titles
	 * @param path
	 * @param accountDetailList
	 */
	public static boolean createAccountDetailExcel(String[] titles,
			String path, List<AccountDetail> accountDetailList) {

		boolean flag = true;

		try {

			WritableWorkbook wbook = Workbook
					.createWorkbook(new FileOutputStream(path)); // 建立excel文件

			WritableSheet wsheet = wbook.createSheet(Constants.CVS_SHEET_NAME,
					0); // 工作表名称

			// 设置Excel字体
			WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16,
					WritableFont.BOLD, false,
					jxl.format.UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			WritableCellFormat titleFormat = new WritableCellFormat(wfont);

			// 设置Excel表头
			for (int i = 0; i < titles.length; i++) {
				Label excelTitle = new Label(i, 0, titles[i], titleFormat);
				wsheet.addCell(excelTitle);
			}
			int c = 1; // 用于循环时Excel的行号
			Iterator it = accountDetailList.iterator();
			while (it.hasNext()) {
				AccountDetail accountDetail = (AccountDetail) it.next();
				Label content1 = new Label(0, c, accountDetail.getPaymentId());
				Label content2 = new Label(1, c, accountDetail.getPayTime()
						.toString());
				Label content3 = new Label(2, c, accountDetail
						.getTransactionType().toString());
				Label content4 = new Label(3, c, accountDetail.getPayer());
				Label content5 = new Label(4, c, accountDetail.getIncome()
						.toString());
				Label content6 = new Label(5, c, accountDetail.getExpenses()
						.toString());
				Label content7 = new Label(6, c, accountDetail.getPayment()
						.toString());

				wsheet.addCell(content1);
				wsheet.addCell(content2);
				wsheet.addCell(content3);
				wsheet.addCell(content4);
				wsheet.addCell(content5);
				wsheet.addCell(content6);
				wsheet.addCell(content7);
				c++;
			}
			wbook.write(); // 写入文件
			wbook.close();

		} catch (Exception e) {
			flag = false;
			System.out.println(e + "生成报表错误");
			e.printStackTrace();

		}

		return flag;

	}

	/**
	 * 导出为Excel
	 * 
	 * @param cdosCategoryKeyWords
	 * @return
	 * @throws Exception
	 */
	public static boolean exportExcel(HttpServletResponse response, String[] titles, List<AccountDetail> accountDetailList) {
		boolean flag = true;
		OutputStream os = null;
		try {
			os = response.getOutputStream();

			Calendar cal = Calendar.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
			String curDatetime = sdf.format(cal.getTime());
			response.reset();
			response.setHeader("Content-disposition",
					"attachment; filename=CategoryKeyWords" + curDatetime
							+ ".xls");
			response.setContentType("application/msexcel");

			WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件

			WritableSheet wsheet = wbook.createSheet(Constants.CVS_SHEET_NAME,
					0); // 工作表名称

			// 设置Excel字体
			WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16,
					WritableFont.BOLD, false,
					jxl.format.UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			WritableCellFormat titleFormat = new WritableCellFormat(wfont);

			// 设置Excel表头
			for (int i = 0; i < titles.length; i++) {
				Label excelTitle = new Label(i, 0, titles[i], titleFormat);
				wsheet.addCell(excelTitle);
			}

			// 设置Excel表头
			for (int i = 0; i < titles.length; i++) {
				Label excelTitle = new Label(i, 0, titles[i], titleFormat);
				wsheet.addCell(excelTitle);
			}
			int c = 1; // 用于循环时Excel的行号
			Iterator<AccountDetail> it = accountDetailList.iterator();
			while (it.hasNext()) {
				AccountDetail accountDetail = (AccountDetail) it.next();
				Label content1 = new Label(0, c, accountDetail.getPaymentId());
				Label content2 = new Label(1, c, accountDetail.getPayTime()
						.toString());
				Label content3 = new Label(2, c, accountDetail
						.getTransactionType().toString());
				Label content4 = new Label(3, c, accountDetail.getPayer());
				Label content5 = new Label(4, c, accountDetail.getIncome()
						.toString());
				Label content6 = new Label(5, c, accountDetail.getExpenses()
						.toString());
				Label content7 = new Label(6, c, accountDetail.getPayment()
						.toString());

				wsheet.addCell(content1);
				wsheet.addCell(content2);
				wsheet.addCell(content3);
				wsheet.addCell(content4);
				wsheet.addCell(content5);
				wsheet.addCell(content6);
				wsheet.addCell(content7);
				c++;
			}
			wbook.write(); // 写入文件
			wbook.close();
			os.close();
		} catch (Exception e) {
			 e.printStackTrace();
			 flag=false;
			 
		}

		return flag;

	}

}