/**
*
*/
package com.chinaops.web.ydgd.servlet;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Alignment;
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.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.chinaops.web.ydgd.entity.Customer;
import com.chinaops.web.ydgd.entity.Order;
import com.chinaops.web.ydgd.entity.Ticket;
import com.chinaops.web.ydgd.service.CustomerService;
import com.chinaops.web.ydgd.service.OrderService;
import com.chinaops.web.ydgd.service.TicketService;
import com.chinaops.web.ydgd.utils.Suite;
/**
* @author tomhat
* 2014年9月1日 上午9:34:38
*/
public class ExportExcelServlet extends HttpServlet {
private static final Log log = LogFactory.getLog(ExportExcelServlet.class);
ApplicationContext context = new ClassPathXmlApplicationContext("spring-dao.xml");
TicketService ticketService = (TicketService) context.getBean("ticketService");
CustomerService customerService = (CustomerService) context.getBean("customerService");
OrderService orderService = (OrderService) context.getBean("orderService");
String targetfile = "D:/资源.xls";// 输出的excel文件名
String[] worksheet = { "共享云", "专享云", "云存储" };// 输出的excel文件工作表名
String[] title = { "云平台", "开通时间", "资源详情" };// excel工作表的标题
WritableWorkbook workbook;
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String customerId = request.getParameter("customerId");
try {
Customer customer = customerService.getCustomerBycustomerId(customerId);
String osSys = System.getProperty("os.name");
// if (StringUtils.isNotEmpty(os) && os.startsWith("Windows"))
// return "f:" + File.separatorChar + "tuts" + File.separatorChar;
// else
// return File.separatorChar + "tuts" + File.separatorChar;
String path = request.getSession().getServletContext().getRealPath("/");
String filename = path + "/excel/" + customer.getCustomerName() + "-资源.xls";
OutputStream os = new FileOutputStream(filename);
workbook = Workbook.createWorkbook(os);
List<Ticket> ticketList = ticketService.getOpenTicketByCustomerId(customerId);
String ticketIdS = new String();
if (ticketList.size() > 0) {
for (Ticket ticket : ticketList) {
ticketIdS += "'" + ticket.getTicketId() + "',";
}
}
String ticketIds = "";
if (ticketIdS.indexOf(",") > 0) {
ticketIds = ticketIdS.substring(0, ticketIdS.length() - 1);
}
List<Order> productTypeList = new ArrayList<Order>();
List<Order> productList = new ArrayList<Order>();
if (!ticketIds.equals("") && ticketIds != null) {
productTypeList = orderService.getProductTypes(ticketIds);
}
if (productTypeList != null && productTypeList.size() > 0) {
for (int i = 0; i < productTypeList.size(); i++) {
WritableSheet sheet = workbook.createSheet(worksheet[i], i);
WritableFont wftitle = new WritableFont(WritableFont.TIMES, 25, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat wcfFtitle = new WritableCellFormat(wftitle);
wcfFtitle.setBackground(Colour.GRAY_25);
wcfFtitle.setAlignment(Alignment.LEFT);
sheet.mergeCells(0, 0, 2, 0);
sheet.setRowView(0, 600);
Label labeltitle = new Label(0, 0, "客户信息", wcfFtitle);
sheet.addCell(labeltitle);
WritableFont wfbase = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat wcfFbase = new WritableCellFormat(wfbase);
wcfFbase.setAlignment(Alignment.LEFT);
sheet.mergeCells(0, 1, 2, 1);
sheet.setRowView(1, 500);
Label labelbase = new Label(0, 1, "基本信息", wcfFbase);
sheet.addCell(labelbase);
WritableFont wfright = new WritableFont(WritableFont.TAHOMA, 12, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat wcfFright = new WritableCellFormat(wfright);
wcfFright.setAlignment(Alignment.RIGHT);
Label labelcustomerName = new Label(0, 2, "客户名称:", wcfFright);
sheet.addCell(labelcustomerName);
Label hangye = new Label(0, 3, "所属行业:", wcfFright);
sheet.addCell(hangye);
Label dizhi = new Label(0, 4, "地址:", wcfFright);
sheet.addCell(dizhi);
Label youbian = new Label(0, 5, "邮编:", wcfFright);
sheet.addCell(youbian);
WritableFont wfleft = new WritableFont(WritableFont.TAHOMA, 12, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat wcfleft = new WritableCellFormat(wfleft);
wcfleft.setAlignment(Alignment.LEFT);
sheet.mergeCells(1, 2, 2, 2);
Label labelcustomerName1 = new Label(1, 2, customer.getCustomerName(), wcfleft);
sheet.addCell(labelcustomerName1);
sheet.mergeCells(1, 3, 2, 3);
Label hangye1 = new Label(1, 3, customer.getCategory(), wcfleft);
sheet.addCell(hangye1);
sheet.mergeCells(1, 4, 2, 4);
Label dizhi1 = new Label(1, 4, customer.getCustomerAddress(), wcfleft);
sheet.addCell(dizhi1);
sheet.mergeCells(1, 5, 2, 5);
Label youbian1 = new Label(1, 5, customer.getPostalCode(), wcfleft);
sheet.addCell(youbian1);
WritableFont wfcontact = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat wcfFcontact = new WritableCellFormat(wfcontact);
wcfFcontact.setAlignment(Alignment.LEFT);
sheet.mergeCells(0, 6, 2, 6);
sheet.setRowView(6, 500);
Label labelcontact = new Label(0, 6, "联系人", wcfFcontact);
sheet.addCell(labelcontact);
Label yewulianxiren = new Label(0, 7, "业务联系人:", wcfFright);
sheet.addCell(yewulianxiren);
Label yewuPhone = new Label(0, 8, "电话:", wcfFright);
sheet.addCell(yewuPhone);
Label yewuEmail = new Label(0, 9, "邮箱:", wcfFright);
sheet.addCell(yewuEmail);
Label jishulianxiren = new Label(0, 10, "技术联系人:", wcfFright);
sheet.addCell(jishulianxiren);
Label jishuPhone = new Label(0, 11, "电话:", wcfFright);
sheet.addCell(jishuPhone);
Label jishuEmail = new Label(0, 12, "邮箱:", wcfFright);
sheet.addCell(jishuEmail);
sheet.mergeCells(1, 7, 2, 7);
Label yewu = new Label(1, 7, customer.getB_contact(), wcfleft);
sheet.addCell(yewu);
sheet.mergeCells(1, 8, 2, 8);
Label ywPhone = new Label(1, 8, customer.getB_phone(), wcfleft);
sheet.addCell(ywPhone);
sheet.mergeCells(1, 9, 2, 9);
Label ywEmail = new Label(1, 9, customer.getB_email(), wcfleft);
sheet.addCell(ywEmail);
sheet.mergeCells(1, 10, 2, 10);
Label jishu = new Label(1, 10, customer.getT_contact(), wcfleft);
sheet.addCell(jishu);
sheet.mergeCells(1, 11, 2, 11);
Label jsPhone = new Label(1, 11, customer.getT_phone(), wcfleft);
sheet.addCell(jsPhone);
sheet.mergeCells(1, 12, 2, 12);
Label jsEmail = new Label(1, 12, customer.getT_email(), wcfleft);
sheet.addCell(jsEmail);
sheet.setRowView(2, 400);
sheet.setRowView(3, 400);
sheet.setRowView(4, 400);
sheet.setRowView(5, 400);
sheet.setRowView(7, 400);
sheet.setRowView(8, 400);
sheet.setRowView(9, 400);
sheet.setRowView(10, 400);
sheet.setRowView(11, 400);
sheet.setRowView(12, 400);
for (int j = 0; j < title.length; j++) {
WritableFont wf = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat wcfF = new WritableCellFormat(wf);
wcfF.setBackground(Colour.GRAY_25);
wcfF.setAlignment(Alignment.CENTRE);
// Label(列号,行号 ,内容 )
Label label = new Label(j, 14, title[j], wcfF); // put
// the
// title
// in
sheet.addCell(label);
}
sheet.setColumnView(0, 15);
sheet.setColumnView(1, 20);
sheet.setColumnView(2, 50);
sheet.setRowView(14, 600);
productList = orderService.getOrderByProductType(ticketIds, productTypeList.get(i).getProductType());
if (productList != null && productList.size() > 0) {
int s = 5;
for (int k = 0; k < productList.size(); k++) {
Order order = productList.get(k);
sheet.setRowView(s * k + 14 + 1, 500);
sheet.setRowView(s * k + 14 + 2, 500);
sheet.setRowView(s * k + 14 + 3, 500);
sheet.setRowView(s * k + 14 + 4, 500);
sheet.setRowView(s * k + 14 + 5, 500);
sheet.mergeCells(0, s * k + 14 + 1, 0, s * k + 14 + 5);
sheet.mergeCells(1, s * k + 14 + 1, 1, s * k + 14 + 5);
WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREEN);
jxl.write.WritableCellFormat wchB = new jxl.write.WritableCellFormat(wfc);
wchB.setAlignment(jxl.format.Alignment.CENTRE);
wchB.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wchB.setBorder(Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.GRAY_25);
Label CloudPlatform = new jxl.write.Label(0, s * k + 14 + 1, order.getCloudPlatform(), wchB);
sheet.addCell(CloudPlatform);
Label OpenTime = new jxl.write.Label(1, s * k + 14 + 1, order.getOpenTime(), wchB);
sheet.addCell(OpenTime);
WritableFont wfbleft = new jxl.write.WritableFont(WritableFont.TAHOMA, 10, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE);
jxl.write.WritableCellFormat wchleft = new jxl.write.WritableCellFormat(wfbleft);
wchleft.setAlignment(jxl.format.Alignment.LEFT);
wchleft.setBorder(Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.GRAY_25);
if (order.getProductType().equals("share")) {
Label ecu = new jxl.write.Label(2, s * k + 14 + 1, "ECU:" + Suite.suiteShare(order.getSuite())[0] + "个\n\t 负载均衡:"
+ ((order.getElb() != null && !order.getElb().equals("")) ? order.getElb() : 0) + "个", wchleft);
sheet.addCell(ecu);
} else if (order.getProductType().equals("exclusive")) {
Label ecu = new jxl.write.Label(2, s * k + 14 + 1, "ECU:" + Suite.suiteExclusive(order.getSuite())[0] + "个\n\t 负载均衡:"
+ ((order.getElb() != null && !order.getElb().equals("")) ? order.getElb() : '0') + "个", wchleft);
sheet.addCell(ecu);
}// 存储:200 GB 主机保护:0 个
Label volume = new jxl.write.Label(2, s * k + 14 + 2, "存储:" + (Integer.parseInt(order.getStorage()) + Integer.parseInt(Suite.suiteShare(order.getSuite())[2]))
+ "GB [含赠送存储:" + Suite.suiteShare(order.getSuite())[2] + "GB]\t 主机保护:" + ((!order.getHa().equals("") || order.getHa() != null) ? order.getHa() : 0) + "个", wchleft);
sheet.addCell(volume);
// 快照:200 GB 防火墙:0 个
Label snapshot = new jxl.write.Label(2, s * k + 14 + 3, "快照:" + (order.getSnapshot()) + "GB\n\t 防火墙:"
+ ((!order.getSecuritys().equals("") && order.getSecuritys() != null) ? order.getSecuritys() : 0) + "个", wchleft);
sheet.addCell(snapshot);
// 带宽:0 M 秘钥:0 个
Label bankwidth = new jxl.write.Label(2, s * k + 14 + 4, "带宽:" + (order.getBandwidth()) + "M \n\t 密钥:"
+ ((!order.getKeypairs().equals("") && order.getKeypairs() != null) ? order.getKeypairs() : 0) + "个", wchleft);
sheet.addCell(bankwidth);
// IP总数:0 个
Label ipCount = new jxl.write.Label(2, s * k + 14 + 5, "IP:" + (order.getIpCount()) + "个\n\t", wchleft);
sheet.addCell(ipCount);
}
}
}
}
workbook.write();
workbook.close();
// 先建立一个文件读取流去读取这个临时excel文件
FileInputStream fs = null;
try {
fs = new FileInputStream(filename);
} catch (FileNotFoundException e) {
log.error("生成excel错误! " + filename + " 不存在!", e);
return;
}
// 设置响应头和保存文件名
// 这个一定要设定,告诉浏览器这次请求是一个下载的数据流
final String userAgent = request.getHeader("USER-AGENT");
response.setContentType("application/vnd.ms-excel");
String excelName = "";
try {
// 这边的 "客户名称-资源.xls" 替换成你自己要显示给用户的文件名
String fileName2 = customer.getCustomerName() + "-资源.xls";
if (StringUtils.contains(userAgent, "MSIE")) {// IE浏览器
excelName = URLEncoder.encode(fileName2, "UTF8");
} else if (StringUtils.contains(userAgent, "Mozilla")) {// google,火狐浏览器
excelName = new String(fileName2.getBytes(), "ISO8859-1");
} else {
excelName = URLEncoder.encode(fileName2, "UTF8");// 其他浏览器
}
} catch (UnsupportedEncodingException e1) {
log.error("转换excel名称编码错误!", e1);
}
response.setHeader("Content-Disposition", "attachment; filename=\"" + excelName + "\"");
log.debug(excelName);
// 写出流信息
int b = 0;
try {
// 这里的 response 就是你 servlet 的那个传参进来的 response
PrintWriter out = response.getWriter();
while ((b = fs.read()) != -1) {
out.write(b);
}
fs.close();
out.close();
log.debug(excelName + " - " + new Date().toString() + "文件下载完毕.");
} catch (Exception e) {
log.error(excelName + " - " + new Date().toString() + " 下载文件失败!.", e);
}
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
super.doGet(req, resp);
}
}
*
*/
package com.chinaops.web.ydgd.servlet;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Alignment;
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.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.chinaops.web.ydgd.entity.Customer;
import com.chinaops.web.ydgd.entity.Order;
import com.chinaops.web.ydgd.entity.Ticket;
import com.chinaops.web.ydgd.service.CustomerService;
import com.chinaops.web.ydgd.service.OrderService;
import com.chinaops.web.ydgd.service.TicketService;
import com.chinaops.web.ydgd.utils.Suite;
/**
* @author tomhat
* 2014年9月1日 上午9:34:38
*/
public class ExportExcelServlet extends HttpServlet {
private static final Log log = LogFactory.getLog(ExportExcelServlet.class);
ApplicationContext context = new ClassPathXmlApplicationContext("spring-dao.xml");
TicketService ticketService = (TicketService) context.getBean("ticketService");
CustomerService customerService = (CustomerService) context.getBean("customerService");
OrderService orderService = (OrderService) context.getBean("orderService");
String targetfile = "D:/资源.xls";// 输出的excel文件名
String[] worksheet = { "共享云", "专享云", "云存储" };// 输出的excel文件工作表名
String[] title = { "云平台", "开通时间", "资源详情" };// excel工作表的标题
WritableWorkbook workbook;
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String customerId = request.getParameter("customerId");
try {
Customer customer = customerService.getCustomerBycustomerId(customerId);
String osSys = System.getProperty("os.name");
// if (StringUtils.isNotEmpty(os) && os.startsWith("Windows"))
// return "f:" + File.separatorChar + "tuts" + File.separatorChar;
// else
// return File.separatorChar + "tuts" + File.separatorChar;
String path = request.getSession().getServletContext().getRealPath("/");
String filename = path + "/excel/" + customer.getCustomerName() + "-资源.xls";
OutputStream os = new FileOutputStream(filename);
workbook = Workbook.createWorkbook(os);
List<Ticket> ticketList = ticketService.getOpenTicketByCustomerId(customerId);
String ticketIdS = new String();
if (ticketList.size() > 0) {
for (Ticket ticket : ticketList) {
ticketIdS += "'" + ticket.getTicketId() + "',";
}
}
String ticketIds = "";
if (ticketIdS.indexOf(",") > 0) {
ticketIds = ticketIdS.substring(0, ticketIdS.length() - 1);
}
List<Order> productTypeList = new ArrayList<Order>();
List<Order> productList = new ArrayList<Order>();
if (!ticketIds.equals("") && ticketIds != null) {
productTypeList = orderService.getProductTypes(ticketIds);
}
if (productTypeList != null && productTypeList.size() > 0) {
for (int i = 0; i < productTypeList.size(); i++) {
WritableSheet sheet = workbook.createSheet(worksheet[i], i);
WritableFont wftitle = new WritableFont(WritableFont.TIMES, 25, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat wcfFtitle = new WritableCellFormat(wftitle);
wcfFtitle.setBackground(Colour.GRAY_25);
wcfFtitle.setAlignment(Alignment.LEFT);
sheet.mergeCells(0, 0, 2, 0);
sheet.setRowView(0, 600);
Label labeltitle = new Label(0, 0, "客户信息", wcfFtitle);
sheet.addCell(labeltitle);
WritableFont wfbase = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat wcfFbase = new WritableCellFormat(wfbase);
wcfFbase.setAlignment(Alignment.LEFT);
sheet.mergeCells(0, 1, 2, 1);
sheet.setRowView(1, 500);
Label labelbase = new Label(0, 1, "基本信息", wcfFbase);
sheet.addCell(labelbase);
WritableFont wfright = new WritableFont(WritableFont.TAHOMA, 12, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat wcfFright = new WritableCellFormat(wfright);
wcfFright.setAlignment(Alignment.RIGHT);
Label labelcustomerName = new Label(0, 2, "客户名称:", wcfFright);
sheet.addCell(labelcustomerName);
Label hangye = new Label(0, 3, "所属行业:", wcfFright);
sheet.addCell(hangye);
Label dizhi = new Label(0, 4, "地址:", wcfFright);
sheet.addCell(dizhi);
Label youbian = new Label(0, 5, "邮编:", wcfFright);
sheet.addCell(youbian);
WritableFont wfleft = new WritableFont(WritableFont.TAHOMA, 12, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat wcfleft = new WritableCellFormat(wfleft);
wcfleft.setAlignment(Alignment.LEFT);
sheet.mergeCells(1, 2, 2, 2);
Label labelcustomerName1 = new Label(1, 2, customer.getCustomerName(), wcfleft);
sheet.addCell(labelcustomerName1);
sheet.mergeCells(1, 3, 2, 3);
Label hangye1 = new Label(1, 3, customer.getCategory(), wcfleft);
sheet.addCell(hangye1);
sheet.mergeCells(1, 4, 2, 4);
Label dizhi1 = new Label(1, 4, customer.getCustomerAddress(), wcfleft);
sheet.addCell(dizhi1);
sheet.mergeCells(1, 5, 2, 5);
Label youbian1 = new Label(1, 5, customer.getPostalCode(), wcfleft);
sheet.addCell(youbian1);
WritableFont wfcontact = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat wcfFcontact = new WritableCellFormat(wfcontact);
wcfFcontact.setAlignment(Alignment.LEFT);
sheet.mergeCells(0, 6, 2, 6);
sheet.setRowView(6, 500);
Label labelcontact = new Label(0, 6, "联系人", wcfFcontact);
sheet.addCell(labelcontact);
Label yewulianxiren = new Label(0, 7, "业务联系人:", wcfFright);
sheet.addCell(yewulianxiren);
Label yewuPhone = new Label(0, 8, "电话:", wcfFright);
sheet.addCell(yewuPhone);
Label yewuEmail = new Label(0, 9, "邮箱:", wcfFright);
sheet.addCell(yewuEmail);
Label jishulianxiren = new Label(0, 10, "技术联系人:", wcfFright);
sheet.addCell(jishulianxiren);
Label jishuPhone = new Label(0, 11, "电话:", wcfFright);
sheet.addCell(jishuPhone);
Label jishuEmail = new Label(0, 12, "邮箱:", wcfFright);
sheet.addCell(jishuEmail);
sheet.mergeCells(1, 7, 2, 7);
Label yewu = new Label(1, 7, customer.getB_contact(), wcfleft);
sheet.addCell(yewu);
sheet.mergeCells(1, 8, 2, 8);
Label ywPhone = new Label(1, 8, customer.getB_phone(), wcfleft);
sheet.addCell(ywPhone);
sheet.mergeCells(1, 9, 2, 9);
Label ywEmail = new Label(1, 9, customer.getB_email(), wcfleft);
sheet.addCell(ywEmail);
sheet.mergeCells(1, 10, 2, 10);
Label jishu = new Label(1, 10, customer.getT_contact(), wcfleft);
sheet.addCell(jishu);
sheet.mergeCells(1, 11, 2, 11);
Label jsPhone = new Label(1, 11, customer.getT_phone(), wcfleft);
sheet.addCell(jsPhone);
sheet.mergeCells(1, 12, 2, 12);
Label jsEmail = new Label(1, 12, customer.getT_email(), wcfleft);
sheet.addCell(jsEmail);
sheet.setRowView(2, 400);
sheet.setRowView(3, 400);
sheet.setRowView(4, 400);
sheet.setRowView(5, 400);
sheet.setRowView(7, 400);
sheet.setRowView(8, 400);
sheet.setRowView(9, 400);
sheet.setRowView(10, 400);
sheet.setRowView(11, 400);
sheet.setRowView(12, 400);
for (int j = 0; j < title.length; j++) {
WritableFont wf = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat wcfF = new WritableCellFormat(wf);
wcfF.setBackground(Colour.GRAY_25);
wcfF.setAlignment(Alignment.CENTRE);
// Label(列号,行号 ,内容 )
Label label = new Label(j, 14, title[j], wcfF); // put
// the
// title
// in
sheet.addCell(label);
}
sheet.setColumnView(0, 15);
sheet.setColumnView(1, 20);
sheet.setColumnView(2, 50);
sheet.setRowView(14, 600);
productList = orderService.getOrderByProductType(ticketIds, productTypeList.get(i).getProductType());
if (productList != null && productList.size() > 0) {
int s = 5;
for (int k = 0; k < productList.size(); k++) {
Order order = productList.get(k);
sheet.setRowView(s * k + 14 + 1, 500);
sheet.setRowView(s * k + 14 + 2, 500);
sheet.setRowView(s * k + 14 + 3, 500);
sheet.setRowView(s * k + 14 + 4, 500);
sheet.setRowView(s * k + 14 + 5, 500);
sheet.mergeCells(0, s * k + 14 + 1, 0, s * k + 14 + 5);
sheet.mergeCells(1, s * k + 14 + 1, 1, s * k + 14 + 5);
WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREEN);
jxl.write.WritableCellFormat wchB = new jxl.write.WritableCellFormat(wfc);
wchB.setAlignment(jxl.format.Alignment.CENTRE);
wchB.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wchB.setBorder(Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.GRAY_25);
Label CloudPlatform = new jxl.write.Label(0, s * k + 14 + 1, order.getCloudPlatform(), wchB);
sheet.addCell(CloudPlatform);
Label OpenTime = new jxl.write.Label(1, s * k + 14 + 1, order.getOpenTime(), wchB);
sheet.addCell(OpenTime);
WritableFont wfbleft = new jxl.write.WritableFont(WritableFont.TAHOMA, 10, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE);
jxl.write.WritableCellFormat wchleft = new jxl.write.WritableCellFormat(wfbleft);
wchleft.setAlignment(jxl.format.Alignment.LEFT);
wchleft.setBorder(Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.GRAY_25);
if (order.getProductType().equals("share")) {
Label ecu = new jxl.write.Label(2, s * k + 14 + 1, "ECU:" + Suite.suiteShare(order.getSuite())[0] + "个\n\t 负载均衡:"
+ ((order.getElb() != null && !order.getElb().equals("")) ? order.getElb() : 0) + "个", wchleft);
sheet.addCell(ecu);
} else if (order.getProductType().equals("exclusive")) {
Label ecu = new jxl.write.Label(2, s * k + 14 + 1, "ECU:" + Suite.suiteExclusive(order.getSuite())[0] + "个\n\t 负载均衡:"
+ ((order.getElb() != null && !order.getElb().equals("")) ? order.getElb() : '0') + "个", wchleft);
sheet.addCell(ecu);
}// 存储:200 GB 主机保护:0 个
Label volume = new jxl.write.Label(2, s * k + 14 + 2, "存储:" + (Integer.parseInt(order.getStorage()) + Integer.parseInt(Suite.suiteShare(order.getSuite())[2]))
+ "GB [含赠送存储:" + Suite.suiteShare(order.getSuite())[2] + "GB]\t 主机保护:" + ((!order.getHa().equals("") || order.getHa() != null) ? order.getHa() : 0) + "个", wchleft);
sheet.addCell(volume);
// 快照:200 GB 防火墙:0 个
Label snapshot = new jxl.write.Label(2, s * k + 14 + 3, "快照:" + (order.getSnapshot()) + "GB\n\t 防火墙:"
+ ((!order.getSecuritys().equals("") && order.getSecuritys() != null) ? order.getSecuritys() : 0) + "个", wchleft);
sheet.addCell(snapshot);
// 带宽:0 M 秘钥:0 个
Label bankwidth = new jxl.write.Label(2, s * k + 14 + 4, "带宽:" + (order.getBandwidth()) + "M \n\t 密钥:"
+ ((!order.getKeypairs().equals("") && order.getKeypairs() != null) ? order.getKeypairs() : 0) + "个", wchleft);
sheet.addCell(bankwidth);
// IP总数:0 个
Label ipCount = new jxl.write.Label(2, s * k + 14 + 5, "IP:" + (order.getIpCount()) + "个\n\t", wchleft);
sheet.addCell(ipCount);
}
}
}
}
workbook.write();
workbook.close();
// 先建立一个文件读取流去读取这个临时excel文件
FileInputStream fs = null;
try {
fs = new FileInputStream(filename);
} catch (FileNotFoundException e) {
log.error("生成excel错误! " + filename + " 不存在!", e);
return;
}
// 设置响应头和保存文件名
// 这个一定要设定,告诉浏览器这次请求是一个下载的数据流
final String userAgent = request.getHeader("USER-AGENT");
response.setContentType("application/vnd.ms-excel");
String excelName = "";
try {
// 这边的 "客户名称-资源.xls" 替换成你自己要显示给用户的文件名
String fileName2 = customer.getCustomerName() + "-资源.xls";
if (StringUtils.contains(userAgent, "MSIE")) {// IE浏览器
excelName = URLEncoder.encode(fileName2, "UTF8");
} else if (StringUtils.contains(userAgent, "Mozilla")) {// google,火狐浏览器
excelName = new String(fileName2.getBytes(), "ISO8859-1");
} else {
excelName = URLEncoder.encode(fileName2, "UTF8");// 其他浏览器
}
} catch (UnsupportedEncodingException e1) {
log.error("转换excel名称编码错误!", e1);
}
response.setHeader("Content-Disposition", "attachment; filename=\"" + excelName + "\"");
log.debug(excelName);
// 写出流信息
int b = 0;
try {
// 这里的 response 就是你 servlet 的那个传参进来的 response
PrintWriter out = response.getWriter();
while ((b = fs.read()) != -1) {
out.write(b);
}
fs.close();
out.close();
log.debug(excelName + " - " + new Date().toString() + "文件下载完毕.");
} catch (Exception e) {
log.error(excelName + " - " + new Date().toString() + " 下载文件失败!.", e);
}
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
super.doGet(req, resp);
}
}