package com.fh.util;
import java.io.FileOutputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
public class OutputExcel {
/**
* 将数据库数据,导出到excel文件中
* 导出的菜单是(客户关系管理--客户信息汇总)
*/
public static String outYhxxToExcel(List<PageData> yjts1){
List<PageData> yjts=yjts1;
//创建新的Excel工作薄
HSSFWorkbook workbook=new HSSFWorkbook ();
HSSFCellStyle style = workbook.createCellStyle(); // 样式对象
//设置字体的样式
HSSFFont font=workbook.createFont();
font.setColor(HSSFColor.RED.index);//HSSFColor.VIOLET.index //字体颜色
//font.setFontHeightInPoints((short)12);
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体增粗
//把字体应用到当前的样式
style.setFont(font);
String filePath ="";
try {
//如果新建一个名为“sheet1”的工作表
Sheet sheet=workbook.createSheet("用户信息");
sheet.setColumnWidth(0, 5560);
sheet.setColumnWidth(1, 5560);
sheet.setColumnWidth(2, 5560);
sheet.setColumnWidth(3, 5560);
sheet.setColumnWidth(4, 5560);
sheet.setColumnWidth(5, 5560);
sheet.setColumnWidth(6, 5560);
sheet.setColumnWidth(7, 5560);
sheet.setColumnWidth(8, 5560);
sheet.setColumnWidth(9, 5560);
sheet.setColumnWidth(10, 5560);
sheet.setColumnWidth(11, 5560);
sheet.setColumnWidth(12, 5560);
sheet.setColumnWidth(13, 5560);
sheet.setColumnWidth(14, 5560);
sheet.setColumnWidth(15, 5560);
sheet.setColumnWidth(16, 5560);
sheet.setColumnWidth(17, 5560);
Row row=null;
Cell cell=null;
int index=0;
row=sheet.createRow(index);
cell=row.createCell(0);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue("用户名");
cell.setCellStyle(style);
cell=row.createCell(1);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue("身份证号码");
cell.setCellStyle(style);
cell=row.createCell(2);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue("手机号码");
cell.setCellStyle(style);
cell=row.createCell(3);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue("用户类型");
cell.setCellStyle(style);
cell=row.createCell(4);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue("套餐");
cell.setCellStyle(style);
cell=row.createCell(5);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue("套餐到期时间");
cell.setCellStyle(style);
cell=row.createCell(6);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue("区域");
cell.setCellStyle(style);
cell=row.createCell(7);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue("居住地址");
cell.setCellStyle(style);
cell=row.createCell(8);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue("栋号");
cell.setCellStyle(style);
cell=row.createCell(9);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue("房号");
cell.setCellStyle(style);
cell=row.createCell(10);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue("光纤编号");
cell.setCellStyle(style);
cell=row.createCell(11);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue("登录账号");
cell.setCellStyle(style);
cell=row.createCell(12);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue("密码");
cell.setCellStyle(style);
cell=row.createCell(13);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue("光猫编号");
cell.setCellStyle(style);
cell=row.createCell(14);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue("光猫类型 ");
cell.setCellStyle(style);
cell=row.createCell(15);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue("光猫价格");
cell.setCellStyle(style);
cell=row.createCell(16);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue("总收款金额 ");
cell.setCellStyle(style);
cell=row.createCell(17);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue("收据单号");
cell.setCellStyle(style);
index=1;
if (yjts.size()>0) {
for (PageData pd : yjts) {
row=sheet.createRow(index);index++;
cell=row.createCell(0);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(pd.getString("yhname"));
cell=row.createCell(1);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(pd.getString("cardcode"));
cell=row.createCell(2);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(pd.getString("yhphone"));
cell=row.createCell(3);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(pd.getString("usertype"));
cell=row.createCell(4);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(pd.getString("dqtc"));
/* Object object = pd.get("tcystime");
if (object!=null) {
cell.setCellValue(object.toString());
}else{
cell.setCellValue("");
}*/
cell=row.createCell(5);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
Object object = pd.get("tcystime");
if (object!=null) {
cell.setCellValue(object.toString());
}else{
cell.setCellValue("");
}
cell=row.createCell(6);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(pd.getString("qy"));
cell=row.createCell(7);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(pd.getString("jzaddress"));
cell=row.createCell(8);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(pd.getString("dh"));
cell=row.createCell(9);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(pd.getString("fh"));
cell=row.createCell(10);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(pd.getString("poncode"));
cell=row.createCell(11);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(pd.getString("loginname"));
cell=row.createCell(12);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(pd.getString("spassword"));
cell=row.createCell(13);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(pd.getString("gmcode"));
cell=row.createCell(14);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(pd.getString("gmlx"));
cell=row.createCell(15);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(pd.getString("gmjg"));
cell=row.createCell(16);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
Object object1 = pd.get("fkmoney");
if (object1!=null) {
cell.setCellValue(object1.toString());
}else{
cell.setCellValue("");
}
cell=row.createCell(17);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(pd.getString("sjdcode"));
}
}
//新建文件输出流
DateFormat format = new SimpleDateFormat("yyyy-MM-dd HHmmss");
String format2 = format.format(new Date());
filePath =Const.FILEPATHFILE + format2+"YHXX"+".xls"; //文件上传路径
//fileName = FileUpload.fileUp(file, filePath, this.get32UUID()); //执行上传
// System.out.println(filePath);
FileOutputStream fOut=new FileOutputStream(PathUtil.getClasspath() +filePath);
//将数据写入Excel
workbook.write(fOut);
fOut.flush();
fOut.close();
}catch(Exception e){
e.printStackTrace();
}
return filePath;
}
}
使用java技术将数据导出到excel中
最新推荐文章于 2020-11-05 17:29:28 发布