- 浏览: 123617 次
最新评论
-
gaoxikun:
看起来很齐全,很完美,但是不知道从哪里下载 。
myeclipse插件简单介绍 -
gaoxikun:
亲,能把这个集成了插件的myeclipse 6.5给我一下吗, ...
myeclipse插件简单介绍 -
hotsmile:
不错!!!!!!!!!
myeclipse插件简单介绍
package com.huawei.bss.execlComm;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.Vector;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.Region;
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.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ProcessXSL {
HSSFWorkbook wb = new HSSFWorkbook();
String workbookFileName = "d:/资源池人员跟踪_20120911.xlsx";
protected final Log log = LogFactory.getLog(getClass());
public ProcessXSL() {
}
/**
* 创建 Execl 文件
*
* @param wb
* HSSFWorkbook
* @param exportInfo
* BaseDataExportInfo
*/
public void createWorkBookSheet(BaseDataExportInfo exportInfo) {
Vector cellList = null;
if (exportInfo.getWorkbookFileName() != null)
workbookFileName = exportInfo.getWorkbookFileName() + ".xlsx";
try {
HSSFSheet sheet = wb.createSheet(exportInfo.getSheetIndex() + "");
wb.setSheetName(exportInfo.getSheetIndex(), exportInfo
.getSheetName());
/** 设置列宽 */
for (int i = 0; i < exportInfo.getTableHead().size(); i++) {
if (i == 1 || i == exportInfo.getTableHead().size() - 1) {
sheet.setColumnWidth(i, 7000);
} else {
sheet.setColumnWidth(i, 4000);
}
}
/** 合并单元格 */
//Region(int rowFrom, short colFrom, int rowTo, short colTo)与CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (exportInfo.getTableHead().size() - 1)));
/** 表 标题 */
HSSFRow row = sheet.createRow((short) 0);
row.setHeight((short) 500); // 设置行高
HSSFFont titleFont = wb.createFont();
titleFont.setFontName("宋体");
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBoldweight((short) 20);
HSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 居中
row = this.createCell(row, (short) 0, titleStyle, exportInfo
.getSheetTitle());
/** 表头 */
HSSFFont headFont = wb.createFont();
headFont.setFontName("宋体");
headFont.setFontHeightInPoints((short) 12);
headFont.setBoldweight((short) 20);
HSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
HSSFRow row2 = sheet.createRow((short) 1);
row2.setHeight((short) 400); // 设置行高
for (int i = 0; i < exportInfo.getTableHead().size(); i++) {
row2 = this.createCell(row2, (short) i, headStyle, exportInfo
.getTableHead().get(i));
}
/** 表体 */
HSSFFont font = wb.createFont();
/** 设置字体样式 */
font.setFontName("宋体");
HSSFCellStyle cellStyle = wb.createCellStyle();
if (exportInfo.getCellDataFomat() != null
&& !"".equals(exportInfo.getCellDataFomat())) {
short stringFormat = HSSFDataFormat.getBuiltinFormat(exportInfo
.getCellDataFomat());
if (stringFormat != -1) {
cellStyle.setDataFormat(stringFormat);
}
}
cellStyle.setFont(font);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居左
for (int i = 0; i < exportInfo.getRowList().size(); i++) {
cellList = (Vector) exportInfo.getRowList().get(i);
HSSFRow row3 = sheet.createRow((short) i + 2);
row3.setHeight((short) 300); // 设置行高
for (int j = 0; j < cellList.size(); j++) {
row3 = this.createCell(row3, (short) j, cellStyle, cellList
.get(j));
}
}
} catch (Exception ex) {
log.info("error while create work book sheet ", ex);
}
}
/**
* 创建 包含多个bookSheet 的 Execl 文件
*
* @param wb
* HSSFWorkbook
* @param exportInfo
* BaseDataExportInfo
*/
public void createMoreWorkBookSheet(BaseDataExportInfo exportInfo) {
if (exportInfo.getWorkbookFileName() != null)
workbookFileName = exportInfo.getWorkbookFileName() + ".xls";
try {
for (int n = 0; n < exportInfo.getRowList().size(); n++) {
// JOptionPane.showMessageDialog(null,"第" +(n+1)+ "分页!");
HSSFSheet sheet = wb.createSheet(n + "");
wb.setSheetName(n, exportInfo.getSheetName()+ " " + (n + 1));
/** 设置列宽 */
// 设置第二列和倒数第二类的宽度为7000,其它列宽度为4000
for (int i = 0; i < exportInfo.getTableHead().size(); i++) {
if (i == 1 || i == exportInfo.getTableHead().size() - 1) {
sheet.setColumnWidth( i, 7000);
} else {
sheet.setColumnWidth( i, 4000);
}
}
/** 合并单元格 */
// 生成标题行, Region的四个参数分别对应 (x1,y1,x2,y2)
sheet.addMergedRegion(new Region(0, (short) 0, 0,
(short) (exportInfo.getTableHead().size() - 1)));
/** 表标题 */
HSSFRow row = sheet.createRow((short) 0);
row.setHeight((short) 500); // 设置行高
// 格式化表标题
HSSFFont titleFont = wb.createFont();
titleFont.setFontName("宋体");
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBoldweight((short) 20);
HSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 居中
row = this.createCell(row, (short) 0, titleStyle, exportInfo
.getSheetTitle());
/** 表头 */
// 格式化表头
HSSFFont headFont = wb.createFont();
headFont.setFontName("宋体");
headFont.setFontHeightInPoints((short) 12);
headFont.setBoldweight((short) 20);
HSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
HSSFRow row2 = sheet.createRow((short) 1);
row2.setHeight((short) 400); // 设置行高
for (int i = 0; i < exportInfo.getTableHead().size(); i++) {
row2 = this.createCell(row2, (short) i, headStyle,
exportInfo.getTableHead().get(i));
}
/** 表体 */
// 格式化表体
HSSFFont font = wb.createFont();
font.setFontName("宋体");
HSSFCellStyle cellStyle = wb.createCellStyle();
if (exportInfo.getCellDataFomat() != null
&& !"".equals(exportInfo.getCellDataFomat())) {
short stringFormat = HSSFDataFormat
.getBuiltinFormat(exportInfo.getCellDataFomat());
if (stringFormat != -1) {
cellStyle.setDataFormat(stringFormat);
}
}
cellStyle.setFont(font);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居左
for (int i = 0; i < 98; i++) {
if (i < exportInfo.getRowList().size()) {
if ((i + n * 100) >= exportInfo.getRowList().size())
break;
Vector cellList = null;
cellList = (Vector) exportInfo.getRowList().get(
i + n * 100);
HSSFRow row3 = sheet.createRow((short) i + 2);
row3.setHeight((short) 300); // 设置行高
for (int j = 0; j < cellList.size(); j++) {
row3 = this.createCell(row3, (short) j, cellStyle,
cellList.get(j));
}
}
}
if ((100 * (n + 1) - 1) >= exportInfo.getRowList().size())
break;
}
} catch (Exception ex) {
log.info("error while create work book sheet ", ex);
}
}
/**
* 创建单元格
*
*
* @param row
* HSSFRow
* @param cellIndex
* short
* @param cellStyle
* HSSFCellStyle
* @param cellValue
* Object
* @return HSSFRow
*/
public HSSFRow createCell(HSSFRow row, int cellIndex,
HSSFCellStyle cellStyle, Object cellValue) {
HSSFCell cell = row.createCell( cellIndex);
try {
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
if (cellStyle != null) {
cell.setCellStyle(cellStyle);
}
if (cellValue == null) {
cell.setCellValue("");
} else if (cellValue instanceof Boolean) {
cell.setCellValue(((Boolean) cellValue).booleanValue());
} else if (cellValue instanceof String) {
cell.setCellValue((String.valueOf(cellValue)));
} else if (cellValue instanceof Date) {
cell.setCellValue((Date) cellValue);
} else {
cell.setCellValue("");
}
// log.info("this cell value is " + cellValue);
} catch (Exception ex) {
log.error("error while execut create cell ", ex);
}
return row;
}
/**
* 读取Excel 表
*
*
* @param aSheet
* HSSFSheet
* @throws Exception
* @return List
*/
public List<Object> readWeekBookSheet(Sheet aSheet) throws Exception {
List<Object> rowList = new ArrayList<Object>();
Vector<String> rowVector = null;
int rowNum = 1;
int cellNum = 1;
int maxCellNum = aSheet.getRow(1).getLastCellNum();
// HSSFCellStyle cellStyle = wb.createCellStyle();
// short stringFormat = HSSFDataFormat.getBuiltinFormat("@"); //请参考HSSFDataFormat内置的数据类型,例如"@"代表文本
// cellStyle.setDataFormat(stringFormat);
for (int rowNumOfSheet = 2; rowNumOfSheet <= aSheet.getPhysicalNumberOfRows(); rowNumOfSheet++) {
rowNum = rowNumOfSheet;
if (null != aSheet.getRow(rowNumOfSheet))
{
Row aRow = aSheet.getRow(rowNumOfSheet);
rowVector = new Vector<String>();
// System.out.println("==============================="+aRow.getCell(2).getCellType());
for (short cellNumOfRow = 0; cellNumOfRow <= maxCellNum; cellNumOfRow++)
{
cellNum = cellNumOfRow;
if (null != aRow.getCell(cellNumOfRow))
{
Cell aCell = aRow.getCell(cellNumOfRow);
int cellType = aCell.getCellType();
switch (cellType) {
case HSSFCell.CELL_TYPE_NUMERIC: // 整形
if (HSSFDateUtil.isCellDateFormatted(aCell))
{
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
rowVector.add(cellNumOfRow, format.format(aCell.getDateCellValue()));
}
else
{
rowVector.add(cellNumOfRow, String
.valueOf(aCell.getNumericCellValue()));
}
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串型
rowVector.add(cellNumOfRow, aCell
.getStringCellValue().trim());
break;
case HSSFCell.CELL_TYPE_FORMULA: // double 型
rowVector.add(cellNumOfRow, String.valueOf(aCell.getStringCellValue()));
break;
case HSSFCell.CELL_TYPE_BLANK: // 空字符
rowVector.add(cellNumOfRow, "");
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔型
rowVector.add(cellNumOfRow, String
.valueOf(aCell.getBooleanCellValue()));
break;
default:
System.out.println("gggggggggggggggggggggggg"+cellType);
rowVector.add(cellNumOfRow, "");
}
}
else
{
rowVector.add(cellNumOfRow, "");
}
}
rowList.add(rowVector);
}
}
return rowList;
}
/**
* 写Execl 文件
*
* @param wb
* HSSFWorkbook
* @param outPutStream
* OutputStream
*/
public void writeWorkBook(HttpServletResponse response) {
try {
OutputStream outputStream = new BufferedOutputStream(response
.getOutputStream());
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-disposition", "attachment;filename=\""
+ new String(workbookFileName.getBytes(), response
.getCharacterEncoding()) + "\"");
wb.write(outputStream);
outputStream.close();
} catch (Exception ex) {
log.error("error while create work book ", ex);
}
}
/**
* 修改properties文件
* @param properties
* @param pathFile
*/
public static void modifyProperties(Properties properties,String pathFile)
{
InputStream inputStream = null;
OutputStream fos = null;
Properties tempProper = new Properties();
File file = new File(pathFile);
try
{
inputStream = new FileInputStream(file);
tempProper.load(inputStream);
fos = new FileOutputStream(file);
tempProper.setProperty("userName", properties.getProperty("userName"));
tempProper.setProperty("passwd", properties.getProperty("passwd"));
tempProper.store(fos, null);
}
catch (IOException e)
{
System.err.println(e.getStackTrace());
}
finally
{
try
{
if(null != fos)
{
fos.close();
}
if(null != inputStream)
{
inputStream.close();
}
}
catch (IOException e)
{
System.err.println(e.getStackTrace());
}
}
}
/**
* 读取Peoperties
* @param args
*/
/**
* 得到属性文件实例
*/
public static Properties getPropertiesByFile(String filePath)
{
InputStream inputStream = null;
Properties properties = new Properties();
try
{
File file = new File(filePath);
inputStream = new FileInputStream(file);
properties.load(inputStream);
}
catch (Exception e)
{
System.out.println(e.getMessage());
}
finally
{
try
{
inputStream.close();
}
catch (IOException e)
{
System.out.println(e.getMessage());
}
}
return properties;
}
public static void main(String[] args)
{
//需要导入poi-bin-3.8-20120326.zip包
ProcessXSL xsl =new ProcessXSL();
Workbook workBook = null;
try {
try {
workBook = new XSSFWorkbook(new FileInputStream("d:/资源池人员跟踪_20120911.xlsx")); // 支持2007
} catch (Exception ex) {
workBook = new HSSFWorkbook(new FileInputStream(
"d:/资源池人员跟踪_20120911.xlsx")); // 支持2003及以前
}
Sheet aSheet = workBook.getSheetAt(0);
List<Object> list = xsl.readWeekBookSheet(aSheet);
System.out.println("====================================="
+ list.size());
for (Object object : list)
{
System.out.println(object);
}
}
catch (Exception e1)
{
e1.printStackTrace();
}
}
}
发表评论
-
java 中的文件读取信息
2013-03-11 08:56 1058import java.io.BufferedReader ... -
oracle结果集的操作信息
2013-03-04 16:22 914众所周知的几个结果集集合操作命令,今天详细地测试了一下,发现一 ... -
js正则表达式(二)
2013-01-09 11:20 886首先加个重要的东西 * ... -
java路径问题以及java对文件的基本操作信息
2012-12-19 14:09 9751.基本概念的理解 绝对 ... -
JS正则表达式
2012-11-15 17:10 852function isTrueName(s) { var pa ... -
java公用类
2012-11-15 17:05 864package cn.org.jshuwei.j2ee.uti ... -
java规范信息
2012-10-30 08:44 27891 一、判断选择题(每题1分) 1. 表达式要在低优先级操作符 ... -
java时间公用和StringUnitl以及java调用存储过程
2012-10-16 17:38 16161 构建存储过程的语句信息 /** * 从Fun ... -
jquery批量删除
2012-09-20 14:31 3389<%@ page language="java ... -
java操作execl文件
2012-09-19 08:53 987package com.huawei.bss.execlCom ... -
通过onkeypress和onkeydown事件禁用键盘中某些键
2012-09-17 15:09 953http://zywang.iteye.com/blog/70 ... -
properties的修改
2012-09-14 16:05 1012public static void modifyProper ... -
java学习的一点记录
2012-09-12 16:15 1236public class Tools { stati ... -
STRUTS2与JSON的LIST和MAP对象返回
2012-09-07 14:57 6686<%@ page language="java ... -
struts2 iterator双重叠迭取值
2012-09-05 18:08 1260•效果:Map<String,List<Derpa ... -
struts2多个配置文件的应用
2012-09-05 10:10 1075<!-- 定义Struts2的核心Filter --&g ... -
java中使用net.sf.json对json进行解析
2012-09-04 12:24 1023作者: http://zhangnet1.iteye.com/ ... -
XML的TreeConfig
2012-09-04 10:20 912/** * <?xml version=&qu ... -
JSON与 STRuts2
2012-09-04 10:20 1311package com.huawei.cmclient.com ... -
sql在不同数据库查询前几条数据
2012-09-03 15:14 800sql在不同数据库查询前几条数据 1. ORACLE ...
相关推荐
2003兼容2007的补丁包,2003word ,2003execl,兼容2007版本的word execl
本文档包括从Excel读取数据,生成新的Excel,以及修改Excel,还要java操作Excel所需都jar包。
java解析execl文件,可以读取execl每个sheet指定单元格数据
Java poi复制execl的sheet页,跨文件操作,数据和样式全部复制,工具类
java操作execl csdn下载 是word文档,实现代码在里面
使用java jxl插件读取execl文件. 内含jar包.可下载直接运行使用.
java数据导入execl
非常好用的Java操作piojar包!!!!!!!!!!请大家多多关照 非常好用的Java操作piojar包!!!!!!!!!!请大家多多关照 非常好用的Java操作piojar包!!!!!!!!!!请大家多多关照
java文件导出为execl
C#转换PDF文件为word Execl文件 C#转换PDF文件为word Execl文件C#转换PDF文件为word Execl文件 C#转换PDF文件为word Execl文件C#转换PDF文件为word Execl文件C#转换PDF文件为word Execl文件C#转换PDF文件为word ...
NULL 博文链接:https://xiaoyao8903.iteye.com/blog/1143940
详细介绍java开发中对execl文件的绘制等基本操作,包括poi和jxl两种方式。
可以读取2007,2003 的EXECL,
本资源为基于Java的execl导入的一个小Demo,这里采用的主要开发框架是Struts2,但这个execl导入功能也是能够轻松转移到其他开发框架的,要注意的是其中的jar包。本Demo中的实体类中所声明的字段,就是要导入的数据...
java只如何实现打印 导出成execljava只如何实现打印 导出成execljava只如何实现打印 导出成execljava只如何实现打印 导出成execljav
php操作execl文件,将文件数据显示在web网页中,并且能够根据填入数据表名能将execl数据保存在mysql中
解压后直接导出 My Eclipse 中使用
* @描述:测试excel读取 * * 导入的jar包 * * poi-3.8-beta3-20110606.jar * * poi-ooxml-3.8-beta3-20110606.jar * * poi-examples-3.8-beta3-20110606.jar * * poi-excelant-3.8-beta3-20110606....
只需要指定模板名称和目标名称,即可实现execl文件下载