private IRow CreateRow(int index, int firstColumnIndex, int lastColumnIndex, ExcelCellStyle cellStyle)
{
IRow row = _currentSheet.GetRow(index);
//ExcelCellStyle tempStyle = cellStyle == null ? _defaultExcelCellStyle : cellStyle;
if (row == null)
{
row = this._currentSheet.CreateRow(index);
for (int i = firstColumnIndex; i <= lastColumnIndex; i++)
{
row.CreateCell(i);
row.GetCell(i).CellStyle = cellStyle == null?_defaultExcelCellStyle.GetCellStyle(this.WorkBook) : cellStyle.GetCellStyle(this.WorkBook);
}
}
else
{
for (int i = firstColumnIndex; i <= lastColumnIndex; i++)
{
if (row.GetCell(i) == null)
{
row.CreateCell(i);
}
row.GetCell(i).CellStyle = cellStyle == null?_defaultExcelCellStyle.GetCellStyle(this.WorkBook) : cellStyle.GetCellStyle(this.WorkBook);
}
}
int kk = this.WorkBook.NumCellStyles;
return(row);
}
private short _defualtColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;//默認顏色值
#endregion 變量
/// <summary>
/// 創建一個新工作表對象，默認空表格。
/// </summary>
public ExcelOparete()
{
_workbook = new HSSFWorkbook();
_currentSheet = null;
//_sheetRowCount = 0;
_defaultExcelCellStyle = new ExcelCellStyle();
}
public void SetSheetName(string sheetName, string newSheetName, ref int sheetIndex)
{
int i = _workbook.GetSheetIndex(_workbook.GetSheet(sheetName));
_currentSheet = _workbook.GetSheetAt(i);
_workbook.SetSheetName(_workbook.GetSheetIndex(_currentSheet), newSheetName);
_workbook.SetSheetOrder(newSheetName, _workbook.NumberOfSheets);
_defaultExcelCellStyle = new ExcelCellStyle();
}
/// <summary>
/// 增加一個新的表格，並將新增的表格作為活動表
/// </summary>
/// <param name="sheetName">表名稱</param>
public void CloneSheet(string sheetName, string newSheetName, ref int sheetIndex)
{
int i = _workbook.GetSheetIndex(_workbook.GetSheet(sheetName));
_currentSheet = _workbook.CloneSheet(i);
sheetIndex = _workbook.GetSheetIndex(_currentSheet);
_workbook.SetSheetName(_workbook.GetSheetIndex(_currentSheet), newSheetName);
_defaultExcelCellStyle = new ExcelCellStyle();
}
private int _columnCount; //列佔的列數
#endregion
#region 構造函數
/// <summary>
/// 創建一個新的列頭對象
/// </summary>
private ExcelColumn()
{
_isSetWith = false;
_columnCount = 1;
_columnName = string.Empty;
_dataPropertyName = string.Empty;
_with = 20;
_defaultExcelCellStyle = null; // new ExcelCellStyle();
_columnExcelCellStyle = null; //new ExcelCellStyle();
//_warpText = false;
//_index = 0;
}
/// <summary>
/// 設置標題值,自動合並單元格
/// </summary>
/// <param name="titleString">標題文本</param>
/// <param name="firstRowIndex">開始行位置</param>
/// <param name="lastRowIndex">結束行位置</param>
/// <param name="firstColumnIndex">開始的列位置</param>
/// <param name="lastColumnIndex">結束的列位置</param>
/// <param name="titleExceCellStyle">單元格樣式，如果為NULL，則為默認（12號字體、加粗）</param>
public void SetTitleValue(string titleString, int firstRowIndex, int lastRowIndex, int firstColumnIndex, int lastColumnIndex, ExcelCellStyle titleExceCellStyle)
{
if (titleExceCellStyle == null)
{
titleExceCellStyle = new ExcelCellStyle(12, FontBoldWeight.BOLD);
titleExceCellStyle.HorizontalAlignment = HorizontalAlignment.CENTER;
}
for (int i = firstRowIndex; i <= lastRowIndex; i++)
{
CreateRow(i, firstColumnIndex, lastColumnIndex, titleExceCellStyle);
}
IRow row = this._currentSheet.GetRow(firstRowIndex);
if (lastColumnIndex > firstColumnIndex)
{
this._currentSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRowIndex, lastRowIndex, firstColumnIndex, lastColumnIndex));
}
row.GetCell(firstColumnIndex).SetCellValue(titleString);
}
/// <summary>
/// 克隆一個新的單元格格式對象
/// </summary>
/// <returns></returns>
public object Clone()
{
ExcelCellStyle style = new ExcelCellStyle();
style.BorderBottom = this._borderBottom;
style.BorderLeft = this._borderLeft;
style.BorderRight = this._borderRight;
style.BorderTop = this._borderTop;
style.HorizontalAlignment = this._alignment;
style.VerticalAlignment = this._verticalAlignment;
style.DataFormart = this._dataFormart;
style.FontBold = this.FontBold;
style.FontName = this.FontName;
style.FontSize = this.FontSize;
style.WarpText = this.WarpText;
style.FontName = this._fontName;
style.UnderLine = this.UnderLine;
style.FontIsItalic = this._isItalic;
style.FontColor = this._fontColor;
style.FontIsStrikeout = this._isStrikeout;
style._fontTypeOffset = this._fontTypeOffset;
return(style);
}
private string GetYdPayLogOnExport(DataTable dtSource)
{
string fn = "/XTemp/缴费明细单.xls";
string filePath = System.Web.Hosting.HostingEnvironment.MapPath(@"/XTemp");
if (System.IO.Directory.Exists(filePath) == false)
{
System.IO.Directory.CreateDirectory(filePath);
}
string filename = System.Web.Hosting.HostingEnvironment.MapPath(fn);
if (System.IO.File.Exists(filename))/*先删除已存在的文件，再汇出Excel*/
{
System.IO.File.Delete(filename);
}
if (dtSource == null || dtSource.Rows.Count == 0)
{
throw new Exception("没有数据");
}
Excel.ExcelCellStyle columnCellStyle0 = new Excel.ExcelCellStyle();
columnCellStyle0 = new Excel.ExcelCellStyle()
{
DataFormart = "0.00",
HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT
};
Excel.ExcelCellStyle columnCellStyle1 = new Excel.ExcelCellStyle();
columnCellStyle1 = new Excel.ExcelCellStyle()
{
DataFormart = "yyyy-MM-dd HH:mm:ss",
};
Excel.ExcelOparete excel = new Excel.ExcelOparete("缴费明细单");
var cellStyle = new ExcelCellStyle();
cellStyle.HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
cellStyle.FontSize = 12;
excel.SetObjectValue("缴费明细单", 0, 0, 1, cellStyle);
excel.SetObjectValue2("建筑名称", 1, 1, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["CoStrcName"], 1, 1, 1, 1);
excel.SetObjectValue2("房间名称", 2, 2, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["CoName"], 2, 2, 1, 1);
excel.SetObjectValue2("倍率", 3, 3, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["Multiply"], 3, 3, 1, 1);
excel.SetObjectValue2("单价(元/kWh)", 4, 4, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["Price"], 4, 4, 1, 1);
excel.SetObjectValue2("充值类型", 5, 5, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["IsPayS"], 5, 5, 1, 1);
excel.SetObjectValue2("充值方式", 6, 6, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["payTypeS"], 6, 6, 1, 1);
excel.SetObjectValue2("充值电量(kWh)", 7, 7, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["PayVal"], 7, 7, 1, 1);
excel.SetObjectValue2("充值金额(元)", 8, 8, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["PayAmt"], 8, 8, 1, 1);
excel.SetObjectValue2("金额(大写)", 9, 9, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["strAmt"], 9, 9, 1, 1);
excel.SetObjectValue2("操作者", 10, 10, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["Create_by"], 10, 10, 1, 1);
excel.SetObjectValue2("操作时间", 11, 11, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["Create_dt"], 11, 11, 1, 1);
excel.SaveExcelByFullFileName(filename);
return(fn);
}
/// <summary>
/// 創建一個新的列頭對象
/// </summary>
/// <param name="columnName">列頭名稱</param>
/// <param name="dataPropertyName">數據的屬性名稱</param>
/// <param name="with">列的寬</param>
/// <param name="columnExcelCellStyle">列頭的單元格式</param>
///<param name="defaultExcelCellStyle">列值的單元格式</param>
public ExcelColumn(string columnName, string dataPropertyName, int with, ExcelCellStyle columnExcelCellStyle, ExcelCellStyle defaultExcelCellStyle)
: this(columnName, dataPropertyName, with)
{
_defaultExcelCellStyle = defaultExcelCellStyle;
_columnExcelCellStyle = columnExcelCellStyle;
}
//public void setObjectValue(PCM.Common.Excel.ExcelOparete excel, object obj, int rowIndex, ref int columnIndex)
//{
// columnIndex += 1;
// excel.SetObjectValue(obj, rowIndex, columnIndex, columnIndex);
//}
/// <summary>
/// 設置一個對象的值,自動合並行列,單元格格為工作表默認的值
/// </summary>
/// <param name="value">對象</param>
/// <param name="rowIndex1">開始行位置</param>
/// <param name="rowIndex2">結束行位置</param>
/// <param name="firstColumnIndex">起始列</param>
/// <param name="lastColumnIndex">結束列</param>
/// <param name="cellStyle">如果為NULL，則為工作表默認的值</param>
public void SetObjectValue2(object value, int rowIndex1, int rowIndex2, int firstColumnIndex, int lastColumnIndex, ExcelCellStyle cellStyle)
{
IRow row = CreateRow(rowIndex1, firstColumnIndex, lastColumnIndex, cellStyle);
if (lastColumnIndex > firstColumnIndex || rowIndex2 > rowIndex1)
{
this._currentSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex1, rowIndex2, firstColumnIndex, lastColumnIndex));
}
SetCellValue(value, row, firstColumnIndex);
}
