ExcelUtil
来源:互联网 发布:淘宝的规律 编辑:程序博客网 时间:2024/05/21 04:25
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil
{
/* the log. */
private static final Log log = LogFactory.getLog(ExcelUtil.class.getName());
/**
* Get the Workbook Object By FilePath.
*
* @param filePath
* <filePath>.
* @return Workbook.
*/
public static Workbook getWorkbook(String filePath)
{
if (StringUtils.isBlank(filePath))
{
log.error("the input param:[" + filePath + " ]is null,getWorkbook failed.");
return null;
}
Workbook wb = null;
try
{
if (isExcel2007(filePath))
{
// vesion2007above.
wb = new XSSFWorkbook(new FileInputStream(new File(filePath)));
}
else
{
// vesion2003.
wb = new HSSFWorkbook(new FileInputStream(new File(filePath)));
}
}
catch (FileNotFoundException e)
{
log.error("getWorkbook failed,FileNotFoundException.", e);
}
catch (IOException e)
{
log.error("getWorkbook failed,IOException.", e);
}
catch (Exception e)
{
log.error("getWorkbook failed.", e);
}
return wb;
}
/**
*
* Write Excel File for version2003 and version2007above.
*
* @param wb
* <Workbook>.
* @param filePath
* ExcelFilePath.
*/
public static void writeExcel(Workbook wb, String filePath)
{
FileOutputStream fos = null;
try
{
fos = new FileOutputStream(filePath);
wb.write(fos);
fos.flush();
}
catch (FileNotFoundException e)
{
log.error("writeExcel failed,FileNotFoundException.", e);
}
catch (IOException e)
{
log.error("writeExcel failed,IOException.", e);
}
catch (Exception e)
{
log.error("writeExcel failed.", e);
}
finally
{
IOUtils.closeQuietly(fos);
}
}
/**
* Judge the excel file whether 2003 or 2007 above.
*
* @param filePath
* <filePath>.
* @return boolean<case <suffix> when 2003 then false else true>.
*/
public static boolean isExcel2007(String filePath)
{
/* the pointer index. */
int pointIndex = filePath.lastIndexOf(IConstants.POINT);
/* the suffix. */
String suffix = filePath.substring(pointIndex);
if (IConstants.EXCEL2007_SUFFIX.equals(suffix))
{
return true;
}
return false;
}
/****************************** DealWith Excel Version2007above Methods ******************************/
/**
* Create a excel sheet.
*
* @param wb
* <XSSFWorkbook>.
* @param sheetName
* <sheetName>.
* @return XSSFSheet <XSSFSheet>.
*/
public static XSSFSheet createSheet(XSSFWorkbook wb, String sheetName)
{
XSSFSheet sheet = wb.createSheet(sheetName);
return sheet;
}
/**
* Create a excel row.
*
* @param height
* <height>.
* @param index
* <index>.
* @param sheet
* <sheet>.
* @return XSSFRow <XSSFRow>.
*/
public static XSSFRow createRow(short height, int index, XSSFSheet sheet)
{
XSSFRow row = sheet.createRow(index);
if (height != 0)
{
row.setHeight(height);
}
return row;
}
/**
* Create Cell For XSSFRow.
*
* @param color
* <XSSFColor>.
* @param cellIndex
* <cellIndex>.
* @param value
* <value>.
* @param row
* <XSSFRow>.
* @param style
* <XSSFCellStyle>
* @return XSSFCell <XSSFCell>.
*/
public static XSSFCell createCell(XSSFColor color, int cellIndex, String value, XSSFRow row, XSSFCellStyle style)
{
XSSFCell cell = row.createCell(cellIndex);
cell.setCellValue(value);
cell.setCellStyle(cellStyle(style, color));
return cell;
}
/**
* Create Cell Style For XSSFWorkbook.
*
* @param xfwb
* <XSSFWorkbook>.
* @return XSSFCellStyle <XSSFCellStyle>.
*/
public static XSSFCellStyle createCellStyle(XSSFWorkbook xfwb)
{
return xfwb.createCellStyle();
}
/**
* Create cellStyle.
*
* @param style
* <XSSFCellStyle>.
* @param color
* <XSSFColor>.
* @return XSSFCellStyle <XSSFCellStyle>.
*/
public static XSSFCellStyle cellStyle(XSSFCellStyle style, XSSFColor color)
{
XSSFCellStyle cellStyle = baseStyle(style);
if (color != null)
{
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(color);
}
return cellStyle;
}
/**
* Create baseStyle for Cell.
*
* @param cellStyle
* <XSSFCellStyle>.
* @return XSSFCellStyle <XSSFCellStyle>.
*/
public static XSSFCellStyle baseStyle(XSSFCellStyle cellStyle)
{
cellStyle.setWrapText(true);
cellStyle.setAlignment(HorizontalAlignment.LEFT);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return cellStyle;
}
/****************************** DealWith Excel Version2003 Methods ******************************/
/**
* Create a excel sheet.
*
* @param wb
* <HSSFWorkbook>.
* @param sheetName
* <sheetName>.
* @return HSSFSheet <HSSFSheet>.
*/
public static HSSFSheet createSheet(HSSFWorkbook wb, String sheetName)
{
HSSFSheet sheet = wb.createSheet(sheetName);
return sheet;
}
/**
* Create a excel row.
*
* @param height
* <height>.
* @param index
* <index>.
* @param sheet
* <sheet>.
* @return HSSFRow <HSSFRow>.
*/
public static HSSFRow createRow(short height, int index, HSSFSheet sheet)
{
HSSFRow row = sheet.createRow(index);
if (height != 0)
{
row.setHeight(height);
}
return row;
}
/**
* Create Cell For Workbook.
*
* @param color
* <color>.
* @param cellIndex
* <cellIndex>.
* @param value
* <value>.
* @param row
* <row>.
* @param style
* <style>
* @return HSSFCell <HSSFCell>.
*/
public static HSSFCell createCell(short color, int cellIndex, String value, HSSFRow row, HSSFCellStyle style)
{
HSSFCell cell = row.createCell(cellIndex);
cell.setCellValue(value);
cell.setCellStyle(cellStyle(style, color));
return cell;
}
/**
* Setup Column With For Cell.
*
* @param column
* <column>.
* @param width
* <width>.
* @param sheet
* <Sheet>.
*/
public static void setColumnWith(int column, int width, Sheet sheet)
{
sheet.setColumnWidth(column, width);
}
/**
* Create a cell style.
*
* @param hfwb
* <hfwb>.
* @return HSSFCellStyle <HSSFCellStyle>.
*/
public static HSSFCellStyle createCellStyle(HSSFWorkbook hfwb)
{
return hfwb.createCellStyle();
}
/**
* Create cellStyle.
*
* @param style
* <HSSFCellStyle>.
* @param color
* <color>.
* @return HSSFCellStyle <HSSFCellStyle>.
*/
public static HSSFCellStyle cellStyle(HSSFCellStyle style, short color)
{
HSSFCellStyle cellStyle = baseStyle(style);
if (color != (short) IConstants.ZERO)
{
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(color);
}
return cellStyle;
}
/**
* Create baseStyle for Cell.
*
* @param cellStyle
* <HSSFCellStyle>
* @return HSSFCellStyle <HSSFCellStyle>.
*/
public static HSSFCellStyle baseStyle(HSSFCellStyle cellStyle)
{
cellStyle.setWrapText(true);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return cellStyle;
}
}
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil
{
/* the log. */
private static final Log log = LogFactory.getLog(ExcelUtil.class.getName());
/**
* Get the Workbook Object By FilePath.
*
* @param filePath
* <filePath>.
* @return Workbook.
*/
public static Workbook getWorkbook(String filePath)
{
if (StringUtils.isBlank(filePath))
{
log.error("the input param:[" + filePath + " ]is null,getWorkbook failed.");
return null;
}
Workbook wb = null;
try
{
if (isExcel2007(filePath))
{
// vesion2007above.
wb = new XSSFWorkbook(new FileInputStream(new File(filePath)));
}
else
{
// vesion2003.
wb = new HSSFWorkbook(new FileInputStream(new File(filePath)));
}
}
catch (FileNotFoundException e)
{
log.error("getWorkbook failed,FileNotFoundException.", e);
}
catch (IOException e)
{
log.error("getWorkbook failed,IOException.", e);
}
catch (Exception e)
{
log.error("getWorkbook failed.", e);
}
return wb;
}
/**
*
* Write Excel File for version2003 and version2007above.
*
* @param wb
* <Workbook>.
* @param filePath
* ExcelFilePath.
*/
public static void writeExcel(Workbook wb, String filePath)
{
FileOutputStream fos = null;
try
{
fos = new FileOutputStream(filePath);
wb.write(fos);
fos.flush();
}
catch (FileNotFoundException e)
{
log.error("writeExcel failed,FileNotFoundException.", e);
}
catch (IOException e)
{
log.error("writeExcel failed,IOException.", e);
}
catch (Exception e)
{
log.error("writeExcel failed.", e);
}
finally
{
IOUtils.closeQuietly(fos);
}
}
/**
* Judge the excel file whether 2003 or 2007 above.
*
* @param filePath
* <filePath>.
* @return boolean<case <suffix> when 2003 then false else true>.
*/
public static boolean isExcel2007(String filePath)
{
/* the pointer index. */
int pointIndex = filePath.lastIndexOf(IConstants.POINT);
/* the suffix. */
String suffix = filePath.substring(pointIndex);
if (IConstants.EXCEL2007_SUFFIX.equals(suffix))
{
return true;
}
return false;
}
/****************************** DealWith Excel Version2007above Methods ******************************/
/**
* Create a excel sheet.
*
* @param wb
* <XSSFWorkbook>.
* @param sheetName
* <sheetName>.
* @return XSSFSheet <XSSFSheet>.
*/
public static XSSFSheet createSheet(XSSFWorkbook wb, String sheetName)
{
XSSFSheet sheet = wb.createSheet(sheetName);
return sheet;
}
/**
* Create a excel row.
*
* @param height
* <height>.
* @param index
* <index>.
* @param sheet
* <sheet>.
* @return XSSFRow <XSSFRow>.
*/
public static XSSFRow createRow(short height, int index, XSSFSheet sheet)
{
XSSFRow row = sheet.createRow(index);
if (height != 0)
{
row.setHeight(height);
}
return row;
}
/**
* Create Cell For XSSFRow.
*
* @param color
* <XSSFColor>.
* @param cellIndex
* <cellIndex>.
* @param value
* <value>.
* @param row
* <XSSFRow>.
* @param style
* <XSSFCellStyle>
* @return XSSFCell <XSSFCell>.
*/
public static XSSFCell createCell(XSSFColor color, int cellIndex, String value, XSSFRow row, XSSFCellStyle style)
{
XSSFCell cell = row.createCell(cellIndex);
cell.setCellValue(value);
cell.setCellStyle(cellStyle(style, color));
return cell;
}
/**
* Create Cell Style For XSSFWorkbook.
*
* @param xfwb
* <XSSFWorkbook>.
* @return XSSFCellStyle <XSSFCellStyle>.
*/
public static XSSFCellStyle createCellStyle(XSSFWorkbook xfwb)
{
return xfwb.createCellStyle();
}
/**
* Create cellStyle.
*
* @param style
* <XSSFCellStyle>.
* @param color
* <XSSFColor>.
* @return XSSFCellStyle <XSSFCellStyle>.
*/
public static XSSFCellStyle cellStyle(XSSFCellStyle style, XSSFColor color)
{
XSSFCellStyle cellStyle = baseStyle(style);
if (color != null)
{
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(color);
}
return cellStyle;
}
/**
* Create baseStyle for Cell.
*
* @param cellStyle
* <XSSFCellStyle>.
* @return XSSFCellStyle <XSSFCellStyle>.
*/
public static XSSFCellStyle baseStyle(XSSFCellStyle cellStyle)
{
cellStyle.setWrapText(true);
cellStyle.setAlignment(HorizontalAlignment.LEFT);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return cellStyle;
}
/****************************** DealWith Excel Version2003 Methods ******************************/
/**
* Create a excel sheet.
*
* @param wb
* <HSSFWorkbook>.
* @param sheetName
* <sheetName>.
* @return HSSFSheet <HSSFSheet>.
*/
public static HSSFSheet createSheet(HSSFWorkbook wb, String sheetName)
{
HSSFSheet sheet = wb.createSheet(sheetName);
return sheet;
}
/**
* Create a excel row.
*
* @param height
* <height>.
* @param index
* <index>.
* @param sheet
* <sheet>.
* @return HSSFRow <HSSFRow>.
*/
public static HSSFRow createRow(short height, int index, HSSFSheet sheet)
{
HSSFRow row = sheet.createRow(index);
if (height != 0)
{
row.setHeight(height);
}
return row;
}
/**
* Create Cell For Workbook.
*
* @param color
* <color>.
* @param cellIndex
* <cellIndex>.
* @param value
* <value>.
* @param row
* <row>.
* @param style
* <style>
* @return HSSFCell <HSSFCell>.
*/
public static HSSFCell createCell(short color, int cellIndex, String value, HSSFRow row, HSSFCellStyle style)
{
HSSFCell cell = row.createCell(cellIndex);
cell.setCellValue(value);
cell.setCellStyle(cellStyle(style, color));
return cell;
}
/**
* Setup Column With For Cell.
*
* @param column
* <column>.
* @param width
* <width>.
* @param sheet
* <Sheet>.
*/
public static void setColumnWith(int column, int width, Sheet sheet)
{
sheet.setColumnWidth(column, width);
}
/**
* Create a cell style.
*
* @param hfwb
* <hfwb>.
* @return HSSFCellStyle <HSSFCellStyle>.
*/
public static HSSFCellStyle createCellStyle(HSSFWorkbook hfwb)
{
return hfwb.createCellStyle();
}
/**
* Create cellStyle.
*
* @param style
* <HSSFCellStyle>.
* @param color
* <color>.
* @return HSSFCellStyle <HSSFCellStyle>.
*/
public static HSSFCellStyle cellStyle(HSSFCellStyle style, short color)
{
HSSFCellStyle cellStyle = baseStyle(style);
if (color != (short) IConstants.ZERO)
{
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(color);
}
return cellStyle;
}
/**
* Create baseStyle for Cell.
*
* @param cellStyle
* <HSSFCellStyle>
* @return HSSFCellStyle <HSSFCellStyle>.
*/
public static HSSFCellStyle baseStyle(HSSFCellStyle cellStyle)
{
cellStyle.setWrapText(true);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return cellStyle;
}
}
0 0
- ExcelUtil
- excelUtil
- ExcelUtil
- ExcelUtil
- ExcelUtil
- ExcelUtil
- ExcelUtil
- ExcelUtil.java
- ExcelUtil.java
- ExcelUtil(JXL)
- ExcelUtil excel工具类
- ExcelUtil-注解-SXSSFWorkbook
- Selenium学习16--ExcelUtil
- Excel工具类ExcelUtil
- 导入导出Excel工具类ExcelUtil
- 导入导出Excel工具类ExcelUtil
- 导入导出Excel工具类ExcelUtil
- java 导入导出Excel工具类ExcelUtil
- mysql(基础篇)------命令行操作MySQL的相关语句以及 SQLyon的使用
- golang获取命令行参数
- AngularJS中控制器之间通信方法
- 最详细的 Toolbar 开发实践总结
- windows网络编程 gethostbyname()
- ExcelUtil
- 三块300GSAS硬盘一块硬盘状态灯为红色怎么解决
- 百度地图SDK 手机报错java.lang.UnsatisfiedLinkError: No implementation found for int--binbinyang
- 面试题42:翻转单词顺序,左旋转字符串
- react-native run-android 慢
- java基础综合练习
- 侧滑菜单,cookie 的使用在刷新以后依然保持原来的展开或者收缩样式。
- MongoDB Sharding及数据库设计
- js删除数组中某一项或几项的几种方法