用jxl Workbook 创建,修改Excel表格
来源:互联网 发布:航天开票软件升级 编辑:程序博客网 时间:2024/05/22 15:13
package com.caitong.activity.action;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import org.apache.struts2.ServletActionContext;
import com.caitong.activity.util.DBUtil;
import com.opensymphony.xwork2.Action;
public class ExportExcelAction implements Action{
/*
* 生成excel文件
*/
public void createExcel() {
try{
HttpServletRequest req = ServletActionContext.getRequest();
HttpServletResponse response = ServletActionContext.getResponse();
req.setCharacterEncoding(“UTF-8”);
String title1 = req.getParameter(“fileName”);
//解决乱码
String downLoadName = new String(title1.getBytes(“gbk”), “iso8859-1”);
response.setContentType(“octets/stream”);
response.addHeader(“Content-Type”, “text/html; charset=utf-8”);
response.setHeader(“Content-Disposition”, “attachment;filename=” + downLoadName+”.xls”);
//创建输出流
OutputStream os = response.getOutputStream();
//创建工作簿
WritableWorkbook workbook = Workbook.createWorkbook(os);
//创建新的一页(说白了就是第一个表)
WritableSheet sheet = workbook.createSheet(“Sheet0”, 0);
//构造表头
//添加合并单元格,第一个参数是起始列,第二个参数是起始行,第三个参数是终止列,第四个参数是终止行
sheet.mergeCells(0, 0, 7, 0);
//设置字体的种类和黑体显示,字体为Arial字号的大小为10,采用黑体显示
WritableFont bold = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD);
//生成一个单元格样式控制对象
WritableCellFormat titleFormate = new WritableCellFormat(bold);
//单元格中的内容水平方向居中
titleFormate.setAlignment(jxl.format.Alignment.CENTRE);
//单元格中的内容垂直方向居中
titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label title = new Label(0, 0, title1, titleFormate);
//表头的内容填到设置的单元格中
sheet.addCell(title);
//设第一行的行高
sheet.setRowView(0, 600, false);
//设置单元格每一列的宽度
for (int i = 0; i <= 7; i++) {
sheet.setColumnView(i, 20);
}
//创建需要显示的具体内容
WritableFont color = new WritableFont(WritableFont.ARIAL);//选择字体
color.setColour(Colour.BLACK);//设置字体为BLACK颜色
WritableCellFormat colorFormat = new WritableCellFormat(color);
//终于开始写表头列
Label agentId = new Label(0,1,”站点编号”,colorFormat);
sheet.addCell(agentId);
Label phone = new Label(1,1,”手机号”,colorFormat);
sheet.addCell(phone);
Label userId = new Label(2,1,”身份证号”,colorFormat);
sheet.addCell(userId);
Label description = new Label(3,1,”奖金说明”,colorFormat);
sheet.addCell(description);
Label bonusDetial = new Label(4,1,”奖金金额”,colorFormat);
sheet.addCell(bonusDetial);
Label addTimestamp = new Label(5,1,”参与时间”,colorFormat);
sheet.addCell(addTimestamp);
Label prizeFlag = new Label(6,1,”兑奖状态”,colorFormat);
sheet.addCell(prizeFlag);
Label prizeTimestamp = new Label(7,1,”兑奖时间”,colorFormat);
sheet.addCell(prizeTimestamp);
//调用存储过程 //使用action上下文获取请求 HttpServletRequest request = ServletActionContext.getRequest(); //获取前台的的输入参数 String luckyDrawId = request.getParameter("luckyDrawId"); //获取数据库的链接 Connection conn = DBUtil.getConnection(); CallableStatement call = null; ResultSet rs = null; try { call = conn.prepareCall("{call PK_REPORT.Qr_Excel_Data(?,?)}"); call.setObject(1, luckyDrawId); call.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR); call.execute(); rs = (ResultSet) call.getObject(2); //设置价格(金额)的格式 java.text.DecimalFormat df = new java.text.DecimalFormat("##0.00"); //游标循环 int Row = 2; int a = 0; double b = 0.0; double c = 0.0; while(rs.next()){ Label agentId1 = new Label(0,Row,rs.getString("Agent_ID"),colorFormat); sheet.addCell(agentId1); Label phone1 = new Label(1,Row,rs.getString("Phone"),colorFormat); sheet.addCell(phone1); Label personalId = new Label(2,Row,rs.getString("Personal_ID"),colorFormat); sheet.addCell(personalId); Label description1 = new Label(3,Row,rs.getString("Description"),colorFormat); sheet.addCell(description1); Label bonusValue = new Label(4,Row,rs.getString("Bonus_Value")== null?"0":String.valueOf(df.format(Float.valueOf(rs.getString("Bonus_Value"))/100)),colorFormat); sheet.addCell(bonusValue); Label joinTimestamp = new Label(5,Row,rs.getString("Join_Timestamp"),colorFormat); sheet.addCell(joinTimestamp); Label prizeFlag1 = new Label(6,Row,rs.getString("Prize_Desc"),colorFormat); sheet.addCell(prizeFlag1); Label prizeTimestamp1 = new Label(7,Row,rs.getString("Prize_Timestamp"),colorFormat); sheet.addCell(prizeTimestamp1); Row = Row + 1; a=a+1; String flag = rs.getString("Prize_Flag"); b = b+ Double.parseDouble(rs.getString("Bonus_Value")== null?"0":String.valueOf(df.format(Float.valueOf(rs.getString("Bonus_Value"))/100))); if("1".equals(flag)) { c = c+ Double.parseDouble(rs.getString("Bonus_Value")== null?"0":String.valueOf(df.format(Float.valueOf(rs.getString("Bonus_Value"))/100))); } } Label Summary = new Label(0,Row, "合计:共" + String.valueOf(a) + "笔,其中应兑" + String.valueOf(b) + "元,实兑" + String.valueOf(c) + "元",colorFormat); sheet.addCell(Summary); }catch (Exception e) { e.printStackTrace(); } //把创建的内容写入到输出流中,并关闭输出流 workbook.write(); workbook.close(); os.flush(); os.close(); }catch(Exception e){ e.printStackTrace(); }}@Overridepublic String execute() throws Exception { // TODO Auto-generated method stub return null;}
}
- 用jxl Workbook 创建,修改Excel表格
- jxl 操作Excel表格之Workbook
- jxl 操作Excel表格之Workbook
- Java 导入 导出 excel jxl.Workbook
- JXL--workbook 集中创建方式+ 例子---->perfect
- 使用jxl工具包创建修改excel文件
- jxl简介,jxl操作excel 创建,读取,修改。
- jxl简介,jxl操作excel 创建,读取,修改。
- jxl操作excel表格
- jxl解析excel表格
- jxl解析excel表格
- Workbook POI 对Excel表格的读取
- java jxl创建/读取/修改Excel 操作Excel
- jxl 修改excel
- jxl 修改excel文件
- jxl创建excel
- jxl 创建EXCEL
- jxl创建excel
- Package name does not correspond to the file path......
- 华为推送 坑点 自定义intent
- python | gtts 将文字转化为语音内容
- 【C++】第4章 复合类型 知识总结
- 资讯精选 | 互联网+时代_企业如何进行研发效能体系快速升级?
- 用jxl Workbook 创建,修改Excel表格
- Deepin下安装nginx
- tf.pack()
- js 获取浏览器版本信息(全)
- 案例:Ajax站内搜索
- vijosp1071-01背包&记录组合&细节-新年趣事之打牌
- 【python学习笔记】Python中*args 和**kwargs的用法
- springmvc+shiro+maven 实现登录认证与权限授权管理 201
- select 1和select 0进行优化