package jjgk.erms.excelTemplate;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.xssf.usermodel.XSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
/**
* Excel操作以及相关的公共方法
* @author ChenPingFan
*
*/
public class ExcelUtil {
/* Excel文档 */
public static HSSFWorkbook workbook;
public static XSSFWorkbook workbooks;
public static final ExcelUtil excelutil = new ExcelUtil();
public static ExcelUtil getInstance()
{
return excelutil;
}
// private Object sheet;
/**加载相应的EXCEL模板**/
public HSSFWorkbook loadexcel(String pa,POIFSFileSystem poifile)
{
InputStream input = null;
try {
input = new FileInputStream(new File(pa));
} catch (FileNotFoundException e) {
e.printStackTrace();
}
try {
poifile = new POIFSFileSystem(input);
workbook = new HSSFWorkbook(poifile);
} catch (IOException e) {
e.printStackTrace();
}
finally{
try {
input.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return workbook;
}
/**给Excel字符串赋值**/
public void setStringValue(HSSFCell cell, String value) {
cell.setCellValue(value);
}
/**给Excel数值赋值**/
public void setDoubleValue(double value,int rownum,int cellnum,Sheet sheet) {
Cell cell = sheet.getRow(rownum).getCell(cellnum);
cell.setCellValue(value);
}
/**给Excel数值赋值**/
public void setIntValue(int value,int rownum,int cellnum,Sheet sheet) {
Cell cell = sheet.getRow(rownum).getCell(cellnum);
cell.setCellValue(value);
}
/**给单元格赋值**/
public void setCellValue(String value, int rownum, int cellnum,Sheet sheet) {
Cell cell = sheet.getRow(rownum).getCell(cellnum);
cell.setCellValue(value);
}
/**
* rownum 需要创建的行数, cellnum 需要创建的列数,,ksrow 从哪一行开始,,
* @param rownum
* @param cellnum
* @param ksrow
*/
public void creatCell(int rownum, int cellnum, int ksrow,Sheet sheet) {
for (int i = ksrow; i < rownum + ksrow; i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < cellnum; j++) {
row.createCell(j);
}
}
}
/**创建字体样式**/
public HSSFCellStyle getHssfFont() {
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 16);// 字号
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
font.setColor(HSSFFont.COLOR_NORMAL);
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFont(font);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
return style;
}
/**创建字体样式**/
public HSSFCellStyle getHssfFontDz(Workbook workbook) {
HSSFFont font = (HSSFFont) workbook.createFont();
font.setFontHeightInPoints((short) 16);// 字号
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
font.setColor(HSSFFont.COLOR_NORMAL);
HSSFCellStyle style = (HSSFCellStyle) workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFont(font);
return style;
}
/** 创建样式 */
public void createYS(int rownum, int cellnum, int ksrow,Workbook workbook,int sheetnum) {
CellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);
style.setWrapText(true);
// 设置单元格字体
// HSSFFont font = (HSSFFont) workbook.createFont();
// font.setFontName("宋体");
// font.setFontHeightInPoints((short) 11);
// style.setFont(font);
Sheet sheet = workbook.getSheetAt(sheetnum);
for (int i = ksrow; i < rownum + ksrow; i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < cellnum; j++) {
Cell cell = row.createCell(j);
cell.setCellStyle(style);
cell = null;
}
}
}
public static HSSFCellStyle getHssfFontforaqyb(){
HSSFFont font = workbook.createFont();
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setWrapText(true);//自动换行
style.setFont(font);
return style;
}
/**
* 合并单元格的方法
*
* @param sheet一个HSSFSheet对象
* @param row一个行对象
* @param rowindex行索引
* @param name单元格的值
* @param a1从第几行开始
* @param a2从第几个单元格开始
* @param a3到第几行结束
* @param a4到第几个单元格结束
*
*/
@SuppressWarnings("deprecation")
public void createCells(int indexRow, int rowindex, String name, int a1,
int a2, int a3, int a4, HSSFCellStyle cellStyle, HSSFFont font,
int flag, HSSFSheet sheet) {
Row row = sheet.createRow(indexRow);
Cell cel0 = row.createCell(rowindex);
if (flag == 0) {
cellStyle.setBorderBottom((short) 1);
cellStyle.setBorderLeft((short) 1);
cellStyle.setBorderRight((short) 1);
cellStyle.setBorderTop((short) 1);
cellStyle.setBottomBorderColor((short) 64);
cellStyle.setAlignment((short) 1);
cellStyle.setVerticalAlignment((short) 1);
cellStyle.setDataFormat((short) 49);
}
cel0.setCellStyle(cellStyle);
// cel0.setEncoding(HSSFCell.ENCODING_UTF_16);
cel0.setCellValue(name);
sheet.addMergedRegion(new Region(a1, (short) a2, a3, (short) a4));
if (cellStyle != null) {
if (font != null) {
cellStyle.setFont(font);
}
}
}
//************************系统基础的公共方法(为了图方便,不单独起一个类)*****************************//
/**
* 获取当前年份
* @return
*/
public static String NowYear() {
Calendar claender = Calendar.getInstance();
int year = claender.get(Calendar.YEAR);
int month = claender.get(Calendar.MONTH);
//当 月份为0时是因为跨年,年度为当前年度,所以要减去1
if(month == 0){
year = year -1;
}
return String.valueOf(year);
}
/**
* 获取当前的月份(实际是上月,因为本月调用的数据都是上个月的数据)
* @return
*/
public static String NowMonth(){
Calendar claender = Calendar.getInstance();
int month = claender.get(Calendar.MONTH);
//跨年时1月份的上个月是12月
if(month == 0){
month = 12;
}
return String.valueOf(month);
}
/**
* 该月当天的日期
* @return
*/
public static String NowDay(){
Calendar claender = Calendar.getInstance();
String day = String.valueOf(claender.get(Calendar.DAY_OF_MONTH));
return day;
}
/**获取当前时间(年月日)**/
public static String sysDate(){
Date today = new Date();
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
return format.format(today);
}
public static String nowYear_today(){
Date today = new Date();
SimpleDateFormat format = new SimpleDateFormat("yyyy");
return format.format(today);
}
public static String nowMonth_today(){
Date today = new Date();
SimpleDateFormat format = new SimpleDateFormat("MM");
return format.format(today);
}
/**
* 获取上个月的年份和月份(实际是倒退了两个月,因为本月调用的数据都是上个月的数据)
* @return
*/
public static String sysOldDate()
{
Calendar calendar = Calendar.getInstance();
calendar.getTime();
calendar.add(Calendar.MONTH, -2);
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy.MM");
return dateFormat.format(calendar.getTime());
}
/**取得下个月的年份和月份(实际上这才是本月)**/
public static String sysNextDate()
{
Calendar calendar = Calendar.getInstance();
calendar.getTime();
calendar.add(Calendar.MONTH, 0);
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy.MM");
return dateFormat.format(calendar.getTime());
}
/**将字符串格式化成时间**/
public String formatTime(String time) throws ParseException
{
if(null != StringUtils.trimToNull(time)){
SimpleDateFormat simp = new SimpleDateFormat("yyyy-mm-dd");
Date t = simp.parse(time);
String date = simp.format(t);
return date;
}
return "";
}
/**
* 精确小数点为N位
*/
private double RoundToN(double num, int weishu) {
String strr_db = String.valueOf(num);
return RoundToN(strr_db, weishu);
}
/**
* 大数据类型四舍五入
*
* @param str
* @return
*/
private String decimalConvert(String strNum) {
BigDecimal d = new BigDecimal(0);
// 根据小数点进行切割
if (!"".equals(strNum)) {
String[] strArray = strNum.split("\\.");
if (strArray.length > 1) {
d = new BigDecimal(strArray[0]);
double dNum = Long.parseLong(strArray[1])
/ Math.pow(10, strArray[1].length());
if (dNum >= 0.5) {
d = d.add(new BigDecimal(1));
}
} else {
d = new BigDecimal(strNum);
}
}
return d.toString();
}
/**
* 精确小数点为N位
*/
private double RoundToN(String num, int weishu) {
String strr_db = num;
double db = 0;
if (strr_db == null || "".equalsIgnoreCase(strr_db.trim())) {
return db;
}
if (strr_db.indexOf(".") == -1) {
} else {
db = Float.parseFloat(strr_db);
if (db > 0) {
db = db * Math.pow(10, weishu);
db = db + Double.parseDouble("0.5");
db = Double.parseDouble(String.valueOf((int) db));
db = db / Math.pow(10, weishu);
return db;
} else if (db < 0) {
db = 0 - db;
db = db * Math.pow(10, weishu);
db = db + Double.parseDouble("0.5");
db = Double.parseDouble(String.valueOf((int) db));
db = db / Math.pow(10, weishu);
db = 0 - db;
return db;
}
}
db = Double.parseDouble(strr_db);
return db;
}
/**新建相应的文件夹路径**/
public String createDir(String new_path) {
File newFile = new File(new_path);
if(!newFile.exists()) {
newFile.mkdirs();
}
return new_path;
}
public String getSysPath(String Dirname) {
SimpleDateFormat match = new SimpleDateFormat("yyyy-MM-dd");
String new_path = ExcelUtil.getSystemPath() + "/"
+ match.format(new Date()) + "/" + Dirname;
return new_path;
}
/**读取配置文件,获取模板的路径**/
@SuppressWarnings("unchecked")
public String getXmlConfig(String mkmc) throws Exception {
String path_excel = null;
String path = ExcelUtil.getSystemPath() + "/config/"+ "templete.xml";
String xmlPath = path.replace("/", File.separator);
SAXReader sax = new SAXReader();
Document doc = sax.read(new File(xmlPath));
Element rootElement = doc.getRootElement();
Element element = rootElement.element("monthTemplete");
List<Element> list = element.elements();
for (Element ele : list) {
ele.attributeValue("name");
if (mkmc.equals(ele.attributeValue("name"))) {
path_excel = ele.attributeValue("path");
}
}
return path_excel;
}
/**
* 获取系统的WebContent路径
* @return
*/
public static String getSystemPath() {
String path = ExcelUtil.class.getClassLoader().getResource("/").getPath().toString();
Properties prop = System.getProperties();
String osname = prop.getProperty("os.name");
if(osname.startsWith("Win")|| osname.startsWith("win")){
path = path.substring(path.indexOf("/")+1, path.indexOf("WEB-INF")-1);
}else{
path = path.substring(path.indexOf("/"), path.indexOf("WEB-INF")-1);
}
return path;
}
/**
* 输出两端日期中间的时间,以年月格式输出
* @param startTime
* @param endTime
* @return
*/
public static List<String> getListTime(String startTime,String endTime){
List<String> time_list = new ArrayList<String>();
if(startTime.equals(endTime)){
//如果两个时间相等
time_list.add(startTime);
}else
{
String startYear = startTime.substring(0, 4);
String endYear = endTime.substring(0, 4);
//取月份
String start_Month = startTime.substring(4);
String end_Month = endTime.substring(4);
int mistake = Integer.parseInt(endYear)-Integer.parseInt(startYear);
// 201105--201203
if(mistake>=1) {
if(mistake == 1) {
for(int i = Integer.parseInt(start_Month);i <=12;i++){
String rq = startYear+getFormatString(String.valueOf(i));
time_list.add(rq);
}
for(int i = 1;i<=Integer.parseInt(end_Month);i++){
String rq = endYear+getFormatString(String.valueOf(i));
time_list.add(rq);
}
}
}else {
//同一年的只算月份
for(int i = Integer.parseInt(start_Month); i<=Integer.parseInt(end_Month);i++){
String rq = startYear + getFormatString(String.valueOf(i));
time_list.add(rq);
}
}
}
return time_list;
}
public static String getFormatString(String number){
return number.length()>1?number:"0"+number;
}
/**
* 获取上个月第一天的时间
* @return
*/
public String lastMonFirstDay(){
Calendar cal = Calendar.getInstance();
int year = cal.get(Calendar.YEAR);
int month = cal.get(Calendar.MONTH) + 1;
cal.set(Calendar.DAY_OF_MONTH, 1);
cal.add(Calendar.DAY_OF_MONTH, -1);
int day = cal.get(Calendar.DAY_OF_MONTH);
String months = "";
String days = "";
if (month > 1) {
month--;
} else {
year--;
month = 12;
}
if (!(String.valueOf(month).length() > 1)) {
months = "0" + month;
} else {
months = String.valueOf(month);
}
if (!(String.valueOf(day).length() > 1)) {
days = "0" + day;
} else {
days = String.valueOf(day);
}
String firstDay = "" + year + "-" + months + "-01";
String[] lastMonth = new String[2];
lastMonth[0] = firstDay;
return firstDay;
}
/**
* 获取上个月最后一天的时间
* @return
*/
public String lastMonLastDay(){
Calendar cal = Calendar.getInstance();
int year = cal.get(Calendar.YEAR);
int month = cal.get(Calendar.MONTH) + 1;
cal.set(Calendar.DAY_OF_MONTH, 1);
cal.add(Calendar.DAY_OF_MONTH, -1);
int day = cal.get(Calendar.DAY_OF_MONTH);
String months = "";
String days = "";
if (month > 1) {
month--;
} else {
year--;
month = 12;
}
if (!(String.valueOf(month).length() > 1)) {
months = "0" + month;
} else {
months = String.valueOf(month);
}
if (!(String.valueOf(day).length() > 1)) {
days = "0" + day;
} else {
days = String.valueOf(day);
}
String lastDay = "" + year + "-" + months + "-" + days;
String[] lastMonth = new String[2];
lastMonth[1] = lastDay;
return lastDay;
}
}