最近在一个项目中涉及到了excel导入导出,excel导出用的POI
导出excel其实就是创建一个工作表,
HSSFWorkbook wb = new HSSFWorkbook();
然后创建sheet
HSSFSheet sheet = wb.createSheet("数据信息");
通过sheet创建row
HSSFRow row = sheet.createRow( rowNumber++ );
通过row创建cell
HSSFCell cell = row.createCell((short) 0);
具体实现是:
public void exportExcel(){
List<Yxjszm> dataset = yxjszmManager.getListByBean(bean);
HttpServletResponse response = (HttpServletResponse)
ActionContext.getContext().get(org.apache.struts2.StrutsStatics.HTTP_RESPONSE);
try {
int rowNumber = 0;
HSSFWorkbook wb = new HSSFWorkbook();
/* 字体样式 */
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 12);
font.setFontName("Arial");
HSSFCellStyle style = wb.createCellStyle();//背景有颜色
style.setFont(font);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
style.setFillPattern(HSSFCellStyle.BRICKS);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 位于中间
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 灰色
style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); // 灰色
style.setWrapText(true); // 当文字长于该列的宽度时,自动把宽加长
HSSFCellStyle style2 = wb.createCellStyle();
style2.setFont(font);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style2.setWrapText(true);
HSSFSheet sheet = wb.createSheet("数据信息");
sheet.setColumnWidth((short) 0, (short) 6000); //默认设置宽度
sheet.setColumnWidth((short) 1, (short) 6000); //默认设置宽度
sheet.setColumnWidth((short) 2, (short) 4000); //默认设置宽度
sheet.setColumnWidth((short) 3, (short) 4000); //默认设置宽度
sheet.setColumnWidth((short) 4, (short) 4000); //默认设置宽度
sheet.setColumnWidth((short) 5, (short) 4000); //默认设置宽度
HSSFRow row = sheet.createRow( rowNumber++ );
HSSFCell cell = row.createCell((short) 0);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("序号");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue( "别名" );
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue( "名Id" );
cell.setCellStyle(style);
//sheet.addMergedRegion( new Region(0, (short) 1, 0, (short) 5 ) );
cell = row.createCell((short) 3);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue( "名" );
cell.setCellStyle(style);
cell = row.createCell((short) 4);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue( "码" );
cell.setCellStyle(style);
cell = row.createCell((short) 5);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue( "上下" );
cell.setCellStyle(style);
for (int i = 0; i < dataset.size(); ++i){
Yxjszm zm = (Yxjszm) dataset.get(i);
rowNumber = this.addRowExcelInfor(cell, sheet, row, style2,zm, rowNumber) ;
}
String title = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
response.setCharacterEncoding("UTF-8");
response.setContentType("octets/stream");// 设置输出流
response.addHeader("Content-Disposition", "attachment;filename="
+ title + ".xls");// 向客户端输出xls文件
OutputStream os=response.getOutputStream();
//add workbook to OutpustStream
wb.write(os);
os.flush();
os.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private int addRowExcelInfor(HSSFCell cell , HSSFSheet sheet , HSSFRow row ,HSSFCellStyle style2, Yxjszm zm , int rowNumber){
row = sheet.createRow( rowNumber++ );
cell = row.createCell((short) 0);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(zm.getSn() );
cell.setCellStyle(style2);
cell = row.createCell((short) 1);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(zm.getRunPostlineid());
cell.setCellStyle(style2);
cell = row.createCell((short) 2);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue( zm.getStationId() );
cell.setCellStyle(style2);
cell = row.createCell((short) 3);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue( zm.getStationName() );
cell.setCellStyle(style2);
cell = row.createCell((short) 4);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(zm.getSpell());
cell.setCellStyle(style2);
cell = row.createCell((short) 5);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(zm.getUpDown());
cell.setCellStyle(style2);
cell.setCellStyle(style2);
return rowNumber;
}
导入excel到数据库用jxl读取excel:
public void upload() throws IOException, BiffException{
String targetDirectory =contextPvd.getAppRealPath("")+"/upload";
String targetFileName = uploadFileName;
File target = new File(targetDirectory, targetFileName);
FileUtils.copyFile(upload, target);
setUploadFileName(target.getPath());//保存文件的存放路径
Workbook rwb = null;
Cell cell = null;
//创建输入流
InputStream stream = new FileInputStream(target);
//获取Excel文件对象
rwb = Workbook.getWorkbook(stream);
//获取文件的指定工作表 默认的第一个
Sheet sheet = rwb.getSheet(0);
//行数(表头的目录不需要,从1开始)
PrintWriter out = contextPvd.getResponse().getWriter();
// StringBuffer script =new StringBuffer();
for(int i=1; i<sheet.getRows(); i++){
Yxjszm zm=new Yxjszm();
//取号
cell = sheet.getCell(0,i);
zm.setSn(Long.parseLong(cell.getContents()));
//取路id
cell = sheet.getCell(1,i);
zm.setRunPostlineid(cell.getContents());
//取id
cell = sheet.getCell(2,i);
zm.setStationId(cell.getContents());
//取名
cell = sheet.getCell(3,i);
zm.setStationName(cell.getContents());
//取码
cell = sheet.getCell(4,i);
zm.setSpell(cell.getContents());
//取上下行
cell = sheet.getCell(5,i);
zm.setUpDown(Long.parseLong(cell.getContents()));
findList = yxjszmManager.findListByXlAndZm(zm.getRunPostlineid(), zm.getStationName());
if(findList.size()>0)
{
log.info("此路("+zm.getRunPostlineid()+")对应站名("+zm.getStationName()+")已存在!");
}
else
{
yxjszmManager.save(zm);
log.info("线路("+zm.getRunPostlineid()+")"+"站名("+zm.getStationName()+")添加成功!");
out.write("<script>parent.editFlag("+i+",'1')</script>");
out.flush();
}
}
out.close();
stream.close();
target.delete();
}
分享到:
相关推荐
Thinkphp5整合excel导入导出Thinkphp5整合excel导入导出Thinkphp5整合excel导入导出Thinkphp5整合excel导入导出Thinkphp5整合excel导入导出Thinkphp5整合excel导入导出Thinkphp5整合excel导入导出Thinkphp5整合excel...
Excel导入导出Excel导入导出Excel导入导出Excel导入导出Excel导入导出Excel导入导出Excel导入导出Excel导入导出Excel导入导出Excel导入导出Excel导入导出Excel导入导出
thinkhphp3.2 excel导入导出demothinkhphp3.2 excel导入导出demothinkhphp3.2 excel导入导出demothinkhphp3.2 excel导入导出demothinkhphp3.2 excel导入导出demothinkhphp3.2 excel导入导出demothinkhphp3.2 excel...
Java POI EXCEL导入导出Java POI EXCEL导入导出Java POI EXCEL导入导出Java POI EXCEL导入导出Java POI EXCEL导入导出Java POI EXCEL导入导出Java POI EXCEL导入导出Java POI EXCEL导入导出Java POI EXCEL导入导出...
c#制作的EX29-Excel导入导出功能。
java_poi实现excel导入导出功能,有详细的注解
万能的Excel导入导出工具. 支持从List中导出. 支持从List中导入导出. 支持从List里面还有List<POJO>>中导入导出. 支持导出类似课程表结构类型纵表. 支持国际化. 支持数据字典. 支持单元格中下拉框数据校验. 支持自动...
Excel导入导出数据库Excel批量导入导出数据库源码,以及一些解决乱码收藏的博客知识
Excel源代码,导入导出各种工具类 <groupId>org.apache.poi <artifactId>poi <groupId>org.apache.poi <artifactId>poi-scratchpad 代码实例: String path = ""; byte[] bytes; vo.setCurrentPage(0); vo....
可以将excel中的内容导入到mssql数据库中,也可以将MSSQL中的数据导出到Excel中.在导入时还可选择字段进行导入,并且原程序全部公开
Excel导入导出Excel导入导出Excel导入导出
excel 导入导出插件,和导出数据库实例
C# 操作 Excel 导入导出,很方便的.
超级列表框导入excel和导出excel(支持导入导出表头)用黑夜上传的LibXL模块弄的,。@田心南。Tags:ExcelEXCEL导入导出excel表。
Excel导入导出 jxl及Poi 工具类:jxl 实现 及 Poi实现 非常实用
Excel导入导出 Excel导入 Excel导出
Java实现Excel导入导出简介 Java实现Excel导入导出是通过Java语言编写的程序,将数据从Excel文件中读取或写入到Excel文件中。这种方式可以实现数据的批量处理和分析,提高工作效率。 Java实现Excel导入导出的应用...
Java实现Excel导入导出功能网络搜集,很好用
基于poi的excel导入导出封装,poi版本 <groupId>org.apache.poi <artifactId>poi <version>4.1.0 <groupId>org.apache.poi <artifactId>poi-ooxml <version>4.1.0 </dependency>