public void ExportExcelFormat()
{
Biz_Bsc_Interface_Kpi_Query objQry = new Biz_Bsc_Interface_Kpi_Query(this.IKpiRefID, "");
DataSet rDsQry = objQry.GetInterfaceData(this.IKpiRefID, this.IYMD, out sRtnMsg, out bIsSuccess);
int iCntCol = 0;
if (rDsQry.Tables.Count > 0)
{
if (rDsQry.Tables[0].Rows.Count > 0)
{
iCntCol = rDsQry.Tables[0].Columns.Count;
}
else
{
iCntCol = 0;
}
}
ExcelEngine xlsEngine = new ExcelEngine();
IApplication xlsApp = xlsEngine.Excel;
IWorkbook xlsBook = xlsEngine.Excel.Workbooks.Create(1);
IWorksheet xlsSheet = xlsBook.Worksheets[0];
try
{
int iCol = WebCommon.GetExcelColumnCount();
for (int i = iCol; i > iCntCol; i--)
{
xlsSheet.ShowColumn(i, false);
}
Biz_Bsc_Interface_Column objCol = new Biz_Bsc_Interface_Column();
string sColNm = "";
for (int i = 0; i < iCntCol; i++)
{
sColNm = rDsQry.Tables[0].Columns[i].ColumnName;
objCol = new Biz_Bsc_Interface_Column(this.IDiCode, sColNm, gUserInfo.Emp_Ref_ID);
if (i == 0)
{
xlsSheet.Range[1, i + 1].Text = sColNm;
xlsSheet.Range[2, i + 1].Text = "발생일자";
xlsSheet.Range[2, i + 1].ColumnWidth = 10;
}
else
{
xlsSheet.Range[1, i + 1].Text = objCol.IColumn_Id;
xlsSheet.Range[2, i + 1].Text = objCol.IColumn_Alias;
xlsSheet.Range[2, i + 1].ColumnWidth = (objCol.IGrid_Width / 9);
xlsSheet.Columns[i].AutofitColumns();
xlsSheet.Columns[i].AutofitRows();
}
//switch (rDsQry.Tables[0].Columns[i].DataType.Name.ToLower())
//{
// case "decimal":
// string sChar = WebCommon.GetExcelColumnName(i);
// string sRange = sChar + "3:" + sChar + "10000";
// IDataValidation dvColumn = xlsSheet.Range[sRange].DataValidation;
// xlsSheet.Range[sRange].Text = "";
// xlsSheet.Range[sRange].NumberFormat = "#,###,###,###,###,###,###,###,###,###,##0.0######";
// xlsSheet.Range[sRange].AutofitColumns();
// dvColumn.AllowType = ExcelDataType.Decimal;
// dvColumn.CompareOperator = ExcelDataValidationComparisonOperator.Between;
// dvColumn.FirstFormula = System.Decimal.MinValue.ToString();
// dvColumn.SecondFormula = System.Decimal.MaxValue.ToString();
// dvColumn.ShowErrorBox = true;
// dvColumn.ErrorBoxText = "Enter Value between " + System.Decimal.MinValue.ToString() + " to " + System.Decimal.MaxValue.ToString();
// dvColumn.ErrorBoxTitle = "ERROR";
// dvColumn.PromptBoxText = "Data Validation using Condition for Numbers";
// dvColumn.ShowPromptBox = true;
// break;
// default:
// break;
//}
}
string sFileName = "InterfaceData" + "_" + this.IKpiRefID + "_" + this.IYMD + ".xls";
xlsSheet.ShowRow(1, false);
//Saving the workbook to disk.
xlsBook.SaveAs(sFileName, ExcelSaveType.SaveAsXLS, Response, ExcelDownloadType.PromptDialog);
}
catch (Exception e)
{
ltrScript.Text = JSHelper.GetAlertScript(e.Message);
}
finally
{
xlsEngine.ThrowNotSavedOnDestroy = false;
xlsEngine.Dispose();
}
//Entering text inside the Cells
//sheet.Range["A1:D1"].Text = "This is the Long Text";
//sheet.Range["E1"].Text = "This is the Long Text";
//sheet.Range["A2:A5"].Text = "This is the Long Text using Autofit Columns and Rows";
////AutoFit Applied to a Range
//sheet.Range["A1:D1"].AutofitColumns();
//sheet.Range["A2:A5"].AutofitRows();
////AutoFit applied to a Single Column
//sheet.AutofitColumn(5);
////AutoFit applied to a Single Row
//sheet.AutofitRow(1);
////Saving the workbook to disk.
//workbook.SaveAs("Sample.xls", ExcelSaveType.SaveAsXLS, Response, ExcelDownloadType.PromptDialog);
////No exception will be thrown if there are unsaved workbooks.
//excelEngine.ThrowNotSavedOnDestroy = false;
//excelEngine.Dispose();
}