Excel导出
来源:互联网 发布:java超时处理 编辑:程序博客网 时间:2024/05/20 21:21
/*** 导出Excel*/
public String exportExcel(){
try {
String agent = getRequest().getHeader("USER-AGENT").toLowerCase();
String fname = year + "年" + month + "月" + "企业主要生产效益指标汇总表.xls";
//根据浏览器类型处理文件名称
if(agent.indexOf("msie 7")>0){
fileName = java.net.URLEncoder.encode(fname, "UTF-8");
}else if(agent.indexOf("msie 8")>0){
fileName = java.net.URLEncoder.encode(fname, "UTF-8");
}else if(agent.indexOf("msie 9")>0){
fileName = java.net.URLEncoder.encode(fname, "UTF-8");
}else if(agent.indexOf("msie 10")>0){
fileName = java.net.URLEncoder.encode(fname, "UTF-8");
}else if(agent.indexOf("msie")>0){
fileName = java.net.URLEncoder.encode(fname, "UTF-8");
}else if(agent.indexOf("firefox")>0){
fileName = new String(fname.getBytes("UTF-8"), "ISO8859-1");
}else if(agent.indexOf("gecko")>0 && agent.indexOf("rv:11")>0){
fileName = java.net.URLEncoder.encode(fname, "UTF-8");
}else{
fileName = java.net.URLEncoder.encode(fname, "UTF-8");
}
getResponse().setContentType("application/msexcel");
getResponse().setHeader("Content-disposition","attachment;filename=\""+fileName+"\"");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
File dstFile = new File(getRealPath() + "excel\\" + outExcelFileName);
File srcFile = new File(getRealPath() + "excel\\summary.xls");
creatTempExcelFile(srcFile, dstFile);
writeExcel("企业名称,行业类型", getExcelData());
return "download";
}
/** 获取导出Excel数据 */
private List<EfficiencyMonthStatistic> getExcelData(){
efficiencyMonthStatistics=efficiencyMonthStatisticService.getSummary(year, month);
return efficiencyMonthStatistics;
}
/** 生成excel */
private void writeExcel(String columnName, List<EfficiencyMonthStatistic> efficiencyMonthStatistics){
WritableWorkbook wwb = null;
Workbook wb = null;
try {
File file = new File(getRealPath() + "excel\\" + outExcelFileName);
wb = Workbook.getWorkbook(file);
wwb = Workbook.createWorkbook(file,wb);
// 用于数字单元格
WritableFont NormalFont = new WritableFont(WritableFont.createFont("Arial"), 10);
WritableCellFormat wcf_num = new WritableCellFormat(NormalFont,NumberFormats.FLOAT);
wcf_num.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_num.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_num.setAlignment(Alignment.LEFT); // 文字水平对齐
wcf_num.setWrap(false); // 文字是否换行
if(wwb != null){
WritableSheet ws = wwb.getSheet(0);
int row = 3;
row++ ;
for(int i = 0; i < efficiencyMonthStatistics.size(); i++){
int j = 0;
ws.setRowView(row, 600, false);
ExcelCellFactory cellFactory = new ExcelCellFactory();
ws.addCell(cellFactory.generatCell(j++, row, StringUtil.defaultIfEmpty(efficiencyMonthStatistics.get(i).getName(), "")));
ws.addCell(cellFactory.generatCell(j++, row, StringUtil.defaultIfEmpty(efficiencyMonthStatistics.get(i).getHylx(), "")));
ws.addCell((StringUtil.isNumber(efficiencyMonthStatistics.get(i).getGyysl()+"")?new jxl.write.Number(j++, row, efficiencyMonthStatistics.get(i).getGyysl(), wcf_num):cellFactory.generatCell(j++, row, "")));
ws.addCell((StringUtil.isNumber(efficiencyMonthStatistics.get(i).getQnGyysl()+"")?new jxl.write.Number(j++, row, efficiencyMonthStatistics.get(i).getQnGyysl(), wcf_num):cellFactory.generatCell(j++, row, "")));
ws.addCell((StringUtil.isNumber(efficiencyMonthStatistics.get(i).getBnljGyysl()+"")?new jxl.write.Number(j++, row, efficiencyMonthStatistics.get(i).getBnljGyysl(), wcf_num):cellFactory.generatCell(j++, row, "")));
ws.addCell((StringUtil.isNumber(efficiencyMonthStatistics.get(i).getQnljGyysl()+"")?new jxl.write.Number(j++, row, efficiencyMonthStatistics.get(i).getQnljGyysl(), wcf_num):cellFactory.generatCell(j++, row, "")));
row ++;
}
}
wwb.write();
} catch (Exception e) {
e.printStackTrace();
}finally{
if(wwb!=null){
try {
wwb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
public InputStream getInputStream() throws FileNotFoundException {
return ServletActionContext.getServletContext().getResourceAsStream("/" + "excel\\" + outExcelFileName);
} import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.lang.RandomStringUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
/** 字符串操作工具类*/
public class StringUtil {
/**
* 邮箱验证
* @param email 需要验证的字符串
* @return boolean
*/
public static boolean isVaildEmail(String email){
String emailPattern="[a-zA-Z0-9][a-zA-Z0-9._-]{2,16}[a-zA-Z0-9]@[a-zA-Z0-9]+.[a-zA-Z0-9]+";
boolean result=Pattern.matches(emailPattern, email);
return result;
}
/**
* 字符串分隔符
*/
public static final String SEPARATOR = String.valueOf((char) 29);
/**
* 判断字符串是否非空
*/
public static boolean isNotEmpty(String str) {
return StringUtils.isNotBlank(str);
}
/**
* 判断字符串是否为空
*/
public static boolean isEmpty(String str) {
return StringUtils.isEmpty(str);
}
/**
* 若字符串为空,则取默认值
*/
public static String defaultIfEmpty(String str, String defaultValue) {
return StringUtils.defaultIfEmpty(str, defaultValue);
}
/**
* 替换固定格式的字符串(支持正则表达式)
*/
public static String replaceAll(String str, String regex, String replacement) {
Pattern p = Pattern.compile(regex);
Matcher m = p.matcher(str);
StringBuffer sb = new StringBuffer();
while (m.find()) {
m.appendReplacement(sb, replacement);
}
m.appendTail(sb);
return sb.toString();
}
/**
* 是否为数字(整数或小数)
*/
public static boolean isNumber(String str) {
return NumberUtils.isNumber(str);
}
/**
* 是否为十进制数(整数)
*/
public static boolean isDigits(String str) {
return NumberUtils.isDigits(str);
}
/**
* 将驼峰风格替换为下划线风格
*/
public static String camelhumpToUnderline(String str) {
Matcher matcher = Pattern.compile("[A-Z]").matcher(str);
StringBuilder builder = new StringBuilder(str);
for (int i = 0; matcher.find(); i++) {
builder.replace(matcher.start() + i, matcher.end() + i, "_" + matcher.group().toLowerCase());
}
if (builder.charAt(0) == '_') {
builder.deleteCharAt(0);
}
return builder.toString();
}
/**
* 将下划线风格替换为驼峰风格
*/
public static String underlineToCamelhump(String str) {
Matcher matcher = Pattern.compile("_[a-z]").matcher(str);
StringBuilder builder = new StringBuilder(str);
for (int i = 0; matcher.find(); i++) {
builder.replace(matcher.start() - i, matcher.end() - i, matcher.group().substring(1).toUpperCase());
}
if (Character.isUpperCase(builder.charAt(0))) {
builder.replace(0, 1, String.valueOf(Character.toLowerCase(builder.charAt(0))));
}
return builder.toString();
}
/**
* 分割固定格式的字符串
*/
public static String[] splitString(String str, String separator) {
return StringUtils.splitByWholeSeparator(str, separator);
}
/**
* 将字符串首字母大写
*/
public static String firstToUpper(String str) {
return Character.toUpperCase(str.charAt(0)) + str.substring(1);
}
/**
* 将字符串首字母小写
*/
public static String firstToLower(String str) {
return Character.toLowerCase(str.charAt(0)) + str.substring(1);
}
/**
* 转为帕斯卡命名方式(如:UserRole)
*/
public static String toPascalStyle(String str, String seperator) {
return StringUtil.firstToUpper(toCamelhumpStyle(str, seperator));
}
/**
* 转为驼峰命令方式(如:userRole)
*/
public static String toCamelhumpStyle(String str, String seperator) {
return StringUtil.underlineToCamelhump(toUnderlineStyle(str, seperator));
}
/**
* 转为下划线命名方式(如:user_role)
*/
public static String toUnderlineStyle(String str, String seperator) {
str = str.trim().toLowerCase();
if (str.contains(seperator)) {
str = str.replace(seperator, "_");
}
return str;
}
/**
* 转为显示命名方式(如:User Role)
*/
public static String toDisplayStyle(String str, String seperator) {
String displayName = "";
str = str.trim().toLowerCase();
if (str.contains(seperator)) {
String[] words = StringUtil.splitString(str, seperator);
for (String word : words) {
displayName += StringUtil.firstToUpper(word) + " ";
}
displayName = displayName.trim();
} else {
displayName = StringUtil.firstToUpper(str);
}
return displayName;
}
/**
* 获取 UUID(32位)
*/
public static String createUUID() {
return UUID.randomUUID().toString().replaceAll("-", "");
}
/**
* 获取日期随机数
* @return
*/
public static String createDateRandom(){
return DateUtil.formatDatetimeNoSeparator(System.currentTimeMillis()) + RandomStringUtils.randomNumeric(4);
}
/**
* 删除最后一个字符
*/
public static String deleteLastStr(String str){
if(isNotEmpty(str)){
return str.substring(0, str.length() - 1);
}
else{
return "";
}
}
public static void main(String[] args) {
System.out.println(StringUtil.isNotEmpty("sdf"));
}
}
//说明
private static final String outExcelFileName = "template_temp.xls";
需要在WebRoot目录下新建excel文件夹, 添加summary.xls
好像是需要2003和2007版本的excel,高的版本好像会报错!
xml配置:
//说明
private static final String outExcelFileName = "template_temp.xls";
需要在WebRoot目录下新建excel文件夹, 添加summary.xls
好像是需要2003和2007版本的excel,高的版本好像会报错!
xml配置:
0 0
- 导出EXCEL
- Excel导出
- 导出Excel
- 导出EXCEL
- 导出Excel
- EXCEL导出
- 导出Excel
- 导出excel
- 导出excel
- EXCEL导出
- 导出excel
- 导出Excel
- Excel导出。
- 导出excel
- Excel 导出
- 导出excel
- 导出excel
- 导出excel
- 数据库连接
- OSI参考模型、TCP/IP参考模型及每层所用的协议
- 蛇形矩阵算法
- 安装配置supervisor
- ZooKeeper分布式锁实现原理
- Excel导出
- 双系统安装WIN7+ubuntu16.04
- 高精度加法模板
- UML总结之交互图
- Ubuntu caffe 搭建
- 在Struts+Spring+Hibernate框架中使用SQL语句查询
- Spring的容器后处理器
- supervisor前端-cesi的安装
- 【VK Cup 2012】Codeforces 209C Trails and Glades