一、Util类
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import javax.swing.JOptionPane;
import net.evecom.fjzw.vo.report.export.AreaDeviceFlowStatistical;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
/**
* Excel导出通用工具类
*
* @author Miles Ma
* @version 1.0
*/
public class ExportExcelTool<T> {
public HSSFWorkbook getWorkBook(String title, String subTitle, String[] headers, Collection<T> dataset) {
// 创建一个工作薄和一个工作表
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("工作表");
sheet.setDefaultColumnWidth((short) 20);
// 生成并设置样式
HSSFCellStyle titleStyle = setTitleStyle(workbook);// 标题单元格样式
HSSFFont subTitleFont = setSubTitleFont(workbook);// 子标题字体
HSSFCellStyle headerStyle = setHeaderStyle(workbook);// 列名单元格样式
HSSFCellStyle otherStyle = setOtherStyle(workbook);// 其他单元格样式
// 生成报表名称
HSSFRow titleRow = sheet.createRow(0);
sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (headers.length - 1)));// 指定合并区域
HSSFCell titleCell = titleRow.createCell((short) 0);
titleRow.setHeightInPoints((short) 42);// 行高
titleCell.setCellStyle(titleStyle);// 设置样式
HSSFRichTextString titleText = new HSSFRichTextString(title + "\r\n" + subTitle);
titleText.applyFont(title.length(), (title + "\r\n" + subTitle).length(), subTitleFont);
titleCell.setCellValue(titleText);// 设置内容
// 产生表格标题行
HSSFRow headerRow = sheet.createRow(1);
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = headerRow.createCell(i);
cell.setCellStyle(headerStyle);
HSSFRichTextString headerText = new HSSFRichTextString(headers[i]);
cell.setCellValue(headerText);
}
HSSFRow dataRow = null;
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = 1;// 从第三行开始
while (it.hasNext()) {
index++;
dataRow = sheet.createRow(index);
T t = (T) it.next();
// 利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < fields.length; i++) {
HSSFCell cell = dataRow.createCell(i);
cell.setCellStyle(otherStyle);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try {
Method getMethod = t.getClass().getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
String textValue = null;
if (value != null) {
if (value instanceof Double) {// 判断值的类型后进行强制类型转换
double doubleValue = (Double) value;
cell.setCellValue(doubleValue);
} else {
textValue = value.toString();// 其它数据类型都当作字符串简单处理
}
if (textValue != null) {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} finally {
// 清理资源
}
}
}
return workbook;
}
// 标题单元格样式
private HSSFCellStyle setTitleStyle(HSSFWorkbook workbook) {
HSSFCellStyle style = workbook.createCellStyle();
// 字体
HSSFFont font = workbook.createFont();
font.setFontName("宋体");// 字体名称
font.setFontHeightInPoints((short) 18);// 字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
style.setFont(font);
// 样式
style.setWrapText(true);// 自动换行
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
// 子标题字体
private HSSFFont setSubTitleFont(HSSFWorkbook workbook) {
HSSFFont font = workbook.createFont();
font.setFontName("Times New Roman");// 字体名称
font.setFontHeightInPoints((short) 14);// 字体大小
return font;
}
// 列名单元格样式
private HSSFCellStyle setHeaderStyle(HSSFWorkbook workbook) {
HSSFCellStyle style = workbook.createCellStyle();
// 字体
HSSFFont font = workbook.createFont();
font.setFontName("Times New Roman");// 字体名称
font.setFontHeightInPoints((short) 11);// 字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
style.setFont(font);
// 样式
style.setFillForegroundColor(HSSFColor.YELLOW.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
// 其他单元格样式
private HSSFCellStyle setOtherStyle(HSSFWorkbook workbook) {
HSSFCellStyle style = workbook.createCellStyle();
// 字体
HSSFFont font = workbook.createFont();
font.setFontName("Times New Roman");// 字体名称
font.setFontHeightInPoints((short) 11);// 字体大小
style.setFont(font);
// 样式
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
public static void main(String[] args) {
ExportExcelTool<AreaDeviceFlowStatistical> tool = new ExportExcelTool<AreaDeviceFlowStatistical>();
String[] headers = { "区域名称", "设备名称", "主机名", "总流入流量", "总流出流量", "平均流入流量", "平均流出流量", "最大流入流量", "最大流出流量" };
List<AreaDeviceFlowStatistical> dataset = new ArrayList<AreaDeviceFlowStatistical>();
// dataset.add(new AreaDeviceFlowStatistical("福州市", "", "10.1.6.21",
// null, 25.21, 25.21, 25.21, 25.21, 25.21));
// dataset.add(new AreaDeviceFlowStatistical("厦门市", "李四", "10.1.6.23",
// 25.23, 25.23, 25.23, 25.23, 25.23, 25.23));
// dataset.add(new AreaDeviceFlowStatistical("泉州市", "王五", "10.1.6.25",
// 25.25, 25.25, 25.25, 25.25, 25.25, 25.25));
OutputStream out = null;
try {
out = new FileOutputStream("E://流量.xls");
String title = "省级设备流量报表";
String subTitle = "2015年12月1日-2015年12月31日";
HSSFWorkbook workBook = tool.getWorkBook(title, subTitle, headers, dataset);
try {
workBook.write(out);// 将workBook写入输出流
} catch (IOException e) {
e.printStackTrace();
}
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}
JOptionPane.showMessageDialog(null, "导出成功!");
System.out.println("excel导出成功！");
}
}
二、Action类
/**
* 导出Excel文件
*
* @author Miles Ma
* @version 1.0
*/
public class ExportExcelAction extends DispatchAction {
/** 日志 */
Logger log = Logger.getLogger(this.getClass());
/** NodeDao */
private NodeDao nodeDao = ConfigurationDaoFactory.getNodeDao();
/** InterfaceDao */
private InterfaceDao interfaceDao = ConfigurationDaoFactory.getInterfaceDao();
/** 日期格式 */
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
public ActionForward exportCityFlow(ActionMapping mapping, ActionForm form, HttpServletRequest request,
HttpServletResponse response) {
String startTime = RequestUtils.getStringParameter(request, "startTime_export", null);
String endTime = RequestUtils.getStringParameter(request, "endTime_export", null);
List<AreaDeviceFlowStatistical> resultlist = new ArrayList<AreaDeviceFlowStatistical>();
try {
if (startTime == null || endTime == null) {
endTime = sdf.format(new Date());
startTime = sdf.format(new Date(System.currentTimeMillis() - (86400000 * 7)));
}
StringBuilder nodeSql = new StringBuilder();
nodeSql.append(" select r.* from ( ");
nodeSql.append(" select n.*, g.name area_name ");
nodeSql.append(" from sv_nodes n left join sv_groups g on n.area_id = g.id ");
nodeSql.append(" where g.parent_id = 1 or g.name like '%平潭%' ");
nodeSql.append(" ) r where r.node_type = 1 ");
nodeSql.append(" order by r.area_id ");
List<VoNode> nodeList = nodeDao.getNodesByNativeSql(nodeSql.toString());
List<NodeInterface> ifList = new ArrayList<NodeInterface>();
AreaDeviceFlowStatistical deviceFlow = null;
String snmpifalias = null;
for (int i = 0; i < nodeList.size(); i++) {
snmpifalias = "";// 重置
deviceFlow = new AreaDeviceFlowStatistical();
String ifSql = " select * from sv_node_interfaces where up_linked = 1 and node_id = "
+ nodeList.get(i).getId() + " order by ifnumber ";
ifList = interfaceDao.getIfByNativeSql(ifSql);
for (int j = 0; j < (ifList.size() - 1); j++) {
snmpifalias += ("'" + ifList.get(j).getSnmpIfalias() + "'") + ",";
}
if (ifList.size() > 0) {
snmpifalias += "'" + ifList.get(ifList.size() - 1).getSnmpIfalias() + "'";
}
FjzwReportMgr reportMgr = new FjzwReportMgr();
Map<String, Double> nodeTraffic = reportMgr.getTotalNodeTraffic(nodeList.get(i).getId(), snmpifalias,
ifList.size(), FormatUtil.parseDate(startTime), FormatUtil.parseDate(endTime));
deviceFlow.setAreaName(nodeList.get(i).getAreaName());
deviceFlow.setDeviceName(nodeList.get(i).getNodeName());
deviceFlow.setHostName(nodeList.get(i).getIpHostName());
deviceFlow.setTotalNodeInOctet(nodeTraffic.get("IFINOCTET"));
deviceFlow.setTotalNodeOutOctet(nodeTraffic.get("IFOUTOCTET"));
deviceFlow.setAvgNodeInOctet(nodeTraffic.get("AVG_IFINOCTET"));
deviceFlow.setAvgNodeOutOctet(nodeTraffic.get("AVG_IFOUTOCTET"));
deviceFlow.setMaxNodeInOctet(nodeTraffic.get("MAX_IFINOCTET"));
deviceFlow.setMaxNodeOutOctet(nodeTraffic.get("MAX_IFOUTOCTET"));
resultlist.add(deviceFlow);
}
} catch (Exception e) {
log.error("export excel error !", e);
request.setAttribute("message", "对不起，没有获取任何有效的采样数据");
return mapping.findForward("errMsg");
}
/** 将设置好的workBook对象写入输出流（生成Excel文档） */
ExportExcelTool<AreaDeviceFlowStatistical> tool = new ExportExcelTool<AreaDeviceFlowStatistical>();
String title = "市级设备流量统计报表";
String startYear = startTime.split(" ")[0].split("-")[0];
String startMonth = startTime.split(" ")[0].split("-")[1];
String startDay = startTime.split(" ")[0].split("-")[2];
String endYear = endTime.split(" ")[0].split("-")[0];
String endMonth = endTime.split(" ")[0].split("-")[1];
String endDay = endTime.split(" ")[0].split("-")[2];
String subTitle = startYear + "年" + startMonth + "月" + startDay + "日" + "-" + endYear + "年" + endMonth + "月"
+ endDay + "日";
String[] headers = { "区域名称", "设备名称", "主机名", "总流入流量(bit)", "总流出流量(bit)", "平均流入流量(bit/s)", "平均流出流量(bit/s)",
"最大流入流量(bit/s)", "最大流出流量(bit/s)" };
HSSFWorkbook workBook = tool.getWorkBook(title, subTitle, headers, resultlist);
OutputStream out = null;
String fileName = "市级设备流量统计报表.xls";
String filePath = request.getSession().getServletContext().getRealPath("/") + fileName;
try {// 新建文件输出流
out = new FileOutputStream(filePath);
try {// 将workBook对象写入文件输出流
workBook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} finally {
try {
out.close();// 写入后关闭文件输出流
} catch (IOException e) {
e.printStackTrace();
}
}
/** 下载Excel文档 */
this.setResponseHeader(response, fileName);// 设置响应头和文件名称
OutputStream output = null;
FileInputStream fis = null;
try {
output = response.getOutputStream();// 获取相应输出流
fis = new FileInputStream(filePath);// 创建文件输入流
byte[] b = new byte[2048];
int i = 0;
try {
while ((i = fis.read(b)) > 0) {
output.write(b, 0, i);// 将输入流的内容写入输出流
}
} catch (IOException e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
} finally {// 关闭流
try {
output.flush();// 强制将输出流中的内容写入到文件中并且清空输出流中的内容
fis.close();
output.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
/** 设置响应头 */
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(), "ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
