public void SetUp()
{
var rangesInWorksheet = new IRange[3][];
for (int i = 0; i < rangesInWorksheet.Length; i++)
{
rangesInWorksheet[i] = A.CollectionOfFake<IRange>(4).ToArray();
for (int j = 0; j < rangesInWorksheet[i].Length; j++)
{
A.CallTo(() => rangesInWorksheet[i][j].Text).Returns(string.Format("{0} {1}", i+1, j+1));
}
}
var range = A.Fake<IRange>();
for (int i = 0; i < rangesInWorksheet.Length; i++)
{
for (int j = 0; j < rangesInWorksheet[i].Length; j++)
{
A.CallTo(() => range[i+1, j+1]).Returns(rangesInWorksheet[i][j]);
}
}
_worksheet = A.Fake<IWorksheet>();
A.CallTo(() => _worksheet.Name).Returns("WorksheetName");
A.CallTo(() => _worksheet.Cells).Returns(range);
_sut = ExcelReaderFactory.CreateExcelReader();
}
public RespondentAnswerTable(IWorksheet worksheet, bool hasHeader)
{
int question_count = (hasHeader) ? (worksheet.ColumnCount() - 1) : 1;
int first_row = (hasHeader) ? 1 : 0;
questionLabels = (hasHeader) ? (worksheet.Header()) : (new string[0]);
answers = new Dictionary<int, string>[question_count];
for (int i = 0; i < answers.Length; ++i)
answers[i] = new Dictionary<int, string>();
//Конец инициализации и переход к копированию таблицы
const int RespCol = 0;
for (int row = first_row; worksheet[row, RespCol] != null; ++row)
{
try
{
int resp_id = int.Parse(worksheet[row, RespCol]);
for (int question = 1; question <= question_count; ++question)
{
string resp_answer = worksheet[row, question];
if (!String.IsNullOrWhiteSpace(resp_answer))
answers[question - 1].Add(resp_id, resp_answer); // throws an exception when resp_id has duplicates
}
}
catch (Exception e)
{
throw new Exception(string.Format("Error parsing row {0} . Probably wrong file format.\nException is {1}", row.ToString(), e.ToString()));
}
}
}
public void InitNewProfile(Profile profile, bool hasDomains)
{
AddOrganisationDetails(profile.Id);
this.hasDomains = hasDomains;
ws = workbook.Worksheets.Add();
ws.Name = sheetNamer.GetSheetName(profile.Name);
rowIndex = 0;
AddHeader();
}
public Table GetExcelTable(IWorksheet worksheet, Selection selection)
{
Table table = new Table(worksheet.Name, selection.ColumnCount, selection.RowCount);
int rowOffset = selection.StartRow;
int columnOffset = selection.StartColumn;
for (int i = 0; i < selection.RowCount; i++)
{
for (int j = 0; j < selection.ColumnCount; j++)
{
var excelCell = worksheet.Cells[i + rowOffset, j + columnOffset];
table.Rows[i].Columns[j] = ExtractExcelCellProperty(excelCell);
}
}
return table;
}
/// <summary>
/// Dado un rango de lineas (inicio, fin) retorna una lista de cadenas de cada linea utilizando un separador
/// y con una pista de parada si se encuentra la cadena
/// </summary>
/// <param name="sheet">Hoja de cálculo</param>
/// <param name="start">Linea de inicio</param>
/// <param name="end">Línea fin</param>
/// <param name="separator">Cadena para separar entre celdas</param>
/// <param name="stophint">Texto de parada si se encuentra en la primera celda</param>
/// <returns></returns>
public static List<string> TextLinesFromRange(IWorksheet sheet, int start, int end, string separator = "", string stophint = "") {
end = Math.Min(sheet.NumRows, end);
var list = new List<string>();
for (var i = start; i <= end; i++) {
var cells = sheet.GetRow(i).Where(x => x != null).Select(x => x.ToString()).ToArray();
if (!string.IsNullOrEmpty(stophint)) {
var first = cells.FirstOrDefault() ?? "";
if (first.StartsWith(stophint))
break;
}
var line = string.Join(separator, cells).Trim();
list.Add(line);
}
return list;
}
internal static void ExportArea(IWorksheet worksheet, DataView dataView)
{
// String Array having required columns in Area Entry Spreadsheet.
string[] AreaColumns = new string[5];
AreaColumns[0] = Area.AreaID;
AreaColumns[1] = Area.AreaName;
AreaColumns[2] = Area.AreaLevel;
AreaColumns[3] = Area.AreaGId;
AreaColumns[4] = Constants.ParentAreaIDColumn;
IRange Range = worksheet.Cells[Constants.CellAddress.AreaWorksheetStartAddress];
Range.CopyFromDataTable(dataView.ToTable(false, AreaColumns), SpreadsheetGear.Data.SetDataFlags.NoColumnHeaders);
// Auto-Resize column width.
worksheet.Cells.Columns.AutoFit();
// TODO Language handling of headings.
}
protected List<Tuple<int, string>> BuscarTablas(IWorksheet sheet, bool buscarNombre = false)
{
var total = sheet.NumRows;
var lista = new List<Tuple<int, string>>();
for (int i = 2; i <= total; i++) {
var txt = sheet.GetString("A", i) ?? "";
if (txt != "Canton")
continue;
var nombre = sheet.Name;
if (buscarNombre) {
nombre = sheet.GetString("A", i - 1);
if (string.IsNullOrEmpty(nombre))
continue;
}
var t = Tuple.Create(i + 1, nombre);
lista.Add(t);
}
return lista;
}
public void GenerateXls(ListBox.SelectedObjectCollection selctedcountry)
{
#region Initialize Workbook
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Create the workbook with default sheet
IWorkbook workbook = application.Workbooks.Create();
//Get the 1st sheet from the workbook
IWorksheet sheet = workbook.Worksheets[0];
string path = Path.GetFullPath(@"..\..\..\..\..\..\..\Common\Data\Northwind.mdb");
//connection string for DataSource
string ConnectionString = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=" + path;
//query for the datasource
string query; string countries = string.Empty;
if (selctedcountry.Count > 0)
{
countries = "'" + selctedcountry[0].ToString() + "'";
for (int i = 0; i < selctedcountry.Count; i++)
{
countries = countries + "," + "'" + selctedcountry[i] + "'";
}
query = "select * from Customers where country in(" + countries + ");";
}
else
{
query = "select * from Customers";
}
//Add the connection to workbook
IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionString, query, ExcelCommandType.Sql);
//Add the QueryTable to sheet object
sheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, sheet.Range["A1"]);
#endregion
#region Refresh the Connection
//Refresh the Connection for include the data
if (refresh.Checked)
{
try
{
sheet.ListObjects[0].Refresh();
sheet.UsedRange.AutofitColumns();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
#endregion
#region Save the Workbook
//Set the Workbook version as excel 2007
if (this.excel2007RadioBtn.Checked)
{
workbook.Version = ExcelVersion.Excel2007;
fileName = "ExternalConnection.xlsx";
}
//Set the Workbook version as Excel 2010
else if (this.excel2010RadioBtn.Checked)
{
workbook.Version = ExcelVersion.Excel2010;
fileName = "ExternalConnection.xlsx";
}
//Set the Workbook version as Excel 2010
else if (this.excel2013RadioBtn.Checked)
{
workbook.Version = ExcelVersion.Excel2013;
fileName = "ExternalConnection.xlsx";
}
//Save the workbook to disk.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
}
private static void WriteLogsForInstallation(ref IWorksheet ExcelSheet, string FilePath, string Date, string Message, string Status, string ExceptionMsg)
{
try
{
int RowIndex = Convert.ToInt32(ExcelSheet.UsedRange.RowCount);
ExcelSheet.Cells[RowIndex, 0].Value = Message;
ExcelSheet.Cells[RowIndex, 1].Value = Date;
ExcelSheet.Cells[RowIndex, 2].Value = Status;
ExcelSheet.Cells[RowIndex, 3].Value = ExceptionMsg;
//ExcelSheet.Cells[RowIndex, 4].Value = UserEmailId;
ExcelSheet.Cells[RowIndex, 0].Borders.Color = System.Drawing.Color.Black;
ExcelSheet.Cells[RowIndex, 1].Borders.Color = System.Drawing.Color.Black;
ExcelSheet.Cells[RowIndex, 2].Borders.Color = System.Drawing.Color.Black;
ExcelSheet.Cells[RowIndex, 3].Borders.Color = System.Drawing.Color.Black;
// ExcelSheet.Cells[RowIndex, 4].Borders.Color = System.Drawing.Color.Black;
ExcelSheet.Name = Constants.PatchConstaints.PatchInstLogFileName;
ExcelSheet.SaveAs(FilePath, SpreadsheetGear.FileFormat.Excel8);
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
}
}
private void btnExport_Click(object sender, EventArgs e)
{
#region Initialize Workbook
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the Default version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
fileName = "CollectionObject.xls";
}
//Set the Default version as Excel 2007
else if (this.rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "CollectionObject.xlsx";
}
else if (this.rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
fileName = "CollectionObject.xlsx";
}
else if (this.rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
fileName = "CollectionObject.xlsx";
}
//Create a new spreadsheet.
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
sheet.ImportData((List <Brand>) this.dataGridView1.DataSource, 4, 1, true);
#region Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.FontName = "Calibri";
pageHeader.Font.Size = 16;
pageHeader.Font.Bold = true;
pageHeader.Color = Color.FromArgb(0, 146, 208, 80);
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Bold = true;
tableHeader.Font.FontName = "Calibri";
tableHeader.Color = Color.FromArgb(0, 146, 208, 80);
#endregion
#region Apply Styles
// Apply style for header
sheet["A1:C2"].Merge();
sheet["A1"].Text = "Automobile Brands in the US";
sheet["A1"].CellStyle = pageHeader;
sheet["A4:C4"].CellStyle = tableHeader;
sheet["A1:C1"].CellStyle.Font.Bold = true;
sheet.UsedRange.AutofitColumns();
#endregion
#endregion
#region Save the Workbook
//Saving the workbook to disk. This spreadsheet is the result of opening and modifying
//an existing spreadsheet and then saving the result to a new workbook.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
}
// Exit
//this.Close();
#endregion
}
/// <summary>
/// Create the Excel document with embedded chart
/// </summary>
/// <returns>Return the created excel document as stream</returns>
public MemoryStream EmbeddedChartXlsIO(string version)
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Step 2 : Instantiate the excel application object
IApplication application = excelEngine.Excel;
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//Open workbook with Data
FileStream inputStream = new FileStream(ResolveApplicationPath("embedded-chart.xlsx"), FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
//Set the default version
if (version == "XLS")
{
workbook.Version = ExcelVersion.Excel97to2003;
}
else
{
workbook.Version = ExcelVersion.Excel2016;
}
//The first worksheet object in the worksheets collection is accessed
IWorksheet sheet = workbook.Worksheets[0];
sheet.Name = "Sample";
// Adding a New chart to the Existing Worksheet
IChartShape chart = workbook.Worksheets[0].Charts.Add();
chart.DataRange = sheet.Range["A3:C15"];
chart.ChartTitle = "Crescent City, CA";
chart.IsSeriesInRows = false;
chart.PrimaryValueAxis.Title = "Precipitation,in.";
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
chart.PrimaryValueAxis.MaximumValue = 14.0;
chart.PrimaryValueAxis.NumberFormat = "0.0";
chart.PrimaryCategoryAxis.Title = "Month";
IChartSerie serieOne = chart.Series[0];
//set the Chart Type
chart.ChartType = ExcelChartType.Column_Clustered_3D;
//set the Backwall fill option
chart.BackWall.Fill.FillType = ExcelFillType.Gradient;
//set the Texture Type
chart.BackWall.Fill.GradientColorType = ExcelGradientColor.TwoColor;
chart.BackWall.Fill.GradientStyle = ExcelGradientStyle.Diagonl_Down;
chart.BackWall.Fill.ForeColor = Color.WhiteSmoke;
chart.BackWall.Fill.BackColor = Color.LightBlue;
//set the Border Linecolor
chart.BackWall.Border.LineColor = Color.Wheat;
//set the Picture Type
chart.BackWall.PictureUnit = ExcelChartPictureType.stretch;
//set the Backwall thickness
chart.BackWall.Thickness = 10;
//set the sidewall fill option
chart.SideWall.Fill.FillType = ExcelFillType.SolidColor;
//set the sidewall foreground and backcolor
chart.SideWall.Fill.BackColor = Color.White;
chart.SideWall.Fill.ForeColor = Color.White;
//set the side wall Border color
chart.SideWall.Border.LineColor = Color.Beige;
//set floor fill option
chart.Floor.Fill.FillType = ExcelFillType.Pattern;
//set the floor pattern Type
chart.Floor.Fill.Pattern = ExcelGradientPattern.Pat_Divot;
//Set the floor fore and Back ground color
chart.Floor.Fill.ForeColor = Color.Blue;
chart.Floor.Fill.BackColor = Color.White;
//set the floor thickness
chart.Floor.Thickness = 3;
IChartSerie serieTwo = chart.Series[1];
//Show value as data labels
serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
serieTwo.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
//Embedded Chart Position
chart.TopRow = 2;
chart.BottomRow = 30;
chart.LeftColumn = 5;
chart.RightColumn = 18;
serieTwo.Name = "Temperature,deg.F";
// Legend setting
chart.Legend.Position = ExcelLegendPosition.Right;
chart.Legend.IsVerticalLegend = false;
//Save the document as a stream and retrun the stream
using (MemoryStream stream = new MemoryStream())
{
//Save the created Excel document to MemoryStream
workbook.SaveAs(stream);
return(stream);
}
}
}
static List<Str_Parameters?> CreateExcelDocument_GetParameters(IWorksheet Ws_Parameters, List<ER_Common.Str_Parameter> List_ErcSp)
{
List<Str_Parameters?> List_Sp = new List<Str_Parameters?>();
Int32[] LineInfo = CreateExcelDocument_ReadLineInfo(Ws_Parameters, CnsExcelKeyword_Parameters, CnsExcelKeyword_Parameters_End);
Int32 Ct_Start = LineInfo[0];
Int32 Ct_End = LineInfo[1];
for (Int32 Ct = Ct_Start; Ct <= Ct_End; Ct++)
{
String ExcelText = Ws_Parameters.Range["A" + Ct.ToString()].Characters.Text;
String Parameter_Name;
String Parameter_Type;
if ((Strings.InStr(ExcelText, "@") > 0))
{
try
{
Parameter_Name = Strings.Mid(ExcelText, Strings.Len("@") + 1, (Strings.InStr(ExcelText, " ") - Strings.Len("@")) - 1);
Parameter_Type = Strings.Mid(ExcelText, Strings.InStr(ExcelText, " ") + 1);
Str_Parameters Sp = new Str_Parameters();
Sp.Name = Parameter_Name;
Sp.Type = Parameter_Type;
Sp.Value = (from O in List_ErcSp where O.Name == Sp.Name select O.Value).FirstOrDefault().ToString();
List_Sp.Add(Sp);
}
catch { }
}
}
return List_Sp;
}
static Int32[] CreateExcelDocument_ReadLineInfo(IWorksheet Ws_Source, String Line_Start, String Line_End)
{
Int32 Ct_Start = 0;
Int32 Ct_End = 0;
Int32 Ct = 0;
for (Ct = 1; Ct <= CnsExcelMaxHeight; Ct++)
{
String ExcelLine = Ws_Source.Range["A" + Ct.ToString()].Characters.Text;
if (ExcelLine == Line_Start)
{ Ct_Start = Ct; }
else if (ExcelLine == Line_End)
{
Ct_End = Ct;
break;
}
}
Int32[] Rv = new Int32[2];
Rv[0] = Ct_Start;
Rv[1] = Ct_End;
return Rv;
}
protected void Page_Load(object sender, EventArgs e)
{
//CtlLib.SetUser("hr");
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string p_user, p_tco_org_pk, p_wg, p_from, p_to;
string p_thr_ws_pk, p_search, p_input, p_opt, p_opt_text, p_m1, p_m2, p_nation;
p_tco_org_pk = Request["p_tco_org_pk"].ToString();
p_wg = Request["p_wg"].ToString();
p_from = Request["p_from"].ToString();
p_to = Request["p_to"].ToString();
p_thr_ws_pk = Request["p_thr_ws_pk"].ToString();
p_input = Request["p_input"].ToString();
p_user = Request["p_user"].ToString();
p_opt = Request["p_opt"].ToString();
p_opt_text = Request["p_opt_text"].ToString();
p_m1 = Request["p_m1"].ToString();
p_m2 = Request["p_m2"].ToString();
p_nation = Request["p_nation"].ToString();
string SQL;
string TemplateFile = "rpt_hrti00800_0.xls";
string TempFile = "../../../../system/temp/rpt_hrti00800_0_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
if (p_opt == "5")
{
SQL = "SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') " +
" ,F.org_NM " +
" ,B.workgroup_NM " +
" ,A.EMP_ID " +
" ,A.FULL_NAME " +
" ,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD'),'DD/MM/YYYY') " +
" ,C.REMARK " +
" ,D.TIME_IN,D.TIME_OUT " +
" ,MAX(DECODE(D.HOL_TYPE, NULL, D.WT, '')) AS WT " +
" ,MAX(D.OT) AS OT " +
" ,MAX(D.NT) AS NT " +
" ,MAX(D.ONT) AS NT2 " +
" ,MAX(DECODE(D.HOL_TYPE, 'SUN', D.WT, '')) AS ST3 " +
" ,MAX(DECODE(D.HOL_TYPE,'HOL', D.WT, '')) AS HT " +
" ,(case when MAX(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK)) >=0 then NULL " +
" else " +
" ABS(MAX(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK))) " +
" END ) " +
" AS LATE_IN " +
",(case when MAX(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK)) >=0 then null " +
" else " +
" abs(MAX(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK))) " +
" end) AS EARLY_OUT " +
" ,MAX(D.MOD_BYHAND_YN ) as by_hand " +
" ,MAX(D.MOD_BY) as mod_by " +
" FROM THR_EMPLOYEE A,THR_work_group B, THR_WORK_SHIFT C,THR_WORK_DATE D,tco_org F " +
" WHERE A.DEL_IF=0 AND B.DEL_IF=0 AND C.DEL_IF=0 AND D.DEL_IF=0 AND F.DEL_IF=0 " +
" AND A.THR_wg_PK=B.PK AND A.PK=D.THR_EMP_PK AND C.PK=D.THR_WS_PK AND A.TCO_org_PK=F.PK " +
" AND D.WORK_DT BETWEEN '" + p_from + "' AND '" + p_to + "' " +
" AND DECODE('" + p_wg + "' ,'ALL', '" + p_wg + "',B.PK) = '" + p_wg + "'" +
" AND DECODE('" + p_nation + "' ,'ALL', '" + p_nation + "',a.nation) = '" + p_nation + "'" +
" AND DECODE('" + p_thr_ws_pk + "','ALL', '" + p_thr_ws_pk + "',C.PK)= '" + p_thr_ws_pk + "' ";
SQL = SQL + " AND ( A.TCO_ORG_PK IN ( ";
SQL = SQL + " SELECT G.PK ";
SQL = SQL + " FROM TCO_ORG G ";
SQL = SQL + " WHERE G.DEL_IF = 0 ";
SQL = SQL + " START WITH G.PK = ";
SQL = SQL + " DECODE ('" + p_tco_org_pk + "', ";
SQL = SQL + " 'ALL', 0, ";
SQL = SQL + " '" + p_tco_org_pk + "' ";
SQL = SQL + " ) ";
SQL = SQL + " CONNECT BY PRIOR G.PK = G.P_PK) ";
SQL = SQL + " OR '" + p_tco_org_pk + "' = 'ALL') " +
" AND ('" + p_input + "' IS NULL " +
" OR (UPPER(A.EMP_ID) LIKE '%' || UPPER('" + p_input + "')|| '%') " +
" OR (UPPER(A.OLD_ID ) LIKE '%' || UPPER('" + p_input + "')|| '%') " +
" OR (UPPER(A.FULL_NAME) LIKE '%' || UPPER('" + p_input + "')|| '%')) " +
" GROUP BY F.org_NM,B.workGROUP_NM,D.WORK_DT,A.EMP_ID,A.FULL_NAME,C.REMARK,D.TIME_IN,D.TIME_OUT,D.WT " +
" ORDER BY F.org_NM,B.workGROUP_NM,D.WORK_DT,A.EMP_ID ";
}
else if (p_opt == "1")
{
SQL = "SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') " +
" ,F.org_NM " +
" ,B.workgroup_NM " +
" ,A.EMP_ID " +
" ,A.FULL_NAME " +
" ,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD'),'DD/MM/YYYY') " +
" ,C.REMARK " +
" ,D.TIME_IN,D.TIME_OUT " +
" ,MAX(DECODE(D.HOL_TYPE, NULL, D.WT, '')) AS WT " +
" ,MAX(D.OT) AS OT " +
" ,MAX(D.NT) AS NT " +
" ,MAX(D.ONT) AS NT2 " +
" ,MAX(DECODE(D.HOL_TYPE, 'SUN', D.WT, '')) AS ST3 " +
" ,MAX(DECODE(D.HOL_TYPE,'HOL', D.WT, '')) AS HT " +
" ,(case when MAX(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK)) >=0 then NULL " +
" else " +
" ABS(MAX(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK))) " +
" END ) " +
" AS LATE_IN " +
",(case when MAX(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK)) >=0 then null " +
" else " +
" abs(MAX(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK))) " +
" end) AS EARLY_OUT " +
" ,MAX(D.MOD_BYHAND_YN ) as by_hand " +
" ,MAX(D.MOD_BY) as mod_by " +
" FROM THR_EMPLOYEE A,THR_work_group B, THR_WORK_SHIFT C,THR_WORK_DATE D,tco_org F " +
" WHERE A.DEL_IF=0 AND B.DEL_IF=0 AND C.DEL_IF=0 AND D.DEL_IF=0 AND F.DEL_IF=0 " +
" AND A.THR_wg_PK=B.PK AND A.PK=D.THR_EMP_PK AND C.PK=D.THR_WS_PK AND A.TCO_org_PK=F.PK " +
" AND D.WORK_DT BETWEEN '" + p_from + "' AND '" + p_to + "' " +
" AND DECODE('" + p_wg + "' ,'ALL', '" + p_wg + "',B.PK) = '" + p_wg + "'" +
" AND DECODE('" + p_nation + "' ,'ALL', '" + p_nation + "',a.nation) = '" + p_nation + "'" +
" AND DECODE('" + p_thr_ws_pk + "','ALL', '" + p_thr_ws_pk + "',C.PK)= '" + p_thr_ws_pk + "' ";
SQL = SQL + " AND ( A.TCO_ORG_PK IN ( ";
SQL = SQL + " SELECT G.PK ";
SQL = SQL + " FROM TCO_ORG G ";
SQL = SQL + " WHERE G.DEL_IF = 0 ";
SQL = SQL + " START WITH G.PK = ";
SQL = SQL + " DECODE ('" + p_tco_org_pk + "', ";
SQL = SQL + " 'ALL', 0, ";
SQL = SQL + " '" + p_tco_org_pk + "' ";
SQL = SQL + " ) ";
SQL = SQL + " CONNECT BY PRIOR G.PK = G.P_PK) ";
SQL = SQL + " OR '" + p_tco_org_pk + "' = 'ALL') " +
" AND ('" + p_input + "' IS NULL " +
" OR (UPPER(A.EMP_ID) LIKE '%' || UPPER('" + p_input + "')|| '%') " +
" OR (UPPER(A.OLD_ID ) LIKE '%' || UPPER('" + p_input + "')|| '%') " +
" OR (UPPER(A.FULL_NAME) LIKE '%' || UPPER('" + p_input + "')|| '%')) " +
" AND (D.TIME_IN IS NULL OR D.TIME_OUT IS NULL) " +
" GROUP BY F.org_NM,B.workGROUP_NM,D.WORK_DT,A.EMP_ID,A.FULL_NAME,C.REMARK,D.TIME_IN,D.TIME_OUT,D.WT " +
" ORDER BY F.org_NM,B.workGROUP_NM,D.WORK_DT,A.EMP_ID ";
}
else if (p_opt == "2")
{
SQL = "SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') " +
" ,F.org_NM " +
" ,B.workgroup_NM " +
" ,A.EMP_ID " +
" ,A.FULL_NAME " +
" ,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD'),'DD/MM/YYYY') " +
" ,C.REMARK " +
" ,D.TIME_IN,D.TIME_OUT " +
" ,MAX(DECODE(D.HOL_TYPE, NULL, D.WT, '')) AS WT " +
" ,MAX(D.OT) AS OT " +
" ,MAX(D.NT) AS NT " +
" ,MAX(D.ONT) AS NT2 " +
" ,MAX(DECODE(D.HOL_TYPE, 'SUN', D.WT, '')) AS ST3 " +
" ,MAX(DECODE(D.HOL_TYPE,'HOL', D.WT, '')) AS HT " +
" ,(case when MAX(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK)) >=0 then NULL " +
" else " +
" ABS(MAX(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK))) " +
" END ) " +
" AS LATE_IN " +
",(case when MAX(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK)) >=0 then null " +
" else " +
" abs(MAX(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK))) " +
" end) AS EARLY_OUT " +
" ,MAX(D.MOD_BYHAND_YN ) as by_hand " +
" ,MAX(D.MOD_BY) as mod_by " +
" FROM THR_EMPLOYEE A,THR_work_group B, THR_WORK_SHIFT C,THR_WORK_DATE D,tco_org F " +
" WHERE A.DEL_IF=0 AND B.DEL_IF=0 AND C.DEL_IF=0 AND D.DEL_IF=0 AND F.DEL_IF=0 " +
" AND A.THR_wg_PK=B.PK AND A.PK=D.THR_EMP_PK AND C.PK=D.THR_WS_PK AND A.TCO_org_PK=F.PK " +
" AND D.WORK_DT BETWEEN '" + p_from + "' AND '" + p_to + "' " +
" AND DECODE('" + p_wg + "' ,'ALL', '" + p_wg + "',B.PK) = '" + p_wg + "'" +
" AND DECODE('" + p_nation + "' ,'ALL', '" + p_nation + "',a.nation) = '" + p_nation + "'" +
" AND DECODE('" + p_thr_ws_pk + "','ALL', '" + p_thr_ws_pk + "',C.PK)= '" + p_thr_ws_pk + "' ";
SQL = SQL + " AND ( A.TCO_ORG_PK IN ( ";
SQL = SQL + " SELECT G.PK ";
SQL = SQL + " FROM TCO_ORG G ";
SQL = SQL + " WHERE G.DEL_IF = 0 ";
SQL = SQL + " START WITH G.PK = ";
SQL = SQL + " DECODE ('" + p_tco_org_pk + "', ";
SQL = SQL + " 'ALL', 0, ";
SQL = SQL + " '" + p_tco_org_pk + "' ";
SQL = SQL + " ) ";
SQL = SQL + " CONNECT BY PRIOR G.PK = G.P_PK) ";
SQL = SQL + " OR '" + p_tco_org_pk + "' = 'ALL') " +
" AND ('" + p_input + "' IS NULL " +
" OR (UPPER(A.EMP_ID) LIKE '%' || UPPER('" + p_input + "')|| '%') " +
" OR (UPPER(A.OLD_ID ) LIKE '%' || UPPER('" + p_input + "')|| '%') " +
" OR (UPPER(A.FULL_NAME) LIKE '%' || UPPER('" + p_input + "')|| '%')) " +
" AND (HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK) < 0 ) " +
" AND (Abs(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK)) >= '" + p_m1 + "' OR '" + p_m1 + "' is null) " +
" AND (Abs(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK)) <= '" + p_m2 + "' OR '" + p_m2 + "' is null) " +
" GROUP BY F.ORG_NM,B.WORKGROUP_NM,D.WORK_DT,A.EMP_ID,A.FULL_NAME,C.REMARK,D.TIME_IN,D.TIME_OUT,D.WT " +
" ORDER BY F.ORG_NM,B.WORKGROUP_NM,D.WORK_DT,A.EMP_ID ";
}
else if (p_opt == "3")
{
SQL = "SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') " +
" ,F.org_NM " +
" ,B.workgroup_NM " +
" ,A.EMP_ID " +
" ,A.FULL_NAME " +
" ,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD'),'DD/MM/YYYY') " +
" ,C.REMARK " +
" ,D.TIME_IN,D.TIME_OUT " +
" ,MAX(DECODE(D.HOL_TYPE, NULL, D.WT, '')) AS WT " +
" ,MAX(D.OT) AS OT " +
" ,MAX(D.NT) AS NT " +
" ,MAX(D.ONT) AS NT2 " +
" ,MAX(DECODE(D.HOL_TYPE, 'SUN', D.WT, '')) AS ST3 " +
" ,MAX(DECODE(D.HOL_TYPE,'HOL', D.WT, '')) AS HT " +
" ,(case when MAX(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK)) >=0 then NULL " +
" else " +
" ABS(MAX(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK))) " +
" END ) " +
" AS LATE_IN " +
",(case when MAX(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK)) >=0 then null " +
" else " +
" abs(MAX(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK))) " +
" end) AS EARLY_OUT " +
" ,MAX(D.MOD_BYHAND_YN ) as by_hand " +
" ,MAX(D.MOD_BY) as mod_by " +
" FROM THR_EMPLOYEE A,THR_work_group B, THR_WORK_SHIFT C,THR_WORK_DATE D,tco_org F " +
" WHERE A.DEL_IF=0 AND B.DEL_IF=0 AND C.DEL_IF=0 AND D.DEL_IF=0 AND F.DEL_IF=0 " +
" AND A.THR_wg_PK=B.PK AND A.PK=D.THR_EMP_PK AND C.PK=D.THR_WS_PK AND A.TCO_org_PK=F.PK " +
" AND D.WORK_DT BETWEEN '" + p_from + "' AND '" + p_to + "' " +
" AND DECODE('" + p_wg + "' ,'ALL', '" + p_wg + "',B.PK) = '" + p_wg + "'" +
" AND DECODE('" + p_nation + "' ,'ALL', '" + p_nation + "',a.nation) = '" + p_nation + "'" +
" AND DECODE('" + p_thr_ws_pk + "','ALL', '" + p_thr_ws_pk + "',C.PK)= '" + p_thr_ws_pk + "' ";
SQL = SQL + " AND ( A.TCO_ORG_PK IN ( ";
SQL = SQL + " SELECT G.PK ";
SQL = SQL + " FROM TCO_ORG G ";
SQL = SQL + " WHERE G.DEL_IF = 0 ";
SQL = SQL + " START WITH G.PK = ";
SQL = SQL + " DECODE ('" + p_tco_org_pk + "', ";
SQL = SQL + " 'ALL', 0, ";
SQL = SQL + " '" + p_tco_org_pk + "' ";
SQL = SQL + " ) ";
SQL = SQL + " CONNECT BY PRIOR G.PK = G.P_PK) ";
SQL = SQL + " OR '" + p_tco_org_pk + "' = 'ALL') " +
" AND ('" + p_input + "' IS NULL " +
" OR (UPPER(A.EMP_ID) LIKE '%' || UPPER('" + p_input + "')|| '%') " +
" OR (UPPER(A.OLD_ID ) LIKE '%' || UPPER('" + p_input + "')|| '%') " +
" OR (UPPER(A.FULL_NAME) LIKE '%' || UPPER('" + p_input + "')|| '%')) " +
" AND (HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK) < 0 ) " +
" AND (Abs(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK)) >= '" + p_m1 + "' OR '" + p_m1 + "' is null) " +
" AND (Abs(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK)) <= '" + p_m2 + "' OR '" + p_m2 + "' is null) " +
" GROUP BY F.ORG_NM,B.WORKGROUP_NM,D.WORK_DT,A.EMP_ID,A.FULL_NAME,C.REMARK,D.TIME_IN,D.TIME_OUT,D.WT " +
" ORDER BY F.ORG_NM,B.WORKGROUP_NM,D.WORK_DT,A.EMP_ID ";
}
else if (p_opt == "4")
{
SQL = "SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') " +
" ,F.org_NM " +
" ,B.workgroup_NM " +
" ,A.EMP_ID " +
" ,A.FULL_NAME " +
" ,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD'),'DD/MM/YYYY') " +
" ,C.REMARK " +
" ,D.TIME_IN,D.TIME_OUT " +
" ,MAX(DECODE(D.HOL_TYPE, NULL, D.WT, '')) AS WT " +
" ,MAX(D.OT) AS OT " +
" ,MAX(D.NT) AS NT " +
" ,MAX(D.ONT) AS NT2 " +
" ,MAX(DECODE(D.HOL_TYPE, 'SUN', D.WT, '')) AS ST3 " +
" ,MAX(DECODE(D.HOL_TYPE,'HOL', D.WT, '')) AS HT " +
" ,(case when MAX(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK)) >=0 then NULL " +
" else " +
" ABS(MAX(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK))) " +
" END ) " +
" AS LATE_IN " +
",(case when MAX(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK)) >=0 then null " +
" else " +
" abs(MAX(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK))) " +
" end) AS EARLY_OUT " +
" ,MAX(D.MOD_BYHAND_YN ) as by_hand " +
" ,MAX(D.MOD_BY) as mod_by " +
" FROM THR_EMPLOYEE A,THR_work_group B, THR_WORK_SHIFT C,THR_WORK_DATE D,tco_org F " +
" WHERE A.DEL_IF=0 AND B.DEL_IF=0 AND C.DEL_IF=0 AND D.DEL_IF=0 AND F.DEL_IF=0 " +
" AND A.THR_wg_PK=B.PK AND A.PK=D.THR_EMP_PK AND C.PK=D.THR_WS_PK AND A.TCO_org_PK=F.PK " +
" AND D.WORK_DT BETWEEN '" + p_from + "' AND '" + p_to + "' " +
" AND DECODE('" + p_wg + "' ,'ALL', '" + p_wg + "',B.PK) = '" + p_wg + "'" +
" AND DECODE('" + p_nation + "' ,'ALL', '" + p_nation + "',a.nation) = '" + p_nation + "'" +
" AND DECODE('" + p_thr_ws_pk + "','ALL', '" + p_thr_ws_pk + "',C.PK)= '" + p_thr_ws_pk + "' ";
SQL = SQL + " AND ( A.TCO_ORG_PK IN ( ";
SQL = SQL + " SELECT G.PK ";
SQL = SQL + " FROM TCO_ORG G ";
SQL = SQL + " WHERE G.DEL_IF = 0 ";
SQL = SQL + " START WITH G.PK = ";
SQL = SQL + " DECODE ('" + p_tco_org_pk + "', ";
SQL = SQL + " 'ALL', 0, ";
SQL = SQL + " '" + p_tco_org_pk + "' ";
SQL = SQL + " ) ";
SQL = SQL + " CONNECT BY PRIOR G.PK = G.P_PK) ";
SQL = SQL + " OR '" + p_tco_org_pk + "' = 'ALL') " +
" AND ('" + p_input + "' IS NULL " +
" OR (UPPER(A.EMP_ID) LIKE '%' || UPPER('" + p_input + "')|| '%') " +
" OR (UPPER(A.OLD_ID ) LIKE '%' || UPPER('" + p_input + "')|| '%') " +
" OR (UPPER(A.FULL_NAME) LIKE '%' || UPPER('" + p_input + "')|| '%')) " +
" AND (NVL(D.MOD_BYHAND_YN,'N')='Y' ) "+
" GROUP BY F.ORG_NM,B.WORKGROUP_NM,D.WORK_DT,A.EMP_ID,A.FULL_NAME,C.REMARK,D.TIME_IN,D.TIME_OUT,D.WT " +
" ORDER BY F.ORG_NM,B.WORKGROUP_NM,D.WORK_DT,A.EMP_ID ";
}
/* OR NVL(E.MOD_BYHAND_YN,'N')='Y' " +
* " OR NVL(D.MOD_AUTO_YN,'N')='Y' OR NVL(E.MOD_AUTO_YN,'N')='Y'*/
else if (p_opt == "6")
{
SQL = "SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') " +
" ,F.org_NM " +
" ,B.workgroup_NM " +
" ,A.EMP_ID " +
" ,A.FULL_NAME " +
" ,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD'),'DD/MM/YYYY') " +
" ,C.REMARK " +
" ,D.TIME_IN,D.TIME_OUT " +
" ,MAX(DECODE(D.HOL_TYPE, NULL, D.WT, '')) AS WT " +
" ,MAX(D.OT) AS OT " +
" ,MAX(D.NT) AS NT " +
" ,MAX(D.ONT) AS NT2 " +
" ,MAX(DECODE(D.HOL_TYPE, 'SUN', D.WT, '')) AS ST3 " +
" ,MAX(DECODE(D.HOL_TYPE,'HOL', D.WT, '')) AS HT " +
" ,(case when MAX(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK)) >=0 then NULL " +
" else " +
" ABS(MAX(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK))) " +
" END ) " +
" AS LATE_IN " +
",(case when MAX(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK)) >=0 then null " +
" else " +
" abs(MAX(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK))) " +
" end) AS EARLY_OUT " +
" ,MAX(D.MOD_BYHAND_YN ) as by_hand " +
" ,MAX(D.MOD_BY) as mod_by " +
" FROM THR_EMPLOYEE A,THR_work_group B, THR_WORK_SHIFT C,THR_WORK_DATE D,tco_org F " +
" WHERE A.DEL_IF=0 AND B.DEL_IF=0 AND C.DEL_IF=0 AND D.DEL_IF=0 AND F.DEL_IF=0 " +
" AND A.THR_wg_PK=B.PK AND A.PK=D.THR_EMP_PK AND C.PK=D.THR_WS_PK AND A.TCO_org_PK=F.PK " +
" AND D.WORK_DT BETWEEN '" + p_from + "' AND '" + p_to + "' " +
" AND DECODE('" + p_wg + "' ,'ALL', '" + p_wg + "',B.PK) = '" + p_wg + "'" +
" AND DECODE('" + p_nation + "' ,'ALL', '" + p_nation + "',a.nation) = '" + p_nation + "'" +
" AND DECODE('" + p_thr_ws_pk + "','ALL', '" + p_thr_ws_pk + "',C.PK)= '" + p_thr_ws_pk + "' ";
SQL = SQL + " AND ( A.TCO_ORG_PK IN ( ";
SQL = SQL + " SELECT G.PK ";
SQL = SQL + " FROM TCO_ORG G ";
SQL = SQL + " WHERE G.DEL_IF = 0 ";
SQL = SQL + " START WITH G.PK = ";
SQL = SQL + " DECODE ('" + p_tco_org_pk + "', ";
SQL = SQL + " 'ALL', 0, ";
SQL = SQL + " '" + p_tco_org_pk + "' ";
SQL = SQL + " ) ";
SQL = SQL + " CONNECT BY PRIOR G.PK = G.P_PK) ";
SQL = SQL + " OR '" + p_tco_org_pk + "' = 'ALL') " +
" AND ('" + p_input + "' IS NULL " +
" OR (UPPER(A.EMP_ID) LIKE '%' || UPPER('" + p_input + "')|| '%') " +
" OR (UPPER(A.OLD_ID ) LIKE '%' || UPPER('" + p_input + "')|| '%') " +
" OR (UPPER(A.FULL_NAME) LIKE '%' || UPPER('" + p_input + "')|| '%')) " +
" AND (HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK) < 0 " +
"or HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK) < 0 ) " +
"AND ( ( " +
"(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK) < 0 and (abs(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK)) >= '" + p_m1 + "' OR '" + p_m1 + "' is null) " +
"AND (abs(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK)) <= '" + p_m2 + "' OR '" + p_m2 + "' is null)) " +
") or ( " +
"HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK) < 0 and (abs(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK)) >= '" + p_m1 + "' OR '" + p_m1 + "' is null) " +
"AND (abs(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK)) <= '" + p_m2 + "' OR '" + p_m2 + "' is null) " +
")) " +
" GROUP BY F.ORG_NM,B.WORKGROUP_NM,D.WORK_DT,A.EMP_ID,A.FULL_NAME,C.REMARK,D.TIME_IN,D.TIME_OUT,D.WT " +
" ORDER BY F.ORG_NM,B.WORKGROUP_NM,D.WORK_DT,A.EMP_ID ";
}
else if (p_opt == "7")
{
SQL = "SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') " +
" ,F.org_NM " +
" ,B.workgroup_NM " +
" ,A.EMP_ID " +
" ,A.FULL_NAME " +
" ,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD'),'DD/MM/YYYY') " +
" ,C.REMARK " +
" ,D.TIME_IN,D.TIME_OUT " +
" ,MAX(DECODE(D.HOL_TYPE, NULL, D.WT, '')) AS WT " +
" ,MAX(D.OT) AS OT " +
" ,MAX(D.NT) AS NT " +
" ,MAX(D.ONT) AS NT2 " +
" ,MAX(DECODE(D.HOL_TYPE, 'SUN', D.WT, '')) AS ST3 " +
" ,MAX(DECODE(D.HOL_TYPE,'HOL', D.WT, '')) AS HT " +
" ,(case when MAX(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK)) >=0 then NULL " +
" else " +
" ABS(MAX(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK))) " +
" END ) " +
" AS LATE_IN " +
",(case when MAX(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK)) >=0 then null " +
" else " +
" abs(MAX(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK))) " +
" end) AS EARLY_OUT " +
" ,MAX(D.MOD_BYHAND_YN ) as by_hand " +
" ,MAX(D.MOD_BY) as mod_by " +
" FROM THR_EMPLOYEE A,THR_work_group B, THR_WORK_SHIFT C,THR_WORK_DATE D,tco_org F " +
" WHERE A.DEL_IF=0 AND B.DEL_IF=0 AND C.DEL_IF=0 AND D.DEL_IF=0 AND F.DEL_IF=0 " +
" AND A.THR_wg_PK=B.PK AND A.PK=D.THR_EMP_PK AND C.PK=D.THR_WS_PK AND A.TCO_org_PK=F.PK " +
" AND D.WORK_DT BETWEEN '" + p_from + "' AND '" + p_to + "' " +
" AND DECODE('" + p_wg + "' ,'ALL', '" + p_wg + "',B.PK) = '" + p_wg + "'" +
" AND DECODE('" + p_nation + "' ,'ALL', '" + p_nation + "',a.nation) = '" + p_nation + "'" +
" AND DECODE('" + p_thr_ws_pk + "','ALL', '" + p_thr_ws_pk + "',C.PK)= '" + p_thr_ws_pk + "' ";
SQL = SQL + " AND ( A.TCO_ORG_PK IN ( ";
SQL = SQL + " SELECT G.PK ";
SQL = SQL + " FROM TCO_ORG G ";
SQL = SQL + " WHERE G.DEL_IF = 0 ";
SQL = SQL + " START WITH G.PK = ";
SQL = SQL + " DECODE ('" + p_tco_org_pk + "', ";
SQL = SQL + " 'ALL', 0, ";
SQL = SQL + " '" + p_tco_org_pk + "' ";
SQL = SQL + " ) ";
SQL = SQL + " CONNECT BY PRIOR G.PK = G.P_PK) ";
SQL = SQL + " OR '" + p_tco_org_pk + "' = 'ALL') " +
" AND ('" + p_input + "' IS NULL " +
" OR (UPPER(A.EMP_ID) LIKE '%' || UPPER('" + p_input + "')|| '%') " +
" OR (UPPER(A.OLD_ID ) LIKE '%' || UPPER('" + p_input + "')|| '%') " +
" OR (UPPER(A.FULL_NAME) LIKE '%' || UPPER('" + p_input + "')|| '%')) " +
" AND D.TIME_IN IS NOT NULL " +
" AND D.TIME_IN BETWEEN NVL('" + p_m1 + "',D.TIME_IN) AND NVL('" + p_m2 + "',D.TIME_IN)" +
" GROUP BY F.ORG_NM,B.WORKGROUP_NM,D.WORK_DT,A.EMP_ID,A.FULL_NAME,C.REMARK,D.TIME_IN,D.TIME_OUT,D.WT " +
" ORDER BY F.ORG_NM,B.WORKGROUP_NM,D.WORK_DT,A.EMP_ID ";
}
else //if (p_opt=="8")
{
SQL = "SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') " +
" ,F.org_NM " +
" ,B.workgroup_NM " +
" ,A.EMP_ID " +
" ,A.FULL_NAME " +
" ,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD'),'DD/MM/YYYY') " +
" ,C.REMARK " +
" ,D.TIME_IN,D.TIME_OUT " +
" ,MAX(DECODE(D.HOL_TYPE, NULL, D.WT, '')) AS WT " +
" ,MAX(D.OT) AS OT " +
" ,MAX(D.NT) AS NT " +
" ,MAX(D.ONT) AS NT2 " +
" ,MAX(DECODE(D.HOL_TYPE, 'SUN', D.WT, '')) AS ST3 " +
" ,MAX(DECODE(D.HOL_TYPE,'HOL', D.WT, '')) AS HT " +
" ,(case when MAX(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK)) >=0 then NULL " +
" else " +
" ABS(MAX(HR_LATELY_EARLY(D.WORK_DT,C.START_TIME,D.DATE_IN,D.TIME_IN,'IN',A.PK))) " +
" END ) " +
" AS LATE_IN " +
",(case when MAX(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK)) >=0 then null " +
" else " +
" abs(MAX(HR_LATELY_EARLY(D.DATE_OUT,D.TIME_OUT,DECODE(C.DURING_DAY,1,D.WORK_DT,TO_CHAR(TO_DATE(D.WORK_DT,'YYYYMMDD')+1,'YYYYMMDD')),C.END_TIME,'OUT',A.PK))) " +
" end) AS EARLY_OUT " +
" ,MAX(D.MOD_BYHAND_YN ) as by_hand " +
" ,MAX(D.MOD_BY) as mod_by " +
" FROM THR_EMPLOYEE A,THR_work_group B, THR_WORK_SHIFT C,THR_WORK_DATE D,tco_org F " +
" WHERE A.DEL_IF=0 AND B.DEL_IF=0 AND C.DEL_IF=0 AND D.DEL_IF=0 AND F.DEL_IF=0 " +
" AND A.THR_wg_PK=B.PK AND A.PK=D.THR_EMP_PK AND C.PK=D.THR_WS_PK AND A.TCO_org_PK=F.PK " +
" AND D.WORK_DT BETWEEN '" + p_from + "' AND '" + p_to + "' " +
" AND DECODE('" + p_wg + "' ,'ALL', '" + p_wg + "',B.PK) = '" + p_wg + "'" +
" AND DECODE('" + p_nation + "' ,'ALL', '" + p_nation + "',a.nation) = '" + p_nation + "'" +
" AND DECODE('" + p_thr_ws_pk + "','ALL', '" + p_thr_ws_pk + "',C.PK)= '" + p_thr_ws_pk + "' ";
SQL = SQL + " AND ( A.TCO_ORG_PK IN ( ";
SQL = SQL + " SELECT G.PK ";
SQL = SQL + " FROM TCO_ORG G ";
SQL = SQL + " WHERE G.DEL_IF = 0 ";
SQL = SQL + " START WITH G.PK = ";
SQL = SQL + " DECODE ('" + p_tco_org_pk + "', ";
SQL = SQL + " 'ALL', 0, ";
SQL = SQL + " '" + p_tco_org_pk + "' ";
SQL = SQL + " ) ";
SQL = SQL + " CONNECT BY PRIOR G.PK = G.P_PK) ";
SQL = SQL + " OR '" + p_tco_org_pk + "' = 'ALL') " +
" AND ('" + p_input + "' IS NULL " +
" OR (UPPER(A.EMP_ID) LIKE '%' || UPPER('" + p_input + "')|| '%') " +
" OR (UPPER(A.OLD_ID ) LIKE '%' || UPPER('" + p_input + "')|| '%') " +
" OR (UPPER(A.FULL_NAME) LIKE '%' || UPPER('" + p_input + "')|| '%')) " +
" AND D.TIME_OUT IS NOT NULL " +
" AND D.TIME_OUT BETWEEN NVL('" + p_m1 + "',D.TIME_OUT) AND NVL('" + p_m2 + "',D.TIME_OUT)" +
" GROUP BY F.ORG_NM,B.WORKGROUP_NM,D.WORK_DT,A.EMP_ID,A.FULL_NAME,C.REMARK,D.TIME_IN,D.TIME_OUT,D.WT " +
" ORDER BY F.ORG_NM,B.WORKGROUP_NM,D.WORK_DT,A.EMP_ID ";
}
//----------------------------------
//Response.Write(SQL);
//Response.End();
DataTable dt_total = CtlLib.TableReadOpen(SQL);
int irow, icol;
irow = dt_total.Rows.Count;
icol = dt_total.Columns.Count;
if (irow == 0)
{
Response.Write("There is no data");
Response.End();
}
exSheet.Cells[1, 1].Value = "TIME SUMMARY ";
exSheet.Cells[2, 2].Value = "Work date: ";
exSheet.Cells[2, 3].Value = p_from.Substring(6, 2) + "/" + p_from.Substring(4, 2) + "/" + p_from.Substring(0, 4);
exSheet.Cells[2, 4].Value = "~";
exSheet.Cells[2, 5].Value = p_to.Substring(6, 2) + "/" + p_to.Substring(4, 2) + "/" + p_to.Substring(0, 4);
exSheet.Cells[2, 9].Value = "Reporter";
exSheet.Cells[2, 11].Value = p_user;
exSheet.Cells[3, 9].Value = "Report Date";
exSheet.Cells[3, 11].Value = dt_total.Rows[0][0].ToString();
if (p_opt != "5")
{
Response.Write("Option: ");
}
if (p_opt != "5")
{
Response.Write(p_opt_text);
}
int i;
int row_pos = 8;
//insert row
for (i = 0; i < irow - 1; i++)
{
exSheet.Range[row_pos + i, 1].Rows.EntireRow.Insert();
}
// exSheet.Range[row_pos, 1, row_pos, 18].Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
//exSheet.Range[row_pos + irow , 1, row_pos + irow , 18].Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
//show data
row_pos = 7;
for (i = 0; i < irow; i++)
{
exSheet.Cells[row_pos + i, 1].Value = i + 1;
for (int j = 1; j < icol; j++)
{
//if (j >= 9 && j < 14)
//{
// exSheet.Cells[row_pos + i, j + 1].Value = Double.Parse(dt_total.Rows[i][j].ToString());
//}
//else
//{
if (IsNumeric(dt_total.Rows[i][j].ToString()))
{
exSheet.Cells[row_pos + i, j + 1].Value = Double.Parse(dt_total.Rows[i][j].ToString());
}
else
{
exSheet.Cells[row_pos + i, j + 1].Value = dt_total.Rows[i][j].ToString();
}
// }
}
}
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
//range = exSheet.Range["A1"];
// hide row A5
//range.Rows.Hidden = true;
exBook.SaveAs(TempFile);
//CtlLib.ExcelToPdf(TempFile);
//string pdfFilePath = TempFile.Replace(".xls", ".pdf");
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
Response.WriteFile(TempFile);
//Response.WriteFile(pdfFilePath);
//Stop execute
Response.End();
}
/// <summary>
/// Customization for Pasting the data to grid
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void gridControl1_ClipboardCanPaste(object sender, GridCutPasteEventArgs e)
{
IDataObject dataObject = Clipboard.GetDataObject();
if (dataObject != null)
{
if (dataObject.GetDataPresent("Biff", true))
{
ExcelEngine xlEngine = new ExcelEngine();
IApplication app = xlEngine.Excel;
IWorkbooks workBook = app.Workbooks;
workBook.PasteWorkbook();
IWorkbook book = workBook[0];
IWorksheet worksheet = book.Worksheets[0];
IRange ur = worksheet.UsedRange;
IRange[] ranges = ur.Cells;//worksheet.UsedCells;
System.Text.StringBuilder str = new System.Text.StringBuilder();
int lastCol = ranges[0].Column, lastRow = ranges[0].Row;
int rc = ur.LastRow - ur.Row + 1;
int cc = ur.LastColumn - ur.Column + 1;
GridExcelConverterControl converter = new GridExcelConverterControl();
GridModel model = new GridModel();
model.ColCount = cc;
model.RowCount = rc;
ur.MoveTo(worksheet.Range[1, 1, rc, cc]);
ranges = worksheet.Range[1, 1, rc, cc].Cells;
foreach (IRange cell in ranges)
{
converter.ConvertExcelRangeToGrid(cell, model);
}
GridData data = new GridData();
data.InsertRows(1, rc);
data.InsertCols(1, cc);
for (int i = 1; i <= model.Data.RowCount; i++)
{
for (int j = 1; j <= model.ColCount; j++)
{
if (model.Data[i, j] != null)
{
data[i - 1, j - 1] = model.Data[i, j].Clone() as GridStyleInfoStore;
}
}
}
//Clipboard.Clear();
DataObject newDataObject = new DataObject(data);
Clipboard.SetDataObject(newDataObject, true);
xlEngine.ThrowNotSavedOnDestroy = false;
xlEngine.Dispose();
}
}
}
//
// GET: /Formulas/
public ActionResult Formulas(string button)
{
ViewData["computedValue"] = "";
//Read Formula
ViewData["formulaString"] = "";
if (button == null)
{
return(View());
}
else if (button == "Write Formula")
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(3);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#region Insert Array Formula
sheet.Range["A2"].Text = "Array formulas";
sheet.Range["B2:E2"].FormulaArray = "{10,20,30,40}";
sheet.Names.Add("ArrayRange", sheet.Range["B2:E2"]);
sheet.Range["B3:E3"].FormulaArray = "ArrayRange+100";
sheet.Range["A2"].CellStyle.Font.Bold = true;
sheet.Range["A2"].CellStyle.Font.Size = 14;
#endregion
#region Excel functions
sheet.Range["A5"].Text = "Formula";
sheet.Range["B5"].Text = "Result";
sheet.Range["A7"].Text = "ABS(ABS(-B3))";
sheet.Range["B7"].Formula = "ABS(ABS(-B3))";
sheet.Range["A9"].Text = "SUM(B3,C3)";
sheet.Range["B9"].Formula = "SUM(B3,C3)";
sheet.Range["A11"].Text = "MIN({10,20,30;5,15,35;6,16,36})";
sheet.Range["B11"].Formula = "MIN({10,20,30;5,15,35;6,16,36})";
sheet.Range["A13"].Text = "LOOKUP(B3,B3:E8)";
sheet.Range["B13"].Formula = "LOOKUP(B3,B3:E3)";
sheet.Range["A5:B5"].CellStyle.Font.Bold = true;
sheet.Range["A5:B5"].CellStyle.Font.Size = 14;
#endregion
#region Simple formulas
sheet.Range["C7"].Number = 10;
sheet.Range["C9"].Number = 10;
sheet.Range["A15"].Text = "C7+C9";
sheet.Range["B15"].Formula = "C7+C9";
#endregion
sheet.Range["B1"].Text = "Excel formula support";
sheet.Range["B1"].CellStyle.Font.Bold = true;
sheet.Range["B1"].CellStyle.Font.Size = 14;
sheet.Range["B1:E1"].Merge();
sheet.Range["A1:A15"].AutofitColumns();
try
{
//Saving the workbook to disk.
return(excelEngine.SaveAsActionResult(workbook, "Formulas.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
catch (Exception)
{
}
workbook.Close();
excelEngine.Dispose();
}
else
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath("FormulaTemplate.xls"));
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Read computed Formula Value.
ViewData["computedValue"] = sheet.Range["C1"].FormulaNumberValue;
//Read Formula
ViewData["formulaString"] = sheet.Range["C1"].Formula;
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
}
return(View());
}
private void btnWriteFormual_Click(object sender, System.EventArgs e)
{
#region Workbook Initialize
////New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
////The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Open the workbook
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
string fullPath = Path.GetFullPath(DEFAULTPATH);
//External formula from another workboook
worksheet.Range["A1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$1";
worksheet.Range["A2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$2";
worksheet.Range["A3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$3";
worksheet.Range["A4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$4";
worksheet.Range["A5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$5";
worksheet.Range["A6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$6";
worksheet.Range["A7"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$7";
worksheet.Range["B1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$1";
worksheet.Range["B2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$2";
worksheet.Range["B3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$3";
worksheet.Range["B4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$4";
worksheet.Range["B5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$5";
worksheet.Range["B6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$6";
worksheet.Range["C1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$1";
worksheet.Range["C2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$2";
worksheet.Range["C3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$3";
worksheet.Range["C4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$4";
worksheet.Range["C5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$5";
worksheet.Range["C6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$6";
worksheet.Range["D1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$1";
worksheet.Range["D2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$2";
worksheet.Range["D3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$3";
worksheet.Range["D4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$4";
worksheet.Range["D5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$5";
worksheet.Range["D6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$6";
worksheet.Range["E1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$1";
worksheet.Range["E2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$2";
worksheet.Range["E3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$3";
worksheet.Range["E4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$4";
worksheet.Range["E5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$5";
worksheet.Range["E6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$6";
worksheet.Range["F1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$F$1";
worksheet.Range["B7"].Formula = "=SUM(B2:B6)";
worksheet.Range["C7"].Formula = "=SUM(C2:C6)";
worksheet.Range["D7"].Formula = "=SUM(D2:D6)";
worksheet.Range["E7"].Formula = "=SUM(E2:E6)";
worksheet.Range["F7"].Formula = "=SUM(F2:F6)";
worksheet.Range["F2"].Formula = "=B2*C2+D2-E2";
worksheet.Range["F3"].Formula = "=B3*C3+D3-E3";
worksheet.Range["F4"].Formula = "=B4*C4+D4-E4";
worksheet.Range["F5"].Formula = "=B5*C5+D5-E5";
worksheet.Range["F6"].Formula = "=B6*C6+D6-E6";
worksheet.Range["A1:F7"].CellStyle.Font.FontName = "Verdana";
worksheet.Range["C2:F7"].NumberFormat = "_($* #,##0.00_)";
worksheet.Range["A1:F1"].CellStyle.Color = System.Drawing.Color.FromArgb(0, 0, 112, 192);
worksheet.Range["A7:F7"].CellStyle.Color = System.Drawing.Color.FromArgb(0, 0, 112, 192);
worksheet.Range["A1:F1"].CellStyle.Font.Bold = true;
worksheet.Range["A1:F1"].CellStyle.Font.Size = 11;
worksheet.Columns[0].ColumnWidth = 17;
worksheet.Columns[1].ColumnWidth = 13;
worksheet.Columns[2].ColumnWidth = 11;
worksheet.Columns[3].ColumnWidth = 11;
worksheet.Columns[4].ColumnWidth = 13;
worksheet.Columns[5].ColumnWidth = 13;
worksheet.Calculate();
#region Workbook Save
//Saving the workbook to disk.
workbook.SaveAs("ExternalFormula.xlsx");
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo("ExternalFormula.xlsx")
{
UseShellExecute = true
};
process.Start();
#else
Process.Start("ExternalFormula.xlsx");
#endif
}
#endregion
}
private void btnConvert_Click(object sender, EventArgs e)
{
string fileName = "";
#region Workbook Initialization
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Get the path of the Input file
string inputPath = GetFullTemplatePath("NorthwindTemplate.xls");
// Existing workbook is opened
IWorkbook workbook = application.Workbooks.Open(inputPath);
// The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Save Workbook or Worksheet
if (rdbWkSheet.Checked)
{
// Save the sheet as HTML
fileName = "WorksheetToHTML.html";
worksheet.SaveAsHtml(fileName);
}
else
{
//Save the book as HTML
fileName = "WorkbookToHTML.html";
workbook.SaveAsHtml(fileName, HtmlSaveOptions.Default);
}
#endregion
#region Workbook Close and Dispose
//Close the workbook and excelEngine
workbook.Close();
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the HTML?", "Conversion successful",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the HTMl file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
//***********************Set RowHeight & ColumnWidth***************
worksheet.StandardHeight = 30;
worksheet.Range["1:1"].RowHeight = 57.75;
worksheet.Range["2:9"].RowHeight = 30.25;
worksheet.Range["A:A"].ColumnWidth = 2.71;
worksheet.Range["B:B"].ColumnWidth = 11.71;
worksheet.Range["C:C"].ColumnWidth = 28;
worksheet.Range["D:D"].ColumnWidth = 22.425;
worksheet.Range["E:E"].ColumnWidth = 16.71;
worksheet.Range["F:F"].ColumnWidth = 28;
worksheet.Range["G:H"].ColumnWidth = 16.71;
worksheet.Range["I:I"].ColumnWidth = 2.71;
//**************************Set Table Value & Formulas*********************
ITable table = worksheet.Tables.Add(worksheet.Range["B2:H9"], true);
worksheet.Range["B2:H9"].Value = new object[, ]
{
{ "BID #", "DESCRIPTION", "DATE RECEIVED", "AMOUNT", "PERCENT COMPLETE", "DEADLINE", "DAYS LEFT" },
{ 1, "Bid number 1", null, 2000, 0.5, null, null },
{ 2, "Bid number 2", null, 3500, 0.25, null, null },
{ 3, "Bid number 3", null, 5000, 0.3, null, null },
{ 4, "Bid number 4", null, 4000, 0.2, null, null },
{ 5, "Bid number 5", null, 4000, 0.75, null, null },
{ 6, "Bid number 6", null, 1500, 0.45, null, null },
{ 7, "Bid number 7", null, 5000, 0.65, null, null },
};
worksheet.Range["B1"].Value = "Bid Details";
worksheet.Range["D3"].Formula = "=TODAY()-10";
worksheet.Range["D4:D5"].Formula = "=TODAY()-20";
worksheet.Range["D6"].Formula = "=TODAY()-10";
worksheet.Range["D7"].Formula = "=TODAY()-28";
worksheet.Range["D8"].Formula = "=TODAY()-17";
worksheet.Range["D9"].Formula = "=TODAY()-15";
worksheet.Range["G3:G9"].Formula = "=[@[DATE RECEIVED]]+30";
worksheet.Range["H3:H9"].Formula = "=[@DEADLINE]-TODAY()";
//****************************Set Table Style********************************
ITableStyle tableStyle = workbook.TableStyles.Add("Bid Tracker");
workbook.DefaultTableStyle = "Bid Tracker";
//Set WholeTable element style.
tableStyle.TableStyleElements[TableStyleElementType.WholeTable].Font.Color = Color.FromRGB(89, 89, 89);
tableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders.Color = Color.FromRGB(89, 89, 89);
tableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
//Set HeaderRow element style.
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Borders.Color = Color.FromRGB(89, 89, 89);
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Interior.Color = Color.FromRGB(131, 95, 1);
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Interior.PatternColor = Color.FromRGB(254, 184, 10);
//Set TotalRow element style.
tableStyle.TableStyleElements[TableStyleElementType.TotalRow].Borders.Color = Color.White;
tableStyle.TableStyleElements[TableStyleElementType.TotalRow].Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.TotalRow].Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.TotalRow].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.TotalRow].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.TotalRow].Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.TotalRow].Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
tableStyle.TableStyleElements[TableStyleElementType.TotalRow].Interior.Color = Color.FromRGB(131, 95, 1);
//***********************************Set Named Styles*****************************
IStyle titleStyle = workbook.Styles["Title"];
titleStyle.Font.Name = "Trebuchet MS";
titleStyle.Font.Size = 36;
titleStyle.Font.Color = Color.FromRGB(56, 145, 167);
titleStyle.IncludeAlignment = true;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
IStyle heading1Style = workbook.Styles["Heading 1"];
heading1Style.IncludeAlignment = true;
heading1Style.HorizontalAlignment = HorizontalAlignment.Left;
heading1Style.IndentLevel = 1;
heading1Style.VerticalAlignment = VerticalAlignment.Bottom;
heading1Style.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.None;
heading1Style.Font.Size = 14;
heading1Style.Font.Color = Color.White;
heading1Style.Font.Bold = false;
heading1Style.IncludePatterns = true;
heading1Style.Interior.Color = Color.FromRGB(131, 95, 1);
heading1Style.Font.Name = "Trebuchet MS";
IStyle dateStyle = workbook.Styles.Add("Date");
dateStyle.IncludeNumber = true;
dateStyle.NumberFormat = "m/d/yyyy";
dateStyle.IncludeAlignment = true;
dateStyle.HorizontalAlignment = HorizontalAlignment.Left;
dateStyle.IndentLevel = 1;
dateStyle.VerticalAlignment = VerticalAlignment.Center;
dateStyle.IncludeFont = false;
dateStyle.IncludeBorder = false;
dateStyle.IncludePatterns = false;
dateStyle.Font.Name = "Trebuchet MS";
IStyle commaStyle = workbook.Styles["Comma"];
commaStyle.IncludeNumber = true;
commaStyle.NumberFormat = "#,##0_);(#,##0)";
commaStyle.IncludeAlignment = true;
commaStyle.HorizontalAlignment = HorizontalAlignment.Left;
commaStyle.IndentLevel = 1;
commaStyle.VerticalAlignment = VerticalAlignment.Center;
commaStyle.Font.Name = "Trebuchet MS";
IStyle normalStyle = workbook.Styles["Normal"];
normalStyle.HorizontalAlignment = HorizontalAlignment.Left;
normalStyle.IndentLevel = 1;
normalStyle.VerticalAlignment = VerticalAlignment.Center;
normalStyle.WrapText = true;
normalStyle.Font.Color = Color.FromRGB(89, 89, 89);
IStyle currencyStyle = workbook.Styles["Currency"];
currencyStyle.NumberFormat = "$#,##0.00";
currencyStyle.IncludeAlignment = true;
currencyStyle.HorizontalAlignment = HorizontalAlignment.Left;
currencyStyle.IndentLevel = 1;
currencyStyle.VerticalAlignment = VerticalAlignment.Center;
currencyStyle.Font.Name = "Trebuchet MS";
IStyle percentStyle = workbook.Styles["Percent"];
percentStyle.IncludeAlignment = true;
percentStyle.HorizontalAlignment = HorizontalAlignment.Right;
percentStyle.VerticalAlignment = VerticalAlignment.Center;
percentStyle.IncludeFont = true;
percentStyle.Font.Name = "Trebuchet MS";
percentStyle.Font.Size = 20;
percentStyle.Font.Bold = true;
percentStyle.Font.Color = Color.FromRGB(89, 89, 89);
percentStyle.Font.Name = "Trebuchet MS";
IStyle comma0Style = workbook.Styles["Comma [0]"];
comma0Style.NumberFormat = "#,##0_);(#,##0)";
comma0Style.IncludeAlignment = true;
comma0Style.HorizontalAlignment = HorizontalAlignment.Right;
comma0Style.IndentLevel = 3;
comma0Style.VerticalAlignment = VerticalAlignment.Center;
percentStyle.Font.Name = "Trebuchet MS";
//************************************Add Conditional Formatting****************
IDataBar dataBar = worksheet.Range["F3:F9"].FormatConditions.AddDatabar();
dataBar.MinPoint.Type = ConditionValueTypes.Number;
dataBar.MinPoint.Value = 1;
dataBar.MaxPoint.Type = ConditionValueTypes.Number;
dataBar.MaxPoint.Value = 0;
dataBar.BarFillType = DataBarFillType.Gradient;
dataBar.BarColor.Color = Color.FromRGB(126, 194, 211);
dataBar.Direction = DataBarDirection.Context;
dataBar.AxisColor.Color = Color.Black;
dataBar.AxisPosition = DataBarAxisPosition.Automatic;
dataBar.NegativeBarFormat.ColorType = DataBarNegativeColorType.Color;
dataBar.NegativeBarFormat.Color.Color = Color.Red;
dataBar.ShowValue = true;
//****************************************Use NamedStyle**************************
worksheet.SheetView.DisplayGridlines = false;
table.TableStyle = tableStyle;
worksheet.Range["B1"].Style = titleStyle;
worksheet.Range["B1"].WrapText = false;
worksheet.Range["B2:H2"].Style = heading1Style;
worksheet.Range["B3:B9"].Style = commaStyle;
worksheet.Range["C3:C9"].Style = normalStyle;
worksheet.Range["D3:D9"].Style = dateStyle;
worksheet.Range["E3:E9"].Style = currencyStyle;
worksheet.Range["F3:F9"].Style = percentStyle;
worksheet.Range["G3:G9"].Style = dateStyle;
worksheet.Range["H3:H9"].Style = comma0Style;
}
//
// GET: /BudgetPlanner/
public ActionResult BudgetPlanner(string budget, string button)
{
if (budget == null)
{
return(View());
}
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
else if (button == "Input Template")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.EnablePartialTrustCode = true;
IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"BudgetPlanner.xls"));
return(excelEngine.SaveAsActionResult(workbook, "Template.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.EnablePartialTrustCode = true;
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//The new workbook will have 12 worksheets
IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"BudgetPlanner.xls"));
IWorksheet sheet = workbook.Worksheets[1];
sheet.FirstVisibleRow = 3;
IFont font = workbook.CreateFont();
font.Bold = true;
#region TextBox
ITextBoxShape textbox = sheet.TextBoxes.AddTextBox(5, 2, 40, 140);
textbox.Text = "Quick Budget";
textbox.RichText.SetFont(0, 11, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(204, 204, 255);
textbox = sheet.TextBoxes.AddTextBox(7, 2, 25, 140);
textbox.Text = "Income";
textbox.RichText.SetFont(0, 5, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);
textbox = sheet.TextBoxes.AddTextBox(16, 2, 25, 140);
textbox.Text = "Spending";
textbox.RichText.SetFont(0, 7, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);
#endregion
#region Write Text and Numbers
sheet.Range["O6"].Text = "Weekly";
sheet.Range["E7"].Text = "Frequency";
sheet.Range["F7"].Text = "Amount";
sheet.Range["G7"].Text = "Monthly";
sheet.Range["H7"].Text = "Yearly";
sheet.Range["B8"].Text = "Total Income";
sheet.Range["C9"].Text = "Salary/Wages";
sheet.Range["C10"].Text = "Salary/Wages(Spouse)";
sheet.Range["C11"].Text = "Other";
sheet.Range["C12"].Text = "Other";
sheet.Range["C13"].Text = "Other";
sheet.Range["B17"].Text = "Transportation";
sheet.Range["F25"].Number = 3000;
sheet.Range["F9"].Number = 55000;
sheet.Range["F10"].Number = 35000;
sheet.Range["C18"].Text = "Auto Loan/Lease";
sheet.Range["C19"].Text = "Insurance";
sheet.Range["C20"].Text = "Gas ";
sheet.Range["C21"].Text = "Maintenance ";
sheet.Range["C22"].Text = "Registration/Inspection";
sheet.Range["C23"].Text = "Bill's train pass";
sheet.Range["C24"].Text = "Jane's bus pass";
sheet.Range["C25"].Text = "Other";
sheet.Range["E16"].Text = "Total";
sheet.Range["N6"].Text = "Chart";
sheet.Range["B27"].Text = "Home";
sheet.Range["F28"].Number = 20000;
sheet.Range["F29"].Number = 5000;
sheet.Range["F33"].Number = 5000;
sheet.Range["C28"].Text = "EMI";
sheet.Range["C29"].Text = "Rent";
sheet.Range["C30"].Text = "Maintanence";
sheet.Range["C31"].Text = "Insurance";
sheet.Range["C32"].Text = "Furniture";
sheet.Range["C33"].Text = "Household Supplies";
sheet.Range["C34"].Text = "Groceries";
sheet.Range["C35"].Text = "Real Estate Tax";
sheet.Range["C36"].Text = "Other";
sheet.Range["B39"].Text = "Utilities";
sheet.Range["F41"].Number = 1000;
sheet.Range["F42"].Number = 250;
sheet.Range["F43"].Number = 150;
sheet.Range["F45"].Number = 175;
sheet.Range["C40"].Text = "Phone - Home";
sheet.Range["C41"].Text = "Phone - Cell";
sheet.Range["C42"].Text = "Cable";
sheet.Range["C43"].Text = "Gas";
sheet.Range["C44"].Text = "Water";
sheet.Range["C45"].Text = "Electricity";
sheet.Range["C46"].Text = "Internet";
sheet.Range["C47"].Text = "Other";
sheet.Range["B50"].Text = "Health";
sheet.Range["F53"].Number = 500;
sheet.Range["C51"].Text = "Dental";
sheet.Range["C52"].Text = "Medical";
sheet.Range["C53"].Text = "Medication";
sheet.Range["C54"].Text = "Vision/contacts";
sheet.Range["C55"].Text = "Life Insurance";
sheet.Range["C56"].Text = "Electricity";
sheet.Range["C57"].Text = "Other";
#endregion
#region Cell styles
IStyle tableStyle = workbook.Styles.Add("TableStyle");
tableStyle.BeginUpdate();
tableStyle.Color = Color.White;
tableStyle.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeTop].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableStyle.EndUpdate();
sheet.Range["E7:H7"].CellStyle.Font.Bold = true;
sheet.Range["B17"].CellStyle.Font.Bold = true;
sheet.Range["B27"].CellStyle.Font.Bold = true;
sheet.Range["B39"].CellStyle.Font.Bold = true;
sheet.Range["B50"].CellStyle.Font.Bold = true;
sheet.Range["E7:H7"].CellStyle.Font.Underline = ExcelUnderline.Single;
sheet.Range["B7:H14"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C9:C13"].CellStyle = tableStyle;
sheet.Range["E9:F13"].CellStyle = tableStyle;
sheet.Range["B16:H26"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["B17:C17"].CellStyle.Color = Color.White;
sheet.Range["C18:C25"].CellStyle = tableStyle;
sheet.Range["O6"].CellStyle = tableStyle;
sheet.Range["E18:F25"].CellStyle = tableStyle;
sheet.Range["B27:H38"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C28:C36"].CellStyle = tableStyle;
sheet.Range["B27:C27"].CellStyle.Color = Color.White;
sheet.Range["E28:F36"].CellStyle = tableStyle;
sheet.Range["B39:H49"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C40:C47"].CellStyle = tableStyle;
sheet.Range["B39:C39"].CellStyle.Color = Color.White;
sheet.Range["E40:F47"].CellStyle = tableStyle;
sheet.Range["B50:H58"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C51:C57"].CellStyle = tableStyle;
sheet.Range["B50:C50"].CellStyle.Color = Color.White;
sheet.Range["E51:F57"].CellStyle = tableStyle;
#endregion
#region Data Validation
IDataValidation validation = sheet.Range["E9:E13"].DataValidation;
sheet.Range["E9:E13"].Text = "Monthly";
validation.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation1 = sheet.Range["E18:E25"].DataValidation;
sheet.Range["E18:E25"].Text = "Monthly";
validation1.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation2 = sheet.Range["O6"].DataValidation;
validation2.ListOfValues = new string[] { "Weekly", "Monthly", "Yearly" };
IDataValidation validation3 = sheet.Range["E28:E37"].DataValidation;
sheet.Range["E28:E36"].Text = "Monthly";
validation3.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation4 = sheet.Range["E40:E47"].DataValidation;
sheet.Range["E40:E47"].Text = "Monthly";
validation4.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation5 = sheet.Range["E51:E57"].DataValidation;
sheet.Range["E51:E57"].Text = "Monthly";
validation5.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
#endregion
#region Formulas
sheet.Range["G8"].Formula = "SUM(G9:G13)";
sheet.Range["H8"].Formula = "SUM(H9:H13)";
sheet.Range["G17"].Formula = "SUM(G18:G25)";
sheet.Range["H17"].Formula = "SUM(H18:H25)";
sheet.Range["G16"].Formula = "G17+G27+G39+G50+G59+G71";
sheet.Range["h16"].Formula = "H17+H27+H39+H50+H59+H71";
for (int i = 9; i <= 13; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
for (int i = 18; i <= 25; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
sheet.Range["G27"].Formula = "SUM(G28:G36)";
sheet.Range["H27"].Formula = "SUM(H28:H37)";
for (int i = 28; i <= 36; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
sheet.Range["G39"].Formula = "SUM(G40:G47)";
sheet.Range["H39"].Formula = "SUM(H40:H47)";
for (int i = 40; i <= 47; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
sheet.Range["G50"].Formula = "SUM(G51:G57)";
sheet.Range["H50"].Formula = "SUM(H51:H57)";
for (int i = 51; i <= 57; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
#endregion
#region SummaryChart
//Clustered Column Chart
IChartShape chart = sheet.Charts.Add();
//Set Chart Type
chart.ChartType = ExcelChartType.Bar_Clustered;
//Set DataRange.
chart.Series.Add("Expense");
chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["N10"];
chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
chart.Series[0].DataPoints[0].DataLabels.Size = 7f;
chart.Series.Add("Income");
chart.Series[1].Values = workbook.Worksheets["Sheet1"].Range["N9"];
chart.Series[1].DataPoints[0].DataLabels.IsValue = true;
chart.Series[1].DataPoints[0].DataLabels.Size = 7f;
chart.Series.Add("Balance");
chart.Series[2].Values = workbook.Worksheets["Sheet1"].Range["N8"];
chart.Series[2].DataPoints[0].DataLabels.IsValue = true;
chart.Series[2].DataPoints[0].DataLabels.Size = 7f;
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
chart.PrimaryCategoryAxis.Visible = false;
//Format Chart Area
IChartFrameFormat chartArea = chart.ChartArea;
//Style
chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chartArea.Border.LineColor = Color.Gray;
chartArea.Border.LineWeight = ExcelChartLineWeight.Medium;
//Plot Area
IChartFrameFormat chartPlotArea = chart.PlotArea;
chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chart.PlotArea.Border.LineColor = Color.Gray;
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Embedded chart position.
chart.TopRow = 7;
chart.BottomRow = 22;
chart.LeftColumn = 9;
chart.RightColumn = 16;
chart.ChartTitle = "Budget Summary";
chart.ChartTitleArea.Bold = true;
#endregion
#region SpendingChart
chart = sheet.Charts.Add();
chart.ChartTitle = "Spending Summary";
chart.ChartTitleArea.Bold = true;
//Set Chart Type
chart.ChartType = ExcelChartType.Pie_3D;
//Set DataRange.
chart.DataRange = workbook.Worksheets["Sheet1"].Range["J9:K12"];
chart.IsSeriesInRows = false;
chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["K9:K12"];
chart.Series[0].CategoryLabels = workbook.Worksheets["Sheet1"].Range["J9:J12"];
chart.Series[0].Name = "Spending summary";
chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
chart.Series[0].DataPoints[0].DataLabels.Size = 7f;
chart.Series[0].DataPoints[1].DataLabels.IsValue = true;
chart.Series[0].DataPoints[1].DataLabels.Size = 7f;
chart.Series[0].DataPoints[2].DataLabels.IsValue = true;
chart.Series[0].DataPoints[2].DataLabels.Size = 7f;
chart.Series[0].DataPoints[3].DataLabels.IsValue = true;
chart.Series[0].DataPoints[3].DataLabels.Size = 7f;
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
//Format Chart Area
chartArea = chart.ChartArea;
//Style
chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chartArea.Border.LineColor = Color.Gray;
chartArea.Border.LineWeight = ExcelChartLineWeight.Medium;
//Plot Area
chartPlotArea = chart.PlotArea;
chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chart.PlotArea.Border.LineColor = Color.Gray;
chartPlotArea.Fill.ForeColor = Color.FromArgb(223, 223, 223);
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Embedded chart position.
chart.TopRow = 25;
chart.BottomRow = 42;
chart.LeftColumn = 9;
chart.RightColumn = 16;
#endregion
#region Sheet View
workbook.Worksheets["Sheet1"].Visibility = WorksheetVisibility.Hidden;
workbook.Worksheets[0].Activate();
workbook.TabSheets[0].TabColor = ExcelKnownColors.Blue;
workbook.TabSheets[1].TabColor = ExcelKnownColors.Blue;
workbook.Worksheets[1].IsRowColumnHeadersVisible = false;
sheet.InsertColumn(9);
#endregion
try
{
//Saving the workbook to disk.
//return excelEngine.SaveAsActionResult(workbook, "BudgetPlanner.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog);
if (budget == "Xls")
{
//Save as .xls format
return(excelEngine.SaveAsActionResult(workbook, "BudgetPlanner.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
//Save as .xlsx format
else
{
workbook.Version = ExcelVersion.Excel2016;
return(excelEngine.SaveAsActionResult(workbook, "BudgetPlanner.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
return(View());
}
}
static List<Str_DataTable?> CreateExcelDocument_GetDataTables_PivotTables(IWorksheet Ws_Parameters)
{
List<Str_DataTable?> List_Dtp = new List<Str_DataTable?>();
Int32[] LineInfo = CreateExcelDocument_ReadLineInfo(Ws_Parameters, CnsExcelKeyword_DataTable_Pivot, CnsExcelKeyword_DataTable_Pivot_End);
Int32 Ct_Start = LineInfo[0];
Int32 Ct_End = LineInfo[1];
if (Ct_Start == 0 && Ct_End == 0)
{ return List_Dtp; }
Int32 DataTable_Ct = 0;
for (Int32 Ct = Ct_Start; Ct <= Ct_End; Ct++)
{
String ExcelText = "";
try { ExcelText = Ws_Parameters.Range["A" + Ct.ToString()].Characters.Text; }
catch { }
String DataTable_Name = "";
String DataTable_ParentName = "";
String DataTable_SourceKey = "";
String DataTable_TargetKey = "";
String DataTable_Location = "";
if (!(Strings.InStr(ExcelText, "[") > 0))
{
try
{
DataTable_Ct++;
String[] Arr_ExcelText = Strings.Split(ExcelText, " ");
DataTable_Name = Arr_ExcelText[0];
DataTable_Location = Arr_ExcelText[1];
try
{
if (Arr_ExcelText.Length > 2)
{
DataTable_ParentName = Arr_ExcelText[3];
DataTable_SourceKey = Arr_ExcelText[4];
DataTable_TargetKey = Arr_ExcelText[5];
}
}
catch { }
Str_DataTable Dt_New = new Str_DataTable();
Dt_New.Ct = DataTable_Ct;
Dt_New.Name = DataTable_Name;
Dt_New.Location = DataTable_Location;
Dt_New.ParentName = DataTable_ParentName;
Dt_New.SourceKey = DataTable_SourceKey;
Dt_New.TargetKey = DataTable_TargetKey;
List_Dtp.Add(Dt_New);
}
catch
{ throw new Exception(@"Invalid Syntax in [#]DataTable_Pivot."); }
}
}
return List_Dtp;
}
/// <summary>
/// Creates spreadsheet
/// </summary>
/// <param name="sender">Contains a reference to the control/object that raised the event</param>
/// <param name="e">Contains the event data</param>
private void btnCreate_Click(object sender, RoutedEventArgs e)
{
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Name = "Invoice";
sheet.IsGridLinesVisible = false;
sheet.EnableSheetCalculations();
sheet.Range["A1"].ColumnWidth = 4.82;
sheet.Range["B1:C1"].ColumnWidth = 13.82;
sheet.Range["D1"].ColumnWidth = 12.20;
sheet.Range["E1"].ColumnWidth = 8.50;
sheet.Range["F1"].ColumnWidth = 9.73;
sheet.Range["G1"].ColumnWidth = 8.82;
sheet.Range["H1"].ColumnWidth = 4.46;
sheet.Range["A1:H1"].CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(51, 63, 79);
sheet.Range["A1:H1"].Merge();
sheet.Range["B4:D6"].Merge();
sheet.Range["B4"].Text = "INVOICE";
sheet.Range["B4"].CellStyle.Font.Bold = true;
sheet.Range["B4"].CellStyle.Font.Size = 32;
IStyle style1 = workbook.Styles.Add("style1");
style1.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium;
style1.Borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Grey_40_percent;
sheet.Range["B7:G7"].Merge();
sheet.Range["B7:G7"].CellStyle = style1;
sheet.Range["B13:G13"].Merge();
sheet.Range["B13:G13"].CellStyle = style1;
sheet.Range["B8"].Text = "BILL TO:";
sheet.Range["B8"].CellStyle.Font.Size = 9;
sheet.Range["B8"].CellStyle.Font.Bold = true;
sheet.Range["B9"].Text = "Abraham Swearegin";
sheet.Range["B9"].CellStyle.Font.Size = 12;
sheet.Range["B9"].CellStyle.Font.Bold = true;
sheet.Range["B10"].Text = "United States, California, San Mateo,";
sheet.Range["B10"].CellStyle.Font.Size = 9;
sheet.Range["B11"].Text = "9920 BridgePointe Parkway,";
sheet.Range["B11"].CellStyle.Font.Size = 9;
sheet.Range["B12"].Number = 9365550136;
sheet.Range["B12"].CellStyle.Font.Size = 9;
sheet.Range["B12"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
IRange range1 = sheet.Range["F8:G8"];
IRange range2 = sheet.Range["F9:G9"];
IRange range3 = sheet.Range["F10:G10"];
IRange range4 = sheet.Range["E11:G11"];
IRange range5 = sheet.Range["F12:G12"];
range1.Merge();
range2.Merge();
range3.Merge();
range4.Merge();
range5.Merge();
sheet.Range["F8"].Text = "INVOICE#";
range1.CellStyle.Font.Size = 8;
range1.CellStyle.Font.Bold = true;
range1.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["F9"].Number = 2058557939;
range2.CellStyle.Font.Size = 9;
range2.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["F10"].Text = "DATE";
range3.CellStyle.Font.Size = 8;
range3.CellStyle.Font.Bold = true;
range3.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["E11"].DateTime = new DateTime(2020, 08, 31);
sheet.Range["E11"].NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy";
range4.CellStyle.Font.Size = 9;
range4.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
range5.CellStyle.Font.Size = 8;
range5.CellStyle.Font.Bold = true;
range5.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
IRange range6 = sheet.Range["B15:G15"];
range6.CellStyle.Font.Size = 10;
range6.CellStyle.Font.Bold = true;
sheet.Range[15, 2].Text = "Code";
sheet.Range[16, 2].Text = "CA-1098";
sheet.Range[17, 2].Text = "LJ-0192";
sheet.Range[18, 2].Text = "So-B909-M";
sheet.Range[19, 2].Text = "FK-5136";
sheet.Range[20, 2].Text = "HL-U509";
sheet.Range[15, 3].Text = "Description";
sheet.Range[16, 3].Text = "AWC Logo Cap";
sheet.Range[17, 3].Text = "Long-Sleeve Logo Jersey, M";
sheet.Range[18, 3].Text = "Mountain Bike Socks, M";
sheet.Range[19, 3].Text = "ML Fork";
sheet.Range[20, 3].Text = "Sports-100 Helmet, Black";
sheet.Range[15, 3, 15, 4].Merge();
sheet.Range[16, 3, 16, 4].Merge();
sheet.Range[17, 3, 17, 4].Merge();
sheet.Range[18, 3, 18, 4].Merge();
sheet.Range[19, 3, 19, 4].Merge();
sheet.Range[20, 3, 20, 4].Merge();
sheet.Range[15, 5].Text = "Quantity";
sheet.Range[16, 5].Number = 2;
sheet.Range[17, 5].Number = 3;
sheet.Range[18, 5].Number = 2;
sheet.Range[19, 5].Number = 6;
sheet.Range[20, 5].Number = 1;
sheet.Range[15, 6].Text = "Price";
sheet.Range[16, 6].Number = 8.99;
sheet.Range[17, 6].Number = 49.99;
sheet.Range[18, 6].Number = 9.50;
sheet.Range[19, 6].Number = 175.49;
sheet.Range[20, 6].Number = 34.99;
sheet.Range[15, 7].Text = "Total";
sheet.Range[16, 7].Formula = "=E16*F16+(E16*F16)";
sheet.Range[17, 7].Formula = "=E17*F17+(E17*F17)";
sheet.Range[18, 7].Formula = "=E18*F18+(E18*F18)";
sheet.Range[19, 7].Formula = "=E19*F19+(E19*F19)";
sheet.Range[20, 7].Formula = "=E20*F20+(E20*F20)";
sheet.Range[15, 6, 20, 7].NumberFormat = "$#,##0.00";
sheet.Range["E15:G15"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["B15:G15"].CellStyle.Font.Size = 10;
sheet.Range["B15:G15"].CellStyle.Font.Bold = true;
sheet.Range["B16:G20"].CellStyle.Font.Size = 9;
sheet.Range["E22:G22"].Merge();
sheet.Range["E22:G22"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["E23:G24"].Merge();
IRange range7 = sheet.Range["E22"];
IRange range8 = sheet.Range["E23"];
range7.Text = "TOTAL";
range7.CellStyle.Font.Size = 8;
range7.CellStyle.Font.Color = ExcelKnownColors.Blue_grey;
range8.Formula = "=SUM(G16:G20)";
range8.NumberFormat = "$#,##0.00";
range8.CellStyle.Font.Size = 24;
range8.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
range8.CellStyle.Font.Bold = true;
sheet.Range[26, 1].Text = "800 Interchange Blvd, Suite 2501, Austin, TX 78721 | [email protected]";
sheet.Range[26, 1].CellStyle.Font.Size = 8;
IRange range9 = sheet.Range["A26:H27"];
range9.CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(172, 185, 202);
range9.Merge();
range9.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range9.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
string inputPath = "syncfusion.xlsiodemos.winui.Assets.XlsIO.invoice.jpeg";
Assembly assembly = typeof(FormatCells).GetTypeInfo().Assembly;
Stream input = assembly.GetManifestResourceStream(inputPath);
sheet.Pictures.AddPicture(3, 4, 7, 8, input);
string OutputFilename = "Invoice.xlsx";
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
Save(stream, OutputFilename);
stream.Dispose();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
}
}
static List<Str_DataTable_Field?> CreateExcelDocument_GetFields(List<Str_DataTable_Section?> List_Section, IWorksheet Ws_Template)
{
var List_Table = (
from O in
(from O in List_Section
where O.HasValue
select O.Value)
select new Str_DataTable?(
new Str_DataTable() { Ct = O.Ct, Name = O.Name, Location = O.Location })).ToList();
return CreateExcelDocument_GetFields(List_Table, Ws_Template);
}
//
// GET: /Bar/
public ActionResult EmbeddedChart(string button, string SaveOption)
{
string basePath = _hostingEnvironment.WebRootPath;
if (button == null)
{
return(View());
}
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//Open workbook with Data
FileStream inputStream = new FileStream(basePath + @"/XlsIO/EmbeddedChart.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
if (SaveOption == "Xls")
{
workbook.Version = ExcelVersion.Excel97to2003;
}
else
{
workbook.Version = ExcelVersion.Excel2016;
}
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
sheet.Name = "Sample";
// Adding a New chart to the Existing Worksheet
IChartShape chart = workbook.Worksheets[0].Charts.Add();
chart.DataRange = sheet.Range["A3:C15"];
chart.ChartTitle = "Crescent City, CA";
chart.IsSeriesInRows = false;
chart.PrimaryValueAxis.Title = "Precipitation,in.";
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
chart.PrimaryValueAxis.MaximumValue = 14.0;
chart.PrimaryValueAxis.NumberFormat = "0.0";
chart.PrimaryCategoryAxis.Title = "Month";
IChartSerie serieOne = chart.Series[0];
//set the Chart Type
chart.ChartType = ExcelChartType.Column_Clustered_3D;
//set the Backwall fill option
chart.BackWall.Fill.FillType = ExcelFillType.Gradient;
//set the Texture Type
chart.BackWall.Fill.GradientColorType = ExcelGradientColor.TwoColor;
chart.BackWall.Fill.GradientStyle = ExcelGradientStyle.Diagonl_Down;
chart.BackWall.Fill.ForeColor = Color.WhiteSmoke;
chart.BackWall.Fill.BackColor = Color.LightBlue;
//set the Border Linecolor
chart.BackWall.Border.LineColor = Color.Wheat;
//set the Picture Type
chart.BackWall.PictureUnit = ExcelChartPictureType.stretch;
//set the Backwall thickness
chart.BackWall.Thickness = 10;
//set the sidewall fill option
chart.SideWall.Fill.FillType = ExcelFillType.SolidColor;
//set the sidewall foreground and backcolor
chart.SideWall.Fill.BackColor = Color.White;
chart.SideWall.Fill.ForeColor = Color.White;
//set the side wall Border color
chart.SideWall.Border.LineColor = Color.Beige;
//set floor fill option
chart.Floor.Fill.FillType = ExcelFillType.Pattern;
//set the floor pattern Type
chart.Floor.Fill.Pattern = ExcelGradientPattern.Pat_Divot;
//Set the floor fore and Back ground color
chart.Floor.Fill.ForeColor = Color.Blue;
chart.Floor.Fill.BackColor = Color.White;
//set the floor thickness
chart.Floor.Thickness = 3;
IChartSerie serieTwo = chart.Series[1];
//Show value as data labels
serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
serieTwo.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
//Embedded Chart Position
chart.TopRow = 2;
chart.BottomRow = 30;
chart.LeftColumn = 5;
chart.RightColumn = 18;
serieTwo.Name = "Temperature,deg.F";
// Legend setting
chart.Legend.Position = ExcelLegendPosition.Right;
chart.Legend.IsVerticalLegend = false;
try
{
string ContentType = null;
string fileName = null;
if (SaveOption == "Xls")
{
workbook.Version = ExcelVersion.Excel97to2003;
ContentType = "Application/vnd.ms-excel";
fileName = "EmbeddedChart.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "EmbeddedChart.xlsx";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
catch (Exception)
{
}
workbook.Close();
excelEngine.Dispose();
return(View());
}
static List<Str_DataTable_Section?> CreateExcelDocument_GetSections(IWorksheet Ws_Parameters, String Line_Start, String Line_End)
{
List<Str_DataTable_Section?> List_Dts = new List<Str_DataTable_Section?>();
Int32[] LineInfo = CreateExcelDocument_ReadLineInfo(Ws_Parameters, Line_Start, Line_End);
Int32 Ct_Start = LineInfo[0];
Int32 Ct_End = LineInfo[1];
if (Ct_Start == 0 && Ct_End == 0)
{ throw new Exception(@"Invalid Syntax in " + Line_Start + @"."); }
Int32 DataTable_Ct = 0;
for (Int32 Ct = Ct_Start; Ct <= Ct_End; Ct++)
{
String ExcelText = "";
try { ExcelText = Ws_Parameters.Range["A" + Ct.ToString()].Characters.Text; }
catch { }
String Name = "";
String Location = "";
if (!(Strings.InStr(ExcelText, "[") > 0))
{
try
{
DataTable_Ct++;
Name = Strings.Mid(ExcelText, 1, Strings.InStr(ExcelText, " ") - 1);
Location = Strings.Mid(ExcelText, Strings.InStr(ExcelText, " ") + 1);
List_Dts.Add(
new Str_DataTable_Section()
{
Ct = DataTable_Ct,
Name = Name,
Location = Location
});
}
catch
{ throw new Exception(@"Invalid Syntax in " + Line_Start + "."); }
}
}
return List_Dts;
}
private void btnCreate_Click(object sender, EventArgs e)
{
#region Initialize Workbook
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
if (this.rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
}
else if (this.rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(3);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Get the Path of the Input File
//Get the Path of the Header Image
string headerImagePath = GetFullImagePath("header.gif");
//Get the Path of the PDF file
string pdfFilePath = GetFullTemplatePath("FaxOrderForm.pdf");
//Get the path of the PDF Icon
string pdfIconPath = GetFullImagePath("pdfIcon.jpg");
//Get the Path of Work file
string wordFilePath = GetFullTemplatePath("FaxOrderForm.doc");
//Get the Path of Work Icon
string wordIconPath = GetFullImagePath("wordIcon.jpg");
#endregion
#region Add OleObject into Worksheet
worksheet.IsGridLinesVisible = false;
worksheet.Pictures.AddPicture(2, 5, headerImagePath);
worksheet["E5:M6"].Merge();
worksheet[5, 5].Text = "Syncfusion accept fax orders from customers worldwide. You can also order online through our secure web server.";
worksheet[5, 5].WrapText = true;
//Add Oleobject for PDF file
worksheet[8, 6].Text = "PDF Order Form";
IOleObject oleObject1 = worksheet.OleObjects.Add(pdfFilePath, System.Drawing.Image.FromFile(pdfIconPath), OleLinkType.Embed);
oleObject1.Location = worksheet[8, 11];
oleObject1.Size = new Size(100, 100);
//Add OleObject for Word file
worksheet[17, 6].Text = "Word Order Form";
IOleObject oleObject2 = worksheet.OleObjects.Add(wordFilePath, System.Drawing.Image.FromFile(wordIconPath), OleLinkType.Embed);
oleObject2.Location = worksheet[17, 11];
oleObject2.Size = new Size(100, 100);
worksheet[25, 5].Text = "Download the order form, print it out and fill in the required information.";
#endregion
#region Workbook Save
if (this.rdbExcel2007.Checked)
{
workbook.SaveAs("OleObjectSample.xlsx");
}
else if (this.rdbExcel2010.Checked)
{
workbook.SaveAs("OleObjectSample.xlsx");
}
else
{
workbook.SaveAs("OleObjectSample.xlsx");
}
#endregion
#region Workbook Close and Dispose
//Close the Workbook
workbook.Close();
//Dispose the ExcelEngine
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
try
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo("OleObjectSample.xlsx")
{
UseShellExecute = true
};
process.Start();
#else
Process.Start("OleObjectSample.xlsx");
#endif
//Exit
this.Close();
}
catch (Win32Exception)
{
MessageBox.Show("MS Excel is not installed in this system");
}
}
else
{
// Exit
this.Close();
}
#endregion
}
public void InsertIntoLocation(IWorksheet targetWorksheet, int targetRow, int targetColumn,
InsertShiftDirection shiftDirection)
{
AssertNotDisposed();
var targetRange = targetWorksheet.GetRange(targetRow, targetColumn, targetRow + EndRow - StartRow,
targetColumn + EndColumn - StartColumn) as Range;
if (targetRange == null)
{
throw new InvalidOperationException("Implementation of this method depends on another Office Interop wrapper.");
}
Excel.Range _range = _GetRange();
Excel.Range _targetRange = targetRange._GetRange();
lock (Synchronization.ClipboardSyncRoot)
{
_targetRange.Insert(EnumConvert.ConvertInsertShiftDirection(shiftDirection), _range.Copy());
}
Marshal.ReleaseComObject(_targetRange);
Marshal.ReleaseComObject(_range);
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
#region theme
//create a custom theme.
Themes themes = new Themes();
ITheme theme = themes.Add("testTheme");
theme.ThemeColorScheme[ThemeColor.Light1].RGB = Color.FromArgb(255, 255, 255);
theme.ThemeColorScheme[ThemeColor.Dark1].RGB = Color.FromArgb(0, 0, 0);
theme.ThemeColorScheme[ThemeColor.Light2].RGB = Color.FromArgb(222, 222, 212);
theme.ThemeColorScheme[ThemeColor.Dark2].RGB = Color.FromArgb(30, 46, 47);
theme.ThemeColorScheme[ThemeColor.Accent1].RGB = Color.FromArgb(233, 117, 90);
theme.ThemeColorScheme[ThemeColor.Accent2].RGB = Color.FromArgb(122, 182, 186);
theme.ThemeColorScheme[ThemeColor.Accent3].RGB = Color.FromArgb(125, 181, 135);
theme.ThemeColorScheme[ThemeColor.Accent4].RGB = Color.FromArgb(230, 191, 94);
theme.ThemeColorScheme[ThemeColor.Accent5].RGB = Color.FromArgb(230, 143, 77);
theme.ThemeColorScheme[ThemeColor.Accent6].RGB = Color.FromArgb(194, 107, 112);
theme.ThemeColorScheme[ThemeColor.Hyperlink].RGB = Color.FromArgb(122, 182, 186);
theme.ThemeColorScheme[ThemeColor.FollowedHyperlink].RGB = Color.FromArgb(166, 140, 177);
theme.ThemeFontScheme.Major[FontLanguageIndex.Latin].Name = "Gill Sans";
theme.ThemeFontScheme.Minor[FontLanguageIndex.Latin].Name = "Gill Sans";
//assign the custom theme for workbook.
workbook.Theme = theme;
#endregion
//does not show sheet gridlines.
worksheet.SheetView.DisplayGridlines = false;
#region RowHeightColumnWidth
//set row height and column width.
worksheet.StandardHeight = 12.75;
worksheet.StandardWidth = 8.43;
worksheet.Rows[1].RowHeight = 32.25;
worksheet.Rows[2].RowHeight = 13.5;
worksheet.Rows[3].RowHeight = 18.75;
worksheet.Rows[6].RowHeight = 18.75;
worksheet.Rows[9].RowHeight = 18.75;
worksheet.Rows[12].RowHeight = 18.75;
worksheet.Rows[15].RowHeight = 19.5;
worksheet.Rows[16].RowHeight = 13.5;
worksheet.Rows[33].RowHeight = 19.5;
worksheet.Rows[34].RowHeight = 13.5;
worksheet.Columns[0].ColumnWidth = 1.7109375;
worksheet.Columns[1].ColumnWidth = 12.140625;
worksheet.Columns[2].ColumnWidth = 12.140625;
worksheet.Columns[3].ColumnWidth = 12.140625;
worksheet.Columns[4].ColumnWidth = 11.85546875;
worksheet.Columns[5].ColumnWidth = 12.7109375;
worksheet.Columns[6].ColumnWidth = 13.85546875;
worksheet.Columns[7].ColumnWidth = 44.7109375;
#endregion
#region Values
//initialize worksheet's values.
worksheet.Name = "BLOOD PRESSURE DATA";
worksheet.Range["B2"].Value = "BLOOD PRESSURE TRACKER";
worksheet.Range["B4:F13"].Value = new object[, ] {
{ "NAME", null, null, null, null },
{ null, null, null, null, null },
{ null, null, null, "Systolic", "Diastolic" },
{ "TARGET BLOOD PRESSURE", null, null, 120, 80 },
{ null, null, null, null, null },
{ null, null, null, "Systolic", "Diastolic" },
{ "CALL PHYSICIAN IF ABOVE", null, null, 140, 90 },
{ null, null, null, null, null },
{ null, null, null, null, null },
{ "PHYSICIAN PHONE NUMBER", null, null, "[Phone Number]", null }
};
worksheet.Range["B16"].Value = "CHARTED PROGRESS";
worksheet.Range["B34"].Value = "DATA ENTRY";
#endregion
#region Table
//initialize table data.
worksheet.Range["B36:H44"].Value = new object[, ] {
{ "TIME", "DATE", "AM/PM", "SYSTOLIC", "DIASTOLIC", "HEART RATE", "NOTES" },
{ new TimeSpan(10, 00, 00), DateTime.Parse("7/1/2013 12:00:00 AM"), "AM", 129, 99, 72, null },
{ new TimeSpan(18, 00, 00), DateTime.Parse("7/1/2013 12:00:00 AM"), "PM", 133, 80, 75, null },
{ new TimeSpan(10, 30, 00), DateTime.Parse("7/2/2012 12:00:00 AM"), "AM", 142, 86, 70, null },
{ new TimeSpan(19, 00, 00), DateTime.Parse("7/2/2012 12:00:00 AM"), "PM", 141, 84, 68, null },
{ new TimeSpan(09, 00, 00), DateTime.Parse("7/3/2012 12:00:00 AM"), "AM", 137, 84, 70, null },
{ new TimeSpan(18, 30, 00), DateTime.Parse("7/3/2012 12:00:00 AM"), "PM", 139, 83, 72, null },
{ new TimeSpan(10, 00, 00), DateTime.Parse("7/4/2012 12:00:00 AM"), "AM", 140, 85, 78, null },
{ new TimeSpan(18, 00, 00), DateTime.Parse("7/4/2012 12:00:00 AM"), "PM", 138, 85, 69, null },
};
ITable table = worksheet.Tables.Add(worksheet.Range["B36:H44"], true);
table.ShowTotals = true;
//set total row formulas.
table.Columns[0].Total.Value = "Average";
table.Columns[3].TotalsCalculation = TotalsCalculation.Average;
table.Columns[4].TotalsCalculation = TotalsCalculation.Average;
table.Columns[5].TotalsCalculation = TotalsCalculation.Average;
table.Columns[6].TotalsCalculation = TotalsCalculation.None;
//config data body range and total range's number format.
table.Columns[0].DataBodyRange.NumberFormat = "h:mm;@";
table.Columns[1].DataBodyRange.NumberFormat = "m/d/yyyy";
table.Columns[3].DataBodyRange.NumberFormat = "0";
table.Columns[4].DataBodyRange.NumberFormat = "0";
table.Columns[5].DataBodyRange.NumberFormat = "0";
table.Columns[3].Total.NumberFormat = "0";
table.Columns[4].Total.NumberFormat = "0";
table.Columns[5].Total.NumberFormat = "0";
//config table range's alignment.
table.Range.HorizontalAlignment = HorizontalAlignment.Left;
table.Range.IndentLevel = 0;
table.Range.VerticalAlignment = VerticalAlignment.Center;
#endregion
#region TableStyle
//create a custom table style.
ITableStyle tablestyle = workbook.TableStyles.Add("testStyle");
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Font.ThemeColor = ThemeColor.Dark1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Font.TintAndShade = 0.25;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeTop].ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeTop].TintAndShade = 0.4;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.InsideHorizontal].ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.InsideHorizontal].TintAndShade = 0.4;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeBottom].TintAndShade = 0.4;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeLeft].ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeLeft].TintAndShade = 0.4;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeRight].ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeRight].TintAndShade = 0.4;
tablestyle.TableStyleElements[TableStyleElementType.FirstRowStripe].Interior.ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.FirstRowStripe].Interior.TintAndShade = 0.8;
tablestyle.TableStyleElements[TableStyleElementType.HeaderRow].Font.Bold = true;
tablestyle.TableStyleElements[TableStyleElementType.HeaderRow].Font.ThemeColor = ThemeColor.Dark1;
tablestyle.TableStyleElements[TableStyleElementType.HeaderRow].Font.TintAndShade = 0.25;
tablestyle.TableStyleElements[TableStyleElementType.HeaderRow].Interior.ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.TotalRow].Font.Bold = true;
tablestyle.TableStyleElements[TableStyleElementType.TotalRow].Font.ThemeColor = ThemeColor.Dark1;
tablestyle.TableStyleElements[TableStyleElementType.TotalRow].Font.TintAndShade = 0.25;
tablestyle.TableStyleElements[TableStyleElementType.TotalRow].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Double;
tablestyle.TableStyleElements[TableStyleElementType.TotalRow].Borders[BordersIndex.EdgeTop].ThemeColor = ThemeColor.Accent1;
//assign custom table style for table.
table.TableStyle = workbook.TableStyles["testStyle"];
#endregion
#region Style
//assign built-in styles for ranges.
worksheet.Range["B2:H2"].Style = workbook.Styles["Heading 1"];
worksheet.Range["B4:F4, B7:D7, B10:D10, B13:D13"].Style = workbook.Styles["Heading 2"];
worksheet.Range["B16:H16, B34:H34"].Style = workbook.Styles["Heading 3"];
//modify built-in styles.
IStyle style_Heading1 = workbook.Styles["Heading 1"];
style_Heading1.HorizontalAlignment = HorizontalAlignment.General;
style_Heading1.VerticalAlignment = VerticalAlignment.Center;
style_Heading1.Font.ThemeFont = ThemeFont.Major;
style_Heading1.Font.Size = 24;
style_Heading1.Font.Bold = true;
style_Heading1.Font.ThemeColor = ThemeColor.Accent1;
style_Heading1.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thick;
style_Heading1.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Accent1;
style_Heading1.IncludeAlignment = true;
style_Heading1.IncludeFont = true;
style_Heading1.IncludeBorder = true;
style_Heading1.IncludeNumber = false;
style_Heading1.IncludePatterns = false;
style_Heading1.IncludeProtection = false;
IStyle style_Heading2 = workbook.Styles["Heading 2"];
style_Heading2.HorizontalAlignment = HorizontalAlignment.General;
style_Heading2.VerticalAlignment = VerticalAlignment.Bottom;
style_Heading2.Font.ThemeFont = ThemeFont.Minor;
style_Heading2.Font.Size = 14;
style_Heading2.Font.ThemeColor = ThemeColor.Dark1;
style_Heading2.Font.TintAndShade = 0.25;
style_Heading2.Font.Bold = false;
style_Heading2.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Dotted;
style_Heading2.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Light1;
style_Heading2.Borders[BordersIndex.EdgeBottom].TintAndShade = -0.5;
style_Heading2.IncludeAlignment = true;
style_Heading2.IncludeFont = true;
style_Heading2.IncludeBorder = true;
style_Heading2.IncludeNumber = false;
style_Heading2.IncludePatterns = false;
style_Heading2.IncludeProtection = false;
IStyle style_Heading3 = workbook.Styles["Heading 3"];
style_Heading3.HorizontalAlignment = HorizontalAlignment.General;
style_Heading3.VerticalAlignment = VerticalAlignment.Center;
style_Heading3.Font.ThemeFont = ThemeFont.Minor;
style_Heading3.Font.Size = 14;
style_Heading3.Font.Bold = true;
style_Heading3.Font.ThemeColor = ThemeColor.Dark1;
style_Heading3.Font.TintAndShade = 0.25;
style_Heading3.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thick;
style_Heading3.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Accent2;
style_Heading3.IncludeAlignment = true;
style_Heading3.IncludeFont = true;
style_Heading3.IncludeBorder = true;
style_Heading3.IncludeNumber = false;
style_Heading3.IncludePatterns = false;
style_Heading3.IncludeProtection = false;
IStyle style_Normal = workbook.Styles["Normal"];
style_Normal.NumberFormat = "General";
style_Normal.HorizontalAlignment = HorizontalAlignment.General;
style_Normal.VerticalAlignment = VerticalAlignment.Center;
style_Normal.Font.ThemeFont = ThemeFont.Minor;
style_Normal.Font.Size = 10;
style_Normal.Font.ThemeColor = ThemeColor.Dark1;
style_Normal.Font.TintAndShade = 0.25;
style_Normal.IncludeAlignment = true;
style_Normal.IncludeFont = true;
style_Normal.IncludeBorder = true;
style_Normal.IncludeNumber = true;
style_Normal.IncludePatterns = true;
style_Normal.IncludeProtection = true;
//modify cell styles.
worksheet.Range["B4"].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.None;
worksheet.Range["C4:F4"].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
IRange range1 = worksheet.Range["E7:F7, E10:F10"];
range1.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Dotted;
range1.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Light1;
range1.Borders[BordersIndex.EdgeBottom].TintAndShade = -0.5;
range1.Font.Bold = true;
IRange range2 = worksheet.Range["E7, E10"];
range2.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
range2.Borders[BordersIndex.EdgeRight].ThemeColor = ThemeColor.Light1;
range2.Borders[BordersIndex.EdgeRight].TintAndShade = -0.5;
#endregion
#region Chart
//create a new chart.
IShape shape = worksheet.Shapes.AddChart(ChartType.ColumnClustered, 8.99984251968504, 268.5, 627.750157480315, 184.5);
//create series for chart.
ISeries series_systolic = shape.Chart.SeriesCollection.NewSeries();
ISeries series_diatolic = shape.Chart.SeriesCollection.NewSeries();
ISeries series_HeartRate = shape.Chart.SeriesCollection.NewSeries();
//set series formulas.
series_systolic.Formula = "=SERIES('BLOOD PRESSURE DATA'!$E$36,'BLOOD PRESSURE DATA'!$C$37:$D$44,'BLOOD PRESSURE DATA'!$E$37:$E$44,1)";
series_diatolic.Formula = "=SERIES('BLOOD PRESSURE DATA'!$F$36,'BLOOD PRESSURE DATA'!$C$37:$D$44,'BLOOD PRESSURE DATA'!$F$37:$F$44,2)";
//set series plot on secondary axis, and change its chart type.
series_HeartRate.AxisGroup = AxisGroup.Secondary;
series_HeartRate.ChartType = ChartType.Line;
series_HeartRate.Formula = "=SERIES('BLOOD PRESSURE DATA'!$G$36,,'BLOOD PRESSURE DATA'!$G$37:$G$44,3)";
//set series fill to gradient fill.
series_systolic.Format.Fill.TwoColorGradient(GradientStyle.Horizontal, 1);
series_systolic.Format.Fill.GradientAngle = 270;
series_systolic.Format.Fill.GradientStops[0].Color.RGB = Color.FromArgb(255, 172, 175);
series_systolic.Format.Fill.GradientStops[1].Color.RGB = Color.FromArgb(255, 227, 228);
series_systolic.Format.Fill.GradientStops.Insert(0xFEC6C8, 0.35);
series_systolic.Format.Line.Color.ObjectThemeColor = ThemeColor.Accent6;
series_diatolic.Format.Fill.TwoColorGradient(GradientStyle.Horizontal, 1);
series_diatolic.Format.Fill.GradientAngle = 270;
series_diatolic.Format.Fill.GradientStops[0].Color.RGB = Color.FromArgb(255, 192, 147);
series_diatolic.Format.Fill.GradientStops[1].Color.RGB = Color.FromArgb(255, 227, 212);
series_diatolic.Format.Fill.GradientStops.Insert(0xFFCBA9, 0.35);
series_diatolic.Format.Line.Color.ObjectThemeColor = ThemeColor.Accent5;
//set series gap width and overlap.
shape.Chart.ColumnGroups[0].GapWidth = 150;
shape.Chart.ColumnGroups[0].Overlap = 0;
//set series line style.
series_HeartRate.Format.Line.BeginArrowheadLength = ArrowheadLength.Medium;
series_HeartRate.Format.Line.BeginArrowheadStyle = ArrowheadStyle.None;
series_HeartRate.Format.Line.BeginArrowheadWidth = ArrowheadWidth.Medium;
series_HeartRate.Format.Line.Color.ObjectThemeColor = ThemeColor.Accent4;
series_HeartRate.Format.Line.Color.TintAndShade = 0;
series_HeartRate.Format.Line.DashStyle = LineDashStyle.Solid;
series_HeartRate.Format.Line.EndArrowheadLength = ArrowheadLength.Medium;
series_HeartRate.Format.Line.EndArrowheadStyle = ArrowheadStyle.None;
series_HeartRate.Format.Line.EndArrowheadWidth = ArrowheadWidth.Medium;
series_HeartRate.Format.Line.Style = LineStyle.Single;
series_HeartRate.Format.Line.Weight = 1.25;
IAxis primary_axis = shape.Chart.Axes.Item(AxisType.Value, AxisGroup.Primary);
primary_axis.HasTitle = true;
primary_axis.AxisTitle.Text = "BLOOD PRESSURE";
primary_axis.AxisTitle.IncludeInLayout = true;
IAxis secondary_axis = shape.Chart.Axes.Item(AxisType.Value, AxisGroup.Secondary);
secondary_axis.HasTitle = true;
secondary_axis.AxisTitle.Text = "HEART RATE";
secondary_axis.AxisTitle.IncludeInLayout = true;
IAxis category_axis = shape.Chart.Axes.Item(AxisType.Category, AxisGroup.Primary);
category_axis.HasTitle = true;
category_axis.CategoryType = CategoryType.CategoryScale;
category_axis.Format.Line.Color.ColorType = SolidColorType.None;
shape.Chart.HasTitle = false;
//set chart font style.
shape.Chart.ChartArea.Font.Size = 9;
shape.Chart.ChartArea.Font.Color.ObjectThemeColor = ThemeColor.Dark1;
shape.Chart.ChartArea.Font.Color.Brightness = 0.5;
#endregion
#region Shape
IShape shape1 = worksheet.Shapes.AddShape(AutoShapeType.Rectangle, 402, 77.25, 234, 100);
shape1.Fill.Solid();
shape1.Fill.Color.ObjectThemeColor = ThemeColor.Accent1;
shape1.Fill.Color.Brightness = 0.6;
//set shape's border to no line.
shape1.Line.Color.ColorType = SolidColorType.None;
//set shape rich text.
ITextRange shape1_p1 = shape1.TextFrame.TextRange.Paragraphs[0];
shape1_p1.Text = "*";
shape1_p1.Runs.Add(" Blood pressures may vary dependent on many");
shape1_p1.Runs.Add(" factors. Always consult with a physician about what is normal for you. These numbers may vary slightly.");
ITextRange shape1_p2 = shape1.TextFrame.TextRange.Paragraphs.Add("");
ITextRange shape1_p3 = shape1.TextFrame.TextRange.Paragraphs.Add("Info from National Institute of Health:");
ITextRange shape1_p4 = shape1.TextFrame.TextRange.Paragraphs.Add("http://www.nhlbi.nih.gov/health/health-topics/topics/hbp/");
shape1.TextFrame.TextRange.Font.Size = 10;
shape1.TextFrame.TextRange.Font.ThemeFont = ThemeFont.Minor;
shape1.TextFrame.TextRange.Font.Color.ObjectThemeColor = ThemeColor.Dark1;
shape1.TextFrame.TextRange.Font.Color.Brightness = 0.25;
shape1_p3.Runs[0].Font.Bold = true;
IShape shape2 = worksheet.Shapes.AddShape(AutoShapeType.Rectangle, 421.5, 546.75, 198, 50);
shape2.Fill.Solid();
shape2.Fill.Color.ObjectThemeColor = ThemeColor.Accent3;
shape2.Fill.Color.Brightness = 0.6;
//set shape's border to no line.
shape2.Line.Color.ColorType = SolidColorType.None;
ITextRange shape2_p1 = shape2.TextFrame.TextRange.Paragraphs[0];
shape2_p1.Text = "NOTE:";
shape2_p1.Runs.Add(" Any blood pressure readings over the indicated numbers (\"CALL PHYSICIAN IF ABOVE\") will be");
shape2_p1.Runs.Add(" highlighted.");
shape2.TextFrame.TextRange.Font.Size = 10;
shape2.TextFrame.TextRange.Font.ThemeFont = ThemeFont.Minor;
shape2.TextFrame.TextRange.Font.Color.ObjectThemeColor = ThemeColor.Dark1;
shape2.TextFrame.TextRange.Font.Color.Brightness = 0.25;
shape2_p1.Runs[0].Font.Bold = true;
#endregion
#region DefinedName
//create defined names for workbook.
workbook.Names.Add("MaxDiastolic", "='BLOOD PRESSURE DATA'!$F$10");
workbook.Names.Add("MaxSystolic", "='BLOOD PRESSURE DATA'!$E$10");
#endregion
#region ConditionalFormat
//create conditional format for ranges.
IFormatCondition condition1 = worksheet.Range["E37:E44"].FormatConditions.Add(FormatConditionType.Expression, formula1: "=E37>MaxSystolic") as IFormatCondition;
IFormatCondition condition2 = worksheet.Range["F37:F44"].FormatConditions.Add(FormatConditionType.Expression, formula1: "=F37>MaxDiastolic") as IFormatCondition;
condition1.Interior.Color = Color.Red;
condition2.Interior.Color = Color.Red;
#endregion
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
//string l_pk = Request["master_pk"];
string TemplateFile = "rpt_bisc00070_happycook_2.xls";
string TempFile = "../../../../system/temp/rpt_bisc00070_happycook_2_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
//string para = "'" + l_pk + "'";
string p_from_date = Request.QueryString["p_from_date"];
string p_to_date = Request.QueryString["p_to_date"];
string p_wh_type = Request.QueryString["p_wh_type"];
string p_tin_warehouse_pk = Request.QueryString["p_tin_warehouse_pk"];
string p_use_yn = Request.QueryString["p_use_yn"];
string p_item_group_pk = Request.QueryString["p_item_group_pk"];
string p_item = Request.QueryString["p_item"];
string p_in_out_yn = Request.QueryString["p_in_out_yn"];
string p_lang = Request.QueryString["p_lang"];
string p_wh_name = Request.QueryString["p_wh_name"];
string para = "'" + p_from_date + "','" + p_to_date + "','" + p_wh_type + "','" + p_tin_warehouse_pk + "','" + p_use_yn + "','" + p_item_group_pk + "','" + p_item + "','" + p_in_out_yn + "','" + p_lang + "'";
DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00070", "'" + Session["User_ID"].ToString() + "'");
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00070_HAPPY_3", para);
//--set-----DataBind--in---Master---
if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString()))
{
exSheet.Cells[1, 1].Select();
byte[] MyData = new byte[0];
MyData = (byte[])dtInfo.Rows[0]["pic"];
MemoryStream stream = new MemoryStream(MyData);
Bitmap b = new Bitmap(stream);
exSheet.Shapes.AddPicture(ResizeImage(b, 100, 55));
}
exSheet.Cells["C1"].Value = dtInfo.Rows[0]["partner_name"].ToString();
exSheet.Cells["C2"].Value = dtInfo.Rows[0]["addr1"].ToString();
exSheet.Cells["C3"].Value = "Tax code: " + dtInfo.Rows[0]["tax_code"].ToString();
exSheet.Cells[1, 7].Value = "Tell: " + dtInfo.Rows[0]["phone_no"].ToString();
exSheet.Cells[2, 7].Value = "Fax: " + dtInfo.Rows[0]["fax_no"].ToString();
exSheet.Cells[3, 7].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");
exSheet.Cells["G5"].Value = System.DateTime.ParseExact(p_from_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy") + "-" + System.DateTime.ParseExact(p_to_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");
exSheet.Cells["C5"].Value = p_wh_name;
//---------
////end-set-----DataBind--in---Master---
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A10"].Rows.EntireRow.Insert();//insert row new of sheet
}
//set data to detail.
// loop detail not percent
for (int l_row = 1; l_row <= dtDetail.Rows.Count; l_row++)
{
for (int col = 1; col <= dtDetail.Columns.Count; col++)
{
if (col == 1)
{
exSheet.Cells[8 + l_row, col].Value = l_row;
}
else if (col == 6 || col == 7 || col == 8 || col == 9 || col == 10 || col == 11)
{
int no = string.IsNullOrEmpty(dtDetail.Rows[l_row - 1][col - 1].ToString()) ? 0 : Convert.ToInt32(dtDetail.Rows[l_row - 1][col - 1]);
exSheet.Cells[8 + l_row, col].Value = no;
}
else
{
exSheet.Cells[8 + l_row, col].Value = dtDetail.Rows[l_row - 1][col - 1];
}
}
}
exSheet.Cells[dtDetail.Rows.Count + 9, 6].Value = "=SUM(F9:F" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 7].Value = "=SUM(G9:G" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 8].Value = "=SUM(H9:H" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 9].Value = "=SUM(I9:I" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 10].Value = "=SUM(J9:J" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 11].Value = "=SUM(K9:K" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 12].Value = "=SUM(L9:L" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 13].Value = "=SUM(M9:M" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 14].Value = "=SUM(N9:N" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 15].Value = "=SUM(O9:O" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 16].Value = "=SUM(P9:P" + (dtDetail.Rows.Count + 8) + ")";
// end loop detail not percent
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
public static void WriteXLSLogs(ref IWorksheet ExcelSheet, string FilePath, string UserName, string Date, string Module, string Details, string UserEmailId, string ClientIpAddress)
{
try
{
int RowIndex =Convert.ToInt32(ExcelSheet.UsedRange.RowCount);
ExcelSheet.Cells[RowIndex, 0].Value = UserName; // "First sentence\r\n" + "Second sentence";
ExcelSheet.Cells[RowIndex, 1].Value = Module;
ExcelSheet.Cells[RowIndex, 2].Value = Date;
ExcelSheet.Cells[RowIndex, 3].Value = Details;
ExcelSheet.Cells[RowIndex, 4].Value = UserEmailId;
ExcelSheet.Cells[RowIndex, 5].Value = ClientIpAddress;
ExcelSheet.Cells[RowIndex, 0].Borders.Color = System.Drawing.Color.Black;
ExcelSheet.Cells[RowIndex, 1].Borders.Color = System.Drawing.Color.Black;
ExcelSheet.Cells[RowIndex, 2].Borders.Color = System.Drawing.Color.Black;
ExcelSheet.Cells[RowIndex, 3].Borders.Color = System.Drawing.Color.Black;
ExcelSheet.Cells[RowIndex, 4].Borders.Color = System.Drawing.Color.Black;
ExcelSheet.Cells[RowIndex, 5].Borders.Color = System.Drawing.Color.Black;
ExcelSheet.Name = GetFileName();
ExcelSheet.SaveAs(FilePath, SpreadsheetGear.FileFormat.Excel8);
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
}
}
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e)
{
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "FormulaSample";
if (rdBtn2003.IsChecked.Value)
{
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xls"
});
}
else
{
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xlsx",
});
}
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
if (rdBtn2003.IsChecked.Value)
{
storageFile = await local.CreateFileAsync("FormulaSample.xls", CreationCollisionOption.ReplaceExisting);
}
else
{
storageFile = await local.CreateFileAsync("FormulaSample.xlsx", CreationCollisionOption.ReplaceExisting);
}
}
if (storageFile == null)
{
return;
}
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
if (this.rdBtn2003.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#region Insert Array Formula
sheet.Range["A2"].Text = "Array formulas";
sheet.Range["B2:E2"].Number = 3;
sheet.Names.Add("ArrayRange", sheet.Range["B2:E2"]);
sheet.Range["B3:E3"].Number = 5;
sheet.Range["A2"].CellStyle.Font.Bold = true;
sheet.Range["A2"].CellStyle.Font.Size = 14;
#endregion
#region Excel functions
sheet.Range["A5"].Text = "Formula";
sheet.Range["B5"].Text = "Result";
sheet.Range["A7"].Text = "ABS(ABS(-B3))";
sheet.Range["B7"].Formula = "ABS(ABS(-B3))";
sheet.Range["A9"].Text = "SUM(B3,C3)";
sheet.Range["B9"].Formula = "SUM(B3,C3)";
sheet.Range["A11"].Text = "MIN(10,20,30,5,15,35,6,16,36)";
sheet.Range["B11"].Formula = "MIN(10,20,30,5,15,35,6,16,36)";
sheet.Range["A13"].Text = "MAX(10,20,30,5,15,35,6,16,36)";
sheet.Range["B13"].Formula = "MAX(10,20,30,5,15,35,6,16,36)";
sheet.Range["A5:B5"].CellStyle.Font.Bold = true;
sheet.Range["A5:B5"].CellStyle.Font.Size = 14;
#endregion
#region Simple formulas
sheet.Range["C7"].Number = 10;
sheet.Range["C9"].Number = 10;
sheet.Range["A15"].Text = "C7+C9";
sheet.Range["B15"].Formula = "C7+C9";
#endregion
sheet.Range["B1"].Text = "Excel formula support";
sheet.Range["B1"].CellStyle.Font.Bold = true;
sheet.Range["B1"].CellStyle.Font.Size = 14;
sheet.Range["B1:E1"].Merge();
sheet.Range["A1:A15"].AutofitColumns();
sheet.UsedRange.AutofitRows();
sheet.UsedRange.RowHeight = 19;
sheet["A1"].ColumnWidth = 29;
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
}
//For Session
//public HttpSessionStateBase Session { get; }
public ActionResult DataTable(string saveOption, string button)
{
string basePath = _hostingEnvironment.WebRootPath;
ViewBag.exportButtonState = "disabled=\"disabled\"";
///SaveOption Null
if (saveOption == null || button == null)
{
return(View());
}
//Start Data Table Functions
if (button == "Input Template")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
FileStream inputStream = new FileStream(basePath + @"/XlsIO/ExportSales.xlsx", FileMode.Open, FileAccess.Read);
// Opening the Existing Worksheet from a Workbook.
IWorkbook workbook = application.Workbooks.Open(inputStream);
try
{
string ContentType = null;
string fileName = null;
if (saveOption == "Xls")
{
workbook.Version = ExcelVersion.Excel97to2003;
ContentType = "Application/vnd.ms-excel";
fileName = "ExportSales.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "ExportSales.xlsx";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
catch (Exception)
{
}
}
else if (button == "Import From Excel")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
FileStream inputStream = new FileStream(basePath + @"/XlsIO/ExportSales.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
IWorksheet sheet = workbook.Worksheets[0];
//Export Data Table
DataTable customersTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
//Add Customer Unique ID
DataColumn column = customersTable.Columns.Add("ID");
column.SetOrdinal(0);
for (int i = 0; i < customersTable.Rows.Count; i++)
{
customersTable.Rows[i][0] = i + 1;
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
//Set the grid value to the Session
_sales = customersTable;
ViewBag.DataSource = _sales;
ViewBag.exportButtonState = "";
return(View());
}
else
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
if (saveOption == "Xls")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2016;
}
//Open an existing spreadsheet which will be used as a template for generating the new spreadsheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook;
workbook = excelEngine.Excel.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Import DataTable to worksheet
sheet.ImportDataTable(_sales, false, 5, 1);
sheet.Range["E4"].Text = "";
#region Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.RGBColor = Color.FromArgb(0, 83, 141, 213);
pageHeader.Font.FontName = "Calibri";
pageHeader.Font.Size = 18;
pageHeader.Font.Bold = true;
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Color = ExcelKnownColors.White;
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 11;
tableHeader.Font.FontName = "Calibri";
tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Color = Color.FromArgb(0, 118, 147, 60);
tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
#endregion
#region Apply Styles
// Apply style for header
sheet["A1:E1"].Merge();
sheet["A1"].Text = "Yearly Sales Report";
sheet["A1"].CellStyle = pageHeader;
sheet["A2:E2"].Merge();
sheet["A2"].Text = "Namewise Sales Comparison Report";
sheet["A2"].CellStyle = pageHeader;
sheet["A2"].CellStyle.Font.Bold = false;
sheet["A2"].CellStyle.Font.Size = 16;
sheet["A3:A4"].Merge();
sheet["B3:B4"].Merge();
sheet["E3:E4"].Merge();
sheet["C3:D3"].Merge();
sheet["C3"].Text = "Sales";
sheet["A3:E4"].CellStyle = tableHeader;
sheet["A3"].Text = "S.ID";
sheet["B3"].Text = "Sales Person";
sheet["C4"].Text = "January - June";
sheet["D4"].Text = "July - December";
sheet["E3"].Text = "Change(%)";
sheet.UsedRange.AutofitColumns();
sheet.Columns[0].ColumnWidth = 10;
sheet.Columns[1].ColumnWidth = 24;
sheet.Columns[2].ColumnWidth = 21;
sheet.Columns[3].ColumnWidth = 21;
sheet.Columns[4].ColumnWidth = 16;
#endregion
try
{
string ContentType = null;
string fileName = null;
if (saveOption == "Xls")
{
workbook.Version = ExcelVersion.Excel97to2003;
ContentType = "Application/vnd.ms-excel";
fileName = "ExportDataTable.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "ExportDataTable.xlsx";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
}
return(View());
}
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e)
{
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "ConditionalFormattings";
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xlsx",
});
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
storageFile = await local.CreateFileAsync("ConditionalFormattings.xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile == null)
{
return;
}
//Instantiate excel Engine
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
Assembly assembly = typeof(CondFormat).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.CFTemplate.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook myWorkbook = await excelEngine.Excel.Workbooks.OpenAsync(fileStream);
IWorksheet sheet = myWorkbook.Worksheets[0];
#region Databar
//Add condition for the range
IConditionalFormats formats = sheet.Range["C7:C46"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Data bar and icon set for the same cell
//Set the format type
format.FormatType = ExcelCFType.DataBar;
IDataBar dataBar = format.DataBar;
//Set the constraint
dataBar.MinPoint.Type = ConditionValueType.LowestValue;
dataBar.MinPoint.Value = "0";
dataBar.MaxPoint.Type = ConditionValueType.HighestValue;
dataBar.MaxPoint.Value = "0";
//Set color for Bar
dataBar.BarColor = Color.FromArgb(255, 156, 208, 243);
//Hide the value in data bar
dataBar.ShowValue = false;
#endregion
#region Iconset
//Add another condition in the same range
format = formats.AddCondition();
//Set Icon format type
format.FormatType = ExcelCFType.IconSet;
IIconSet iconSet = format.IconSet;
iconSet.IconSet = ExcelIconSetType.FourRating;
iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue;
iconSet.IconCriteria[0].Value = "0";
iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue;
iconSet.IconCriteria[1].Value = "0";
iconSet.ShowIconOnly = true;
//Sets Icon sets for another range
formats = sheet.Range["E7:E46"].ConditionalFormats;
format = formats.AddCondition();
format.FormatType = ExcelCFType.IconSet;
iconSet = format.IconSet;
iconSet.IconSet = ExcelIconSetType.ThreeSymbols;
iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue;
iconSet.IconCriteria[0].Value = "0";
iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue;
iconSet.IconCriteria[1].Value = "0";
iconSet.ShowIconOnly = true;
#endregion
#region Color Scale
formats = sheet.Range["D7:D46"].ConditionalFormats;
format = formats.AddCondition();
format.FormatType = ExcelCFType.ColorScale;
IColorScale colorScale = format.ColorScale;
//Sets 3 - color scale.
colorScale.SetConditionCount(3);
colorScale.Criteria[0].FormatColorRGB = Color.FromArgb(255, 230, 197, 218);
colorScale.Criteria[0].Type = ConditionValueType.LowestValue;
colorScale.Criteria[0].Value = "0";
colorScale.Criteria[1].FormatColorRGB = Color.FromArgb(255, 244, 210, 178);
colorScale.Criteria[1].Type = ConditionValueType.Percentile;
colorScale.Criteria[1].Value = "50";
colorScale.Criteria[2].FormatColorRGB = Color.FromArgb(255, 245, 247, 171);
colorScale.Criteria[2].Type = ConditionValueType.HighestValue;
colorScale.Criteria[2].Value = "0";
#endregion
await myWorkbook.SaveAsAsync(storageFile);
//Close the workbook.
myWorkbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
}
//
// GET: /DataValidation/
public ActionResult DataValidation(string SaveOption)
{
if (SaveOption == null)
{
return(View());
}
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
if (SaveOption == "Xls")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2016;
}
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(3);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Data validation to list the values in the first cell
IDataValidation validation = sheet.Range["C7"].DataValidation;
sheet.Range["B7"].Text = "Select an item from the validation list";
validation.ListOfValues = new string[] { "PDF", "XlsIO", "DocIO" };
validation.PromptBoxText = "Data Validation list";
validation.IsPromptBoxVisible = true;
validation.ShowPromptBox = true;
sheet.Range["C7"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for Numbers
IDataValidation validation1 = sheet.Range["C9"].DataValidation;
sheet.Range["B9"].Text = "Enter a Number to validate";
validation1.AllowType = ExcelDataType.Integer;
validation1.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation1.FirstFormula = "0";
validation1.SecondFormula = "10";
validation1.ShowErrorBox = true;
validation1.ErrorBoxText = "Enter Value between 0 to 10";
validation1.ErrorBoxTitle = "ERROR";
validation1.PromptBoxText = "Data Validation using Condition for Numbers";
validation1.ShowPromptBox = true;
sheet.Range["C9"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for Date
IDataValidation validation2 = sheet.Range["C11"].DataValidation;
sheet.Range["B11"].Text = "Enter the Date to validate";
validation2.AllowType = ExcelDataType.Date;
validation2.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation2.FirstDateTime = new DateTime(2003, 5, 10);
validation2.SecondDateTime = new DateTime(2004, 5, 10);
validation2.ShowErrorBox = true;
validation2.ErrorBoxText = "Enter Value between 5/10/2003 to 5/10/2004";
validation2.ErrorBoxTitle = "ERROR";
validation2.PromptBoxText = "Data Validation using Condition for Date";
validation2.ShowPromptBox = true;
sheet.Range["C11"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for TextLength
IDataValidation validation3 = sheet.Range["C13"].DataValidation;
sheet.Range["B13"].Text = "Enter the Text to validate";
validation3.AllowType = ExcelDataType.TextLength;
validation3.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation3.FirstFormula = "1";
validation3.SecondFormula = "6";
validation3.ShowErrorBox = true;
validation3.ErrorBoxText = "Retype text length to 6 character";
validation3.ErrorBoxTitle = "ERROR";
validation3.PromptBoxText = "Data Validation using Condition for TextLength";
validation3.ShowPromptBox = true;
sheet.Range["C13"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for Time
IDataValidation validation4 = sheet.Range["C15"].DataValidation;
sheet.Range["B15"].Text = "Enter the Time to validate";
validation4.AllowType = ExcelDataType.Time;
validation4.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation4.FirstFormula = "10";
validation4.SecondFormula = "12";
validation4.ShowErrorBox = true;
validation4.ErrorBoxText = "Enter the Correct time between 10 to 12 ";
validation4.ErrorBoxTitle = "ERROR";
validation4.PromptBoxText = "Data Validation using Condition for Time";
validation4.ShowPromptBox = true;
sheet.Range["C15"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
sheet.Range["B2:C2"].Merge();
sheet.Range["B2"].Text = "Simple Data validation";
sheet.Range["B5"].Text = "Validation criteria";
sheet.Range["C5"].Text = "Validation";
sheet.Range["B5"].CellStyle.Font.Bold = true;
sheet.Range["C5"].CellStyle.Font.Bold = true;
sheet.Range["B2"].CellStyle.Font.Bold = true;
sheet.Range["B2"].CellStyle.Font.Size = 16;
sheet.Range["B2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.UsedRange.AutofitColumns();
sheet.UsedRange.AutofitRows();
try
{
//Saving the workbook to disk.
if (SaveOption == "Xls")
{
return(excelEngine.SaveAsActionResult(workbook, "DataValidation.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "DataValidation.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
workbook.Close();
excelEngine.Dispose();
return(View());
}
private static void CreateXLSFileHeader(ref IWorksheet excelSheet, string FileName)
{
try
{
int RowIndex = 0;
int ColumnIndex = 0;
List<string> ListHeaderName = new List<string>();
ListHeaderName.Add("Name");
ListHeaderName.Add("Module");
ListHeaderName.Add("Date");
ListHeaderName.Add("Details");
ListHeaderName.Add("UserEmailID");
ListHeaderName.Add("IpAddress");
foreach (string StrHeaderName in ListHeaderName)
{
excelSheet.Cells[RowIndex, ColumnIndex].Value = StrHeaderName;
excelSheet.Cells[RowIndex, ColumnIndex].Borders.Color = System.Drawing.Color.Black; // Sets The Border Color For Header
excelSheet.Cells[RowIndex, ColumnIndex].Interior.Color = System.Drawing.Color.Gray; // Sets The Background Color For Header
excelSheet.Cells[RowIndex, ColumnIndex].Font.Bold = true; // Sets Header Font To Bold
excelSheet.Cells[RowIndex, ColumnIndex].Font.Color = System.Drawing.Color.White; // Sets Header Font Color To White
excelSheet.Cells[RowIndex, ColumnIndex].Font.Size = 12;
excelSheet.Cells[RowIndex, ColumnIndex].VerticalAlignment = VAlign.Top;
SpreadsheetGear.IRange Range = excelSheet.Range[RowIndex, ColumnIndex, 1, ColumnIndex + 1];
if (StrHeaderName == "Details")
{
Range.ColumnWidth = 80.00;
}
else
{
Range.ColumnWidth = 23.00;
}
ColumnIndex++;
}
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
}
}
public JsonResult AddOrder()
{
try
{
string login = HttpContext.User.Identity.Name;
int returnId = 0;
using (PortalKATEKEntities db = new PortalKATEKEntities())
{
HttpPostedFileBase[] files = new HttpPostedFileBase[Request.Files.Count];
for (int i = 0; i < files.Length; i++)
{
files[i] = Request.Files[i];
}
int[] ord = GetOrdersArray(Request.Form.ToString());
int typeMaterials = GetTypeMaterials(Request.Form.ToString());
int fileSize = files[0].ContentLength;
var fileName = Path.GetFileName(files[0].FileName);
byte[] fileByteArray = new byte[fileSize];
files[0].InputStream.Read(fileByteArray, 0, fileSize);
string fileLocation = Path.Combine(Server.MapPath("~/temp"), fileName);
if (!Directory.Exists(Server.MapPath("~/temp")))
{
Directory.CreateDirectory(Server.MapPath("~/temp"));
}
files[0].SaveAs(fileLocation);
string error = "";
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(fileLocation);
IWorksheet worksheet = workbook.Worksheets[0];
int lenght = worksheet.Rows.Length;
for (int i = 0; i < lenght; i++)
{
if (worksheet.Rows[i].Columns[1].DisplayText.Contains("PCAM") == true)
{
PlexiglassPositionsOrder pos = new PlexiglassPositionsOrder
{
id_PlexiglassOrder = 0,
positionNum = "",
designation = worksheet.Rows[i].Cells[1].Value.Trim(),
name = worksheet.Rows[i].Cells[2].Value.Trim(),
index = worksheet.Rows[i].Cells[3].Value.Trim(),
quentity = 0,
square = 0.0,
barcode = ""
};
bool correct = GetFileMaterials(pos.designation);
if (correct == false)
{
error += pos.designation + " - файл не найден " + "\n";
}
}
}
workbook.Close();
}
if (error != "")
{
return(Json(error, JsonRequestBehavior.AllowGet));
}
var order = new PlexiglassOrder
{
id_CMO_TypeProduct = typeMaterials,
id_AspNetUsers = db.AspNetUsers.First(a => a.Email == login).Id,
datetimeCreate = DateTime.Now,
folder = "",
remove = false,
note = "",
id_PlexiglassCompany = null
};
db.PlexiglassOrder.Add(order);
db.SaveChanges();
order.folder = CreateFolderAndFileForPreOrder(order.id, files);
CreatingPositionsPreorder(order.id, order.folder);
db.Entry(order).State = EntityState.Modified;
db.SaveChanges();
foreach (var p in ord)
{
PlexiglassOrdersPlans po = new PlexiglassOrdersPlans
{
PZ_PlanZakazId = p,
PlexiglassOrderId = order.id
};
db.PlexiglassOrdersPlans.Add(po);
db.SaveChanges();
}
new EmailPlexiglas(order, login, 0);
return(Json(returnId, JsonRequestBehavior.AllowGet));
}
}
catch (Exception ex)
{
logger.Debug("PlexiglasController/AddOrder: " + ex.Message);
return(Json(0, JsonRequestBehavior.AllowGet));
}
}
protected IList<ValoresFuente> LlenarTabla(IWorksheet sheet, int row, FichaIndicadores ficha)
{
var lista = new List<ValoresFuente>();
var total = sheet.NumRows;
var listaVars = ficha.Variables.Select(x => x.Value).OrderBy(x => x.Orden);
for (int i = row; i <= total; i++) {
var codigo = sheet.GetString("B", i);
if (string.IsNullOrEmpty(codigo))
break;
// IMPORTANTE
var f = new ValoresFuente { Tipo = ficha.Nombre, IdItem = total++, Codigo1 = codigo };
var nombre = sheet.GetString("C", i);
f.Codigo2 = string.IsNullOrEmpty(nombre) ? codigo : nombre;
var off = 3;
foreach (var v in listaVars) {
off++;
f[v.Codigo] = sheet.GetString(off, i);
}
lista.Add(f);
}
return lista;
}
private void CreatingPositionsPreorder(int orderId, string path)
{
List <string> fiels = GetFileArray(path);
using (PortalKATEKEntities db = new PortalKATEKEntities())
{
foreach (var fiel in fiels.Where(a => a.Contains("~") == false))
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(fiel);
IWorksheet worksheet = workbook.Worksheets[0];
int lenght = worksheet.Rows.Length;
for (int i = 0; i < lenght; i++)
{
if (worksheet.Rows[i].Columns[1].DisplayText.Contains("PCAM") == true)
{
PlexiglassPositionsOrder pos = new PlexiglassPositionsOrder
{
id_PlexiglassOrder = orderId,
positionNum = worksheet.Rows[i].Cells[0].Value,
designation = worksheet.Rows[i].Cells[1].Value.Trim(),
name = worksheet.Rows[i].Cells[2].Value.Trim(),
index = worksheet.Rows[i].Cells[3].Value.Trim(),
quentity = (int)worksheet.Rows[i].Cells[4].Number,
square = 0.0,
barcode = GetBarcode(worksheet.Rows[i].Cells[1].Value, worksheet.Rows[i].Cells[3].Value)
};
if (double.IsNaN(worksheet.Rows[i].Cells[5].Number))
{
try
{
pos.square = Convert.ToDouble(worksheet.Rows[i].Cells[5].DisplayText);
}
catch
{
try
{
pos.square = Convert.ToDouble(worksheet.Rows[i].Cells[5].DisplayText.Replace(".", ","));
}
catch
{
}
}
}
else
{
pos.square = worksheet.Rows[i].Cells[5].Number;
}
db.PlexiglassPositionsOrder.Add(pos);
db.SaveChanges();
GetFileMaterials(pos.designation, path);
worksheet.Rows[i].Cells[9].Value = pos.barcode;
worksheet.Rows[i].Cells[9].Text = pos.barcode;
}
}
workbook.Version = ExcelVersion.Excel2013;
workbook.Save();
workbook.Close();
}
}
}
}
static List<Str_DataTable_Section?> CreateExcelDocument_GetDataTables_Headers(IWorksheet Ws_Parameters)
{
return CreateExcelDocument_GetSections(Ws_Parameters, CnsExcelKeyword_DataTable_Header, CnsExcelKeyword_DataTable_Header_End);
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string TemplateFile = "rpt_hrti01300_1.xls";
string TempFile = "../../../../system/temp/rpt_hrti01300_1_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
MemoryStream stream;
Bitmap b;
string p_tco_dept_pk, p_thr_group_pk, p_status, p_emp_id, p_work_mon, p_from_dt, p_to_dt, p_dept_nm, l_work_from, p_nation, p_salary_period, p_lstEmpID;
p_tco_dept_pk = Request["l_tco_dept_pk"].ToString();
p_thr_group_pk = Request["l_thr_group_pk"].ToString();
p_status = Request["l_status"].ToString();
p_emp_id = Request["l_emp_id"].ToString();
p_work_mon = Request["l_work_mon"].ToString();
p_to_dt = Request["l_to_dt"].ToString();
p_from_dt = Request["l_from_dt"].ToString();
p_dept_nm = Request["l_dept_nm"].ToString();
p_nation = Request["l_nation"].ToString();
p_salary_period = Request["l_SalaryPeriod"].ToString();
p_lstEmpID = Request["l_lstEmpID"].ToString();
string l_Emp_id = "";
if (p_lstEmpID != "")
{
l_Emp_id = " and b.emp_id in ('" + p_lstEmpID + "')";
}
else
{
p_emp_id = p_emp_id.ToUpper();
l_Emp_id = " and b.emp_id like '%" + p_emp_id + "%'";
}
//-----------------information of company-----------------
string SQL_Com
= "select a.PARTNER_LNAME,a.ADDR1,a.PHONE_NO,b.data,to_char(to_date('" + p_work_mon + "','yyyymm'),'mm/yyyy') " +
"from tco_company a, tco_bpphoto b " +
"where a.DEL_IF=0 and b.del_if(+) = 0 and a.TCO_BPPHOTO_PK = b.pk(+)" +
"and a.pk in ( select tco_company_pk from " +
" tco_org f " +
" where f.pk IN ( " +
" SELECT g.pk " +
" FROM tco_org g " +
" WHERE g.del_if = 0 " +
" START WITH g.pk = " +
" DECODE ('" + p_tco_dept_pk + "', " +
" 'ALL', 0, " +
" '" + p_tco_dept_pk + "' " +
" ) " +
" CONNECT BY PRIOR g.pk = g.p_pk) " +
" OR '" + p_tco_dept_pk + "' = 'ALL') " +
"and rownum=1 ";
//Response.Write(SQL_Com);
//Response.End();
DataTable dt_Com = CtlLib.TableReadOpen(SQL_Com);
exSheet.Range["C1"].Value = dt_Com.Rows[0][0].ToString();
exSheet.Range["C2"].Value = dt_Com.Rows[0][1].ToString();
exSheet.Range["C3"].Value = dt_Com.Rows[0][2].ToString();
if (!string.IsNullOrEmpty(dt_Com.Rows[0][3].ToString()))
{
byte[] MyData = new byte[0];
MyData = (byte[])dt_Com.Rows[0][3];
stream = new MemoryStream(MyData);
b = new Bitmap(stream);
exSheet.Cells["A1"].Select();
exSheet.Shapes.AddPicture(CtlLib.ResizeImage(b, 130, 60));
}
//-----------------working time-----------------
string SQL = "select c.org_NM,b.emp_id,b.full_name " +
",to_char(to_date(b.join_dt,'yyyymmdd'),'dd/mm/yyyy') join_dt " +
",MAX(DECODE(A.WORK_DT,'" + p_from_dt + "',a.TIME_IN,'')) as in_1 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+1,'YYYYMMDD'),a.TIME_IN,'')) as in_2 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+2,'YYYYMMDD'),a.TIME_IN,'')) as in_3 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+3,'YYYYMMDD'),a.TIME_IN,'')) as in_4 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+4,'YYYYMMDD'),a.TIME_IN,'')) as in_5 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+5,'YYYYMMDD'),a.TIME_IN,'')) as in_6 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+6,'YYYYMMDD'),a.TIME_IN,'')) as in_7 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+7,'YYYYMMDD'),a.TIME_IN,'')) as in_8 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+8,'YYYYMMDD'),a.TIME_IN,'')) as in_9 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+9,'YYYYMMDD'),a.TIME_IN,'')) as in_10 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+10,'YYYYMMDD'),a.TIME_IN,'')) as in_11 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+11,'YYYYMMDD'),a.TIME_IN,'')) as in_12 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+12,'YYYYMMDD'),a.TIME_IN,'')) as in_13 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+13,'YYYYMMDD'),a.TIME_IN,'')) as in_14 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+14,'YYYYMMDD'),a.TIME_IN,'')) as in_15 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+15,'YYYYMMDD'),a.TIME_IN,'')) as in_16 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+16,'YYYYMMDD'),a.TIME_IN,'')) as in_17 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+17,'YYYYMMDD'),a.TIME_IN,'')) as in_18 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+18,'YYYYMMDD'),a.TIME_IN,'')) as in_19 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+19,'YYYYMMDD'),a.TIME_IN,'')) as in_20 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+20,'YYYYMMDD'),a.TIME_IN,'')) as in_21 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+21,'YYYYMMDD'),a.TIME_IN,'')) as in_22 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+22,'YYYYMMDD'),a.TIME_IN,'')) as in_23 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+23,'YYYYMMDD'),a.TIME_IN,'')) as in_24 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+24,'YYYYMMDD'),a.TIME_IN,'')) as in_25 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+25,'YYYYMMDD'),a.TIME_IN,'')) as in_26 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+26,'YYYYMMDD'),a.TIME_IN,'')) as in_27 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+27,'YYYYMMDD'),a.TIME_IN,'')) as in_28 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+28,'YYYYMMDD'),a.TIME_IN,'')) as in_29 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+29,'YYYYMMDD'),a.TIME_IN,'')) as in_30 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+30,'YYYYMMDD'),a.TIME_IN,'')) as in_31 " +
",MAX(DECODE(A.WORK_DT,'" + p_from_dt + "',a.TIME_OUT,'')) as out_1 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+1,'YYYYMMDD'),a.TIME_OUT,'')) as out_2 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+2,'YYYYMMDD'),a.TIME_OUT,'')) as out_3 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+3,'YYYYMMDD'),a.TIME_OUT,'')) as out_4 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+4,'YYYYMMDD'),a.TIME_OUT,'')) as out_5 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+5,'YYYYMMDD'),a.TIME_OUT,'')) as out_6 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+6,'YYYYMMDD'),a.TIME_OUT,'')) as out_7 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+7,'YYYYMMDD'),a.TIME_OUT,'')) as out_8 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+8,'YYYYMMDD'),a.TIME_OUT,'')) as out_9 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+9,'YYYYMMDD'),a.TIME_OUT,'')) as out_10 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+10,'YYYYMMDD'),a.TIME_OUT,'')) as out_11 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+11,'YYYYMMDD'),a.TIME_OUT,'')) as out_12 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+12,'YYYYMMDD'),a.TIME_OUT,'')) as out_13 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+13,'YYYYMMDD'),a.TIME_OUT,'')) as out_14 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+14,'YYYYMMDD'),a.TIME_OUT,'')) as out_15 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+15,'YYYYMMDD'),a.TIME_OUT,'')) as out_16 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+16,'YYYYMMDD'),a.TIME_OUT,'')) as out_17 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+17,'YYYYMMDD'),a.TIME_OUT,'')) as out_18 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+18,'YYYYMMDD'),a.TIME_OUT,'')) as out_19 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+19,'YYYYMMDD'),a.TIME_OUT,'')) as out_20 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+20,'YYYYMMDD'),a.TIME_OUT,'')) as out_21 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+21,'YYYYMMDD'),a.TIME_OUT,'')) as out_22 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+22,'YYYYMMDD'),a.TIME_OUT,'')) as out_23 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+23,'YYYYMMDD'),a.TIME_OUT,'')) as out_24 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+24,'YYYYMMDD'),a.TIME_OUT,'')) as out_25 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+25,'YYYYMMDD'),a.TIME_OUT,'')) as out_26 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+26,'YYYYMMDD'),a.TIME_OUT,'')) as out_27 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+27,'YYYYMMDD'),a.TIME_OUT,'')) as out_28 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+28,'YYYYMMDD'),a.TIME_OUT,'')) as out_29 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+29,'YYYYMMDD'),a.TIME_OUT,'')) as out_30 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + p_from_dt + "','YYYYMMDD')+30,'YYYYMMDD'),a.TIME_OUT,'')) as out_31 " +
", NVL (c.seq, 0) seq,SUM(decode(a.hol_type, null, NVL(A.WT,0), 0)) WORK_TIME " +
", sum(decode(a.hol_type, null, a.ot, 0)) OT " +
", (SUM(NVL(A.NT, 0)) + SUM(NVL(A.ONT, 0))) NT " +
", sum(decode(a.hol_type, null, 0, NVL(a.WT, 0) + NVL(a.ot, 0) )) HT " +
" from thr_work_date a,thr_employee b,tco_org c " +
"where a.del_if=0 and b.del_if=0 and c.del_if=0 " +
"and a.THR_EMP_PK=b.pk and b.tco_org_pk=c.pk " +
"and a.WORK_DT between '" + p_from_dt + "' and '" + p_to_dt + "' " +
"and a.WORK_DT between b.join_dt and nvl(b.left_dt,'" + p_to_dt + "') " +
"and b.join_dt<=a.WORK_DT " +
"and (b.left_dt>a.WORK_DT or b.left_dt is null) " +
"and (b.tco_org_pk in ( " +
" SELECT g.pk " +
" FROM tco_org g " +
" WHERE g.del_if = 0 " +
" START WITH g.pk = " +
" DECODE ('" + p_tco_dept_pk + "', " +
" 'ALL', 0, " +
" '" + p_tco_dept_pk + "' " +
" ) " +
" CONNECT BY PRIOR g.pk = g.p_pk) " +
" OR '" + p_tco_dept_pk + "' = 'ALL') " +
"and decode('" + p_thr_group_pk + "','ALL','ALL',b.thr_wg_pk)='" + p_thr_group_pk + "'" +
l_Emp_id +
"and decode('" + p_status + "','ALL','ALL',b.status)='" + p_status + "'" +
" and decode('" + p_salary_period + "','ALL','ALL',nvl(b.sal_period,' '))='" + p_salary_period + "' " +
" and decode('" + p_nation + "','ALL','ALL',nvl(b.nation,'01'))='" + p_nation + "' " +
" GROUP BY A.THR_EMP_PK,c.org_NM,b.emp_id,b.full_name,b.join_dt,NVL (c.seq, 0) " +
" ORDER BY NVL (c.seq, 0),c.org_nm, b.emp_id ";
string SQL_work_from = " select m.from_dt " +
"from thr_close m " +
"where m.del_if=0 and m.id='01' and m.month_close='" + p_work_mon + "' ";
DataTable dt_from = CtlLib.TableReadOpen(SQL_work_from);
l_work_from = dt_from.Rows[0][0].ToString();
string SQL1 = "select c.org_NM,b.emp_id,b.full_name " +
",to_char(to_date(b.join_dt,'yyyymmdd'),'dd/mm/yyyy') join_dt " +
",MAX(DECODE(A.WORK_DT,'" + l_work_from + "',a.TIME_IN,'')) as in_1 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+1,'YYYYMMDD'),a.TIME_IN,'')) as in_2 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+2,'YYYYMMDD'),a.TIME_IN,'')) as in_3 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+3,'YYYYMMDD'),a.TIME_IN,'')) as in_4 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+4,'YYYYMMDD'),a.TIME_IN,'')) as in_5 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+5,'YYYYMMDD'),a.TIME_IN,'')) as in_6 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+6,'YYYYMMDD'),a.TIME_IN,'')) as in_7 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+7,'YYYYMMDD'),a.TIME_IN,'')) as in_8 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+8,'YYYYMMDD'),a.TIME_IN,'')) as in_9 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+9,'YYYYMMDD'),a.TIME_IN,'')) as in_10 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+10,'YYYYMMDD'),a.TIME_IN,'')) as in_11 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+11,'YYYYMMDD'),a.TIME_IN,'')) as in_12 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+12,'YYYYMMDD'),a.TIME_IN,'')) as in_13 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+13,'YYYYMMDD'),a.TIME_IN,'')) as in_14 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+14,'YYYYMMDD'),a.TIME_IN,'')) as in_15 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+15,'YYYYMMDD'),a.TIME_IN,'')) as in_16 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+16,'YYYYMMDD'),a.TIME_IN,'')) as in_17 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+17,'YYYYMMDD'),a.TIME_IN,'')) as in_18 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+18,'YYYYMMDD'),a.TIME_IN,'')) as in_19 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+19,'YYYYMMDD'),a.TIME_IN,'')) as in_20 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+20,'YYYYMMDD'),a.TIME_IN,'')) as in_21 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+21,'YYYYMMDD'),a.TIME_IN,'')) as in_22 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+22,'YYYYMMDD'),a.TIME_IN,'')) as in_23 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+23,'YYYYMMDD'),a.TIME_IN,'')) as in_24 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+24,'YYYYMMDD'),a.TIME_IN,'')) as in_25 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+25,'YYYYMMDD'),a.TIME_IN,'')) as in_26 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+26,'YYYYMMDD'),a.TIME_IN,'')) as in_27 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+27,'YYYYMMDD'),a.TIME_IN,'')) as in_28 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+28,'YYYYMMDD'),a.TIME_IN,'')) as in_29 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+29,'YYYYMMDD'),a.TIME_IN,'')) as in_30 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+30,'YYYYMMDD'),a.TIME_IN,'')) as in_31 " +
",MAX(DECODE(A.WORK_DT,'" + l_work_from + "',a.TIME_OUT,'')) as out_1 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+1,'YYYYMMDD'),a.TIME_OUT,'')) as out_2 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+2,'YYYYMMDD'),a.TIME_OUT,'')) as out_3 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+3,'YYYYMMDD'),a.TIME_OUT,'')) as out_4 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+4,'YYYYMMDD'),a.TIME_OUT,'')) as out_5 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+5,'YYYYMMDD'),a.TIME_OUT,'')) as out_6 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+6,'YYYYMMDD'),a.TIME_OUT,'')) as out_7 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+7,'YYYYMMDD'),a.TIME_OUT,'')) as out_8 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+8,'YYYYMMDD'),a.TIME_OUT,'')) as out_9 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+9,'YYYYMMDD'),a.TIME_OUT,'')) as out_10 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+10,'YYYYMMDD'),a.TIME_OUT,'')) as out_11 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+11,'YYYYMMDD'),a.TIME_OUT,'')) as out_12 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+12,'YYYYMMDD'),a.TIME_OUT,'')) as out_13 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+13,'YYYYMMDD'),a.TIME_OUT,'')) as out_14 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+14,'YYYYMMDD'),a.TIME_OUT,'')) as out_15 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+15,'YYYYMMDD'),a.TIME_OUT,'')) as out_16 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+16,'YYYYMMDD'),a.TIME_OUT,'')) as out_17 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+17,'YYYYMMDD'),a.TIME_OUT,'')) as out_18 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+18,'YYYYMMDD'),a.TIME_OUT,'')) as out_19 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+19,'YYYYMMDD'),a.TIME_OUT,'')) as out_20 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+20,'YYYYMMDD'),a.TIME_OUT,'')) as out_21 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+21,'YYYYMMDD'),a.TIME_OUT,'')) as out_22 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+22,'YYYYMMDD'),a.TIME_OUT,'')) as out_23 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+23,'YYYYMMDD'),a.TIME_OUT,'')) as out_24 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+24,'YYYYMMDD'),a.TIME_OUT,'')) as out_25 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+25,'YYYYMMDD'),a.TIME_OUT,'')) as out_26 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+26,'YYYYMMDD'),a.TIME_OUT,'')) as out_27 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+27,'YYYYMMDD'),a.TIME_OUT,'')) as out_28 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+28,'YYYYMMDD'),a.TIME_OUT,'')) as out_29 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+29,'YYYYMMDD'),a.TIME_OUT,'')) as out_30 " +
",MAX(DECODE(A.WORK_DT,TO_CHAR(TO_DATE('" + l_work_from + "','YYYYMMDD')+30,'YYYYMMDD'),a.TIME_OUT,'')) as out_31 " +
", NVL (c.seq, 0) seq,SUM(decode(a.hol_type, null, NVL(A.WT,0), 0)) WORK_TIME " +
", sum(decode(a.hol_type, null, a.ot, 0)) OT " +
", (SUM(NVL(A.NT, 0)) + SUM(NVL(A.ONT, 0))) NT " +
", sum(decode(a.hol_type, null, 0, NVL(a.WT, 0) + NVL(a.ot, 0) )) HT " +
" from thr_work_date a,thr_employee b,tco_org c " +
"where a.del_if=0 and b.del_if=0 and c.del_if=0 " +
"and a.THR_EMP_PK=b.pk and b.tco_org_pk=c.pk " +
"and a.WORK_DT between '" + l_work_from + "' and '" + p_to_dt + "' " +
"and a.WORK_DT between b.join_dt and nvl(b.left_dt,'" + p_to_dt + "') " +
"and b.join_dt<=a.WORK_DT " +
" AND b.left_dt BETWEEN '" + p_from_dt + "' and '" + p_to_dt + "' " +
"and b.left_dt is not null " +
"and (b.tco_org_pk in ( " +
" SELECT g.pk " +
" FROM tco_org g " +
" WHERE g.del_if = 0 " +
" START WITH g.pk = " +
" DECODE ('" + p_tco_dept_pk + "', " +
" 'ALL', 0, " +
" '" + p_tco_dept_pk + "' " +
" ) " +
" CONNECT BY PRIOR g.pk = g.p_pk) " +
" OR '" + p_tco_dept_pk + "' = 'ALL') " +
"and decode('" + p_thr_group_pk + "','ALL','ALL',b.thr_wg_pk)='" + p_thr_group_pk + "'" +
l_Emp_id +
"and decode('" + p_status + "','ALL','ALL',b.status)='" + p_status + "'" +
" and decode('" + p_nation + "','ALL','ALL',nvl(b.nation,'01'))='" + p_nation + "' " +
" GROUP BY A.THR_EMP_PK,c.org_NM,b.emp_id,b.full_name,b.join_dt,NVL (c.seq, 0) " +
" ORDER BY NVL (c.seq, 0),c.org_nm, b.emp_id ";
if (p_status == "R")
{
SQL = SQL1;
}
//Response.Write(SQL);
//Response.End();
DataTable dt_Emp = CtlLib.TableReadOpen(SQL);
string SQL_Cal = "select substr(t.car_date,-2),t.hol_type " +
"from thr_calendar T,thr_close m " +
"where t.del_if=0 and m.del_if=0 and m.id='" + p_salary_period + "' and m.month_close='" + p_work_mon + "' " +
" and t.tco_company_pk=(select tco_company_pk from tco_org g where g.del_if=0 and g.pk='" + p_tco_dept_pk + "' ) " +
" and t.car_date between m.from_dt and '" + p_to_dt + "' order by t.car_date ";
//Response.Write(SQL_Cal);
//Response.End();
DataTable dt_Cal = CtlLib.TableReadOpen(SQL_Cal);
int row_emp = dt_Emp.Rows.Count;
int row_cal = dt_Cal.Rows.Count;
int s_col = 7;
int s_row = 6;
//exSheet.Range["T2"].Value = string.Format("IN - OUT SUMMARY - {0}", p_dept_nm);
exSheet.Range["T3:U3"].Value = dt_Com.Rows[0][4].ToString();
//header columns
for (int row = 0; row < row_cal; row++)
{
exSheet.Range[4, s_col + row, 5, s_col + row].Value = double.Parse(dt_Cal.Rows[row][0].ToString());
}
s_row = 6;
//insert new row
for (int row = 1; row < row_emp; row++)
{
exSheet.Range["A8:AT9"].EntireRow.Insert();
}
exSheet.Range["A6:AT7"].Rows.Copy(exSheet.Range["A" + (s_row + 2) + ":" + "AT" + (((row_emp - 1) * 2) + s_row - 1 + 2)], XlPasteType.xlPasteAll);
for (int row = 0; row < row_emp; row++)
{
exSheet.Range["A" + (s_row + row) + ":A" + (s_row + row + 1)].Value = row + 1;
exSheet.Range["B" + (s_row + row) + ":B" + (s_row + row + 1)].Value = dt_Emp.Rows[row]["ORG_NM"].ToString();
exSheet.Range["C" + (s_row + row) + ":C" + (s_row + row + 1)].Value = dt_Emp.Rows[row]["EMP_ID"].ToString();
exSheet.Range["D" + (s_row + row) + ":D" + (s_row + row + 1)].Value = dt_Emp.Rows[row]["FULL_NAME"].ToString();
exSheet.Range["E" + (s_row + row) + ":E" + (s_row + row + 1)].Value = dt_Emp.Rows[row]["JOIN_DT"].ToString();
GeneralData(exSheet, "1", "G", s_row + row, row, "IN_1", dt_Emp);
GeneralData(exSheet, "1", "G", s_row + row + 1, row, "OUT_1", dt_Emp);
GeneralData(exSheet, "1", "H", s_row + row, row, "IN_2", dt_Emp);
GeneralData(exSheet, "1", "H", s_row + row + 1, row, "OUT_2", dt_Emp);
GeneralData(exSheet, "1", "I", s_row + row, row, "IN_3", dt_Emp);
GeneralData(exSheet, "1", "I", s_row + row + 1, row, "OUT_3", dt_Emp);
GeneralData(exSheet, "1", "J", s_row + row, row, "IN_4", dt_Emp);
GeneralData(exSheet, "1", "J", s_row + row + 1, row, "OUT_4", dt_Emp);
GeneralData(exSheet, "1", "K", s_row + row, row, "IN_5", dt_Emp);
GeneralData(exSheet, "1", "K", s_row + row + 1, row, "OUT_5", dt_Emp);
GeneralData(exSheet, "1", "L", s_row + row, row, "IN_6", dt_Emp);
GeneralData(exSheet, "1", "L", s_row + row + 1, row, "OUT_6", dt_Emp);
GeneralData(exSheet, "1", "M", s_row + row, row, "IN_7", dt_Emp);
GeneralData(exSheet, "1", "M", s_row + row + 1, row, "OUT_7", dt_Emp);
GeneralData(exSheet, "1", "N", s_row + row, row, "IN_8", dt_Emp);
GeneralData(exSheet, "1", "N", s_row + row + 1, row, "OUT_8", dt_Emp);
GeneralData(exSheet, "1", "O", s_row + row, row, "IN_9", dt_Emp);
GeneralData(exSheet, "1", "O", s_row + row + 1, row, "OUT_9", dt_Emp);
GeneralData(exSheet, "1", "P", s_row + row, row, "IN_10", dt_Emp);
GeneralData(exSheet, "1", "P", s_row + row + 1, row, "OUT_10", dt_Emp);
GeneralData(exSheet, "1", "Q", s_row + row, row, "IN_11", dt_Emp);
GeneralData(exSheet, "1", "Q", s_row + row + 1, row, "OUT_11", dt_Emp);
GeneralData(exSheet, "1", "R", s_row + row, row, "IN_12", dt_Emp);
GeneralData(exSheet, "1", "R", s_row + row + 1, row, "OUT_12", dt_Emp);
GeneralData(exSheet, "1", "S", s_row + row, row, "IN_13", dt_Emp);
GeneralData(exSheet, "1", "S", s_row + row + 1, row, "OUT_13", dt_Emp);
GeneralData(exSheet, "1", "T", s_row + row, row, "IN_14", dt_Emp);
GeneralData(exSheet, "1", "T", s_row + row + 1, row, "OUT_14", dt_Emp);
GeneralData(exSheet, "1", "U", s_row + row, row, "IN_15", dt_Emp);
GeneralData(exSheet, "1", "U", s_row + row + 1, row, "OUT_15", dt_Emp);
GeneralData(exSheet, "1", "V", s_row + row, row, "IN_16", dt_Emp);
GeneralData(exSheet, "1", "V", s_row + row + 1, row, "OUT_16", dt_Emp);
GeneralData(exSheet, "1", "W", s_row + row, row, "IN_17", dt_Emp);
GeneralData(exSheet, "1", "W", s_row + row + 1, row, "OUT_17", dt_Emp);
GeneralData(exSheet, "1", "X", s_row + row, row, "IN_18", dt_Emp);
GeneralData(exSheet, "1", "X", s_row + row + 1, row, "OUT_18", dt_Emp);
GeneralData(exSheet, "1", "Y", s_row + row, row, "IN_19", dt_Emp);
GeneralData(exSheet, "1", "Y", s_row + row + 1, row, "OUT_19", dt_Emp);
GeneralData(exSheet, "1", "Z", s_row + row, row, "IN_20", dt_Emp);
GeneralData(exSheet, "1", "Z", s_row + row + 1, row, "OUT_20", dt_Emp);
GeneralData(exSheet, "1", "AA", s_row + row, row, "IN_21", dt_Emp);
GeneralData(exSheet, "1", "AA", s_row + row + 1, row, "OUT_21", dt_Emp);
GeneralData(exSheet, "1", "AB", s_row + row, row, "IN_22", dt_Emp);
GeneralData(exSheet, "1", "AB", s_row + row + 1, row, "OUT_22", dt_Emp);
GeneralData(exSheet, "1", "AC", s_row + row, row, "IN_23", dt_Emp);
GeneralData(exSheet, "1", "AC", s_row + row + 1, row, "OUT_23", dt_Emp);
GeneralData(exSheet, "1", "AD", s_row + row, row, "IN_24", dt_Emp);
GeneralData(exSheet, "1", "AD", s_row + row + 1, row, "OUT_24", dt_Emp);
GeneralData(exSheet, "1", "AE", s_row + row, row, "IN_25", dt_Emp);
GeneralData(exSheet, "1", "AE", s_row + row + 1, row, "OUT_25", dt_Emp);
GeneralData(exSheet, "1", "AF", s_row + row, row, "IN_26", dt_Emp);
GeneralData(exSheet, "1", "AF", s_row + row + 1, row, "OUT_26", dt_Emp);
GeneralData(exSheet, "1", "AG", s_row + row, row, "IN_27", dt_Emp);
GeneralData(exSheet, "1", "AG", s_row + row + 1, row, "OUT_27", dt_Emp);
GeneralData(exSheet, "1", "AH", s_row + row, row, "IN_28", dt_Emp);
GeneralData(exSheet, "1", "AH", s_row + row + 1, row, "OUT_28", dt_Emp);
GeneralData(exSheet, "1", "AI", s_row + row, row, "IN_29", dt_Emp);
GeneralData(exSheet, "1", "AI", s_row + row + 1, row, "OUT_29", dt_Emp);
GeneralData(exSheet, "1", "AJ", s_row + row, row, "IN_30", dt_Emp);
GeneralData(exSheet, "1", "AJ", s_row + row + 1, row, "OUT_30", dt_Emp);
GeneralData(exSheet, "1", "AK", s_row + row, row, "IN_31", dt_Emp);
GeneralData(exSheet, "1", "AK", s_row + row + 1, row, "OUT_31", dt_Emp);
GeneralData(exSheet, "2", "AL", s_row + row, row, "WORK_TIME", dt_Emp);
GeneralData(exSheet, "2", "AM", s_row + row, row, "OT", dt_Emp);
GeneralData(exSheet, "2", "AN", s_row + row, row, "NT", dt_Emp);
GeneralData(exSheet, "2", "AO", s_row + row, row, "HT", dt_Emp);
s_row++;
}
// draw color
s_row = 6;
int temp = 0;
temp = 31 - row_cal;
switch (temp)
{
case 3:
exSheet.Range["AI1"].Columns.Hidden = true;
break;
case 2:
exSheet.Range["AJ1"].Columns.Hidden = true;
break;
case 1:
exSheet.Range["AK1"].Columns.Hidden = true;
break;
}
for (int row = 0; row < row_cal; row++)
{
switch (dt_Cal.Rows[row][1].ToString())
{
case "SUN":
exSheet.Range[s_row, row + s_col, ((row_emp * 2) + s_row - 1), row + s_col].Interior.Color = Color.SkyBlue;
break;
case "HOL":
exSheet.Range[s_row, row + s_col, ((row_emp * 2) + s_row - 1), row + s_col].Interior.Color = Color.Pink;
break;
}
}
exBook.SaveAs(TempFile);
//CtlLib.ExcelToPdf(TempFile);
string pdfFilePath = TempFile.Replace(".xls", ".pdf");
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
Response.WriteFile(TempFile);
//Response.WriteFile(pdfFilePath);
//Stop execute
Response.End();
}
static List<Str_DataTable_Section?> CreateExcelDocument_GetDataTables_PivotTables_Totals(IWorksheet Ws_Parameters)
{
return CreateExcelDocument_GetSections(Ws_Parameters, CnsExcelKeyword_DataTable_Pivot_Totals, CnsExcelKeyword_DataTable_Pivot_Totals_End);
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string p_tco_org_pk = Request["l_tco_org_pk"];
string p_work_mon = Request["l_work_mon"];
string p_emp_id = Request["l_emp_id"];
string p_from_dt = Request["dt_from"];
string p_to_dt = Request["dt_to"];
string p_nation = Request["l_nation"];
string p_sal_period = Request["l_sal_period"];
string TemplateFile = "rpt_hrti01700_0.xls";
string TempFile = "../../../../system/temp/rpt_hrti01700_0_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
string para = "'" + p_emp_id + "','" + p_work_mon + "','" + p_tco_org_pk + "','" + p_nation + "','" + p_sal_period + "'";
DataTable dt_detail = CtlLib.TableReadOpenCursor("ST_HR_RPT_HRTI01700_0", para);
string sql_day
= "select substr(c.car_date,-2), c.hol_type from thr_calendar c " +
"where c.del_if=0 " +
"and c.car_date between '" + p_from_dt + "' and '" + p_to_dt + "' " +
"and c.tco_company_pk = (select g.tco_company_pk " +
" from tco_org g " +
" where g.del_if = 0 and g.pk = '" + p_tco_org_pk + "') " +
" order by c.car_date ";
DataTable dt_day = CtlLib.TableReadOpen(sql_day);
string sql_date
= "select " +
" to_char(to_date('" + p_work_mon + "', 'yyyymm'), 'mm yyyy') " +
" from dual ";
DataTable dt_date = CtlLib.TableReadOpen(sql_date);
int row_loop = 10;
int l_col = dt_detail.Columns.Count - 31;
int row_emp = dt_detail.Rows.Count;
int row_day = dt_day.Rows.Count;
int count = 0;
int s_row = 9;
int pos = 9;
int s_col = 6;
int temp = 0;
int l_col_pass = 11;
double[] grd_total = new double[l_col];
exSheet.Cells["A6"].Value = "THÁNG " + dt_date.Rows[0][0];
// do du lieu header ngay
for (int d = 0; d < dt_day.Rows.Count; d++)
{
exSheet.Cells[9, s_col + d].Value = dt_day.Rows[d][0].ToString();
}
for (int l_addrow = 1; l_addrow < dt_detail.Rows.Count; l_addrow++)
{
exSheet.Range[row_loop + l_addrow, 50].Rows.EntireRow.Insert(); //insert row new of sheet
}
// xu ly du lieu do vao excel
if (dt_detail.Rows.Count > 0)
{
for (int l_row = 0; l_row < dt_detail.Rows.Count; l_row++)
{
count++;
exSheet.Cells[row_loop + l_row, 1].Value = count;
// do du lieu vao cac dong
for (int col = 2; col < l_col; col++)
{
if (IsNumeric(dt_detail.Rows[l_row][col].ToString()) && col != 3)
{
grd_total[col] += double.Parse(dt_detail.Rows[l_row][col].ToString());
exSheet.Cells[row_loop + l_row, col].Value = double.Parse(dt_detail.Rows[l_row][col].ToString());
}
else
{
exSheet.Cells[row_loop + l_row, col].Value = dt_detail.Rows[l_row][col].ToString();
}
}
pos++;
//TO MAU CHO NHUNG NGAY OFF VA NGAY LE
for (int col = s_col; col < s_col + 31; col++)
{
if (dt_detail.Rows[l_row][col + l_col_pass + 31].ToString() == "SUN")
{
exSheet.Range[row_loop + l_row, col].Interior.Color = Color.SkyBlue;
}
if (dt_detail.Rows[l_row][col + l_col_pass + 31].ToString() == "HOL")
{
exSheet.Range[row_loop + l_row, col].Interior.Color = Color.Pink;
if (dt_detail.Rows[l_row][col].ToString() == "")
{
exSheet.Cells[row_loop + l_row, col].Value = "PH";
}
}
}
}
//do du lieu vao grand total
for (int k = 36; k < l_col; k++)
{
exSheet.Cells[pos + 1, k].Value = grd_total[k];
exSheet.Cells[pos + 1, k].Rows.Font.Bold = true;
}
}
//exSheet.Cells[row_loop + count + 2, 9].Value = "Ngày "+ dt_master.Rows[0]["ins_day"].ToString() +" Tháng "+ dt_master.Rows[0]["ins_mon"].ToString() + " Năm "+ dt_master.Rows[0]["ins_year"].ToString();
for (int row = 0; row < row_day; row++)
{
temp = 31 - row_day;
switch (temp)
{
case 3:
exSheet.Range["AH1"].Columns.Hidden = true;
exSheet.Range["AI1"].Columns.Hidden = true;
exSheet.Range["AJ1"].Columns.Hidden = true;
break;
case 2:
exSheet.Range["AI1"].Columns.Hidden = true;
exSheet.Range["AJ1"].Columns.Hidden = true;
break;
case 1:
exSheet.Range["AJ1"].Columns.Hidden = true;
break;
}
switch (dt_day.Rows[row][1].ToString())
{
case "SUN":
exSheet.Range[s_row, row + s_col].Interior.Color = Color.SkyBlue;
break;
case "HOL":
exSheet.Range[s_row, row + s_col, s_row, row + s_col].Interior.Color = Color.Pink;
break;
}
}
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
//range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
static List<Str_DataTable_Field?> CreateExcelDocument_GetFields(List<Str_DataTable?> List_Table, IWorksheet Ws_Template)
{
List<Str_DataTable_Field?> List_Dtf = new List<Str_DataTable_Field?>();
var List = from O in List_Table where O.HasValue orderby O.Value.Ct select O.Value;
foreach (var Item in List)
{
ER_Common.Str_ParsedExcelRange PR = ER_Common.ParseExcelRange(Item.Location);
Int32 Table_Width = PR.X2 - PR.X1;
Int32 Table_Pivot_Field_Ct = 0;
for (Int32 Ct = 0; Ct <= Table_Width; Ct++)
{
String Excel_Text = Ws_Template.Range[ER_Common.GenerateChr(PR.X1 + Ct) + PR.Y1.ToString()].Characters.Text;
if (Strings.InStr(Excel_Text, "[") > 0)
{
Table_Pivot_Field_Ct++;
String FieldName =
Strings.Mid(
Excel_Text
, Strings.InStr(Excel_Text, "[") + 1
, (Strings.InStrRev(Excel_Text, "]") - Strings.Len("]")) - 1);
Str_DataTable_Field Dtf = new Str_DataTable_Field();
Dtf.Ct = Table_Pivot_Field_Ct;
Dtf.DataTable_Ct = Item.Ct;
Dtf.Name = FieldName;
Dtf.Position = Ct;
List_Dtf.Add(Dtf);
}
}
}
return List_Dtf;
}
/// <summary>
/// Convert the Excel document to JSON
/// </summary>
/// <returns>Return the JSON document as stream</returns>
public MemoryStream ImportExportXlsIO(string button, string option, DataTable dataTable)
{
if (button == "Input Document")
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Step 2 : Instantiate the excel application object
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//Opening the encrypted Workbook
FileStream inputStream = new FileStream(ResolveApplicationPath("northwind-data-template.xls"), FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelParseOptions.Default);
//Save the document as a stream and retrun the stream
using (MemoryStream stream = new MemoryStream())
{
//Save the created Excel document to MemoryStream
workbook.SaveAs(stream);
return(stream);
}
}
}
else
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open]
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Exports the DataTable to a spreadsheet.
string fileName = string.Empty;
#region Workbook Initialize
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the Workbook version as Excel 97to2003
if (option == "XLS")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
fileName = "ExportToExcel.xls";
}
//Set the Workbook version as Excel 2007
else
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "ExportToExcel.xlsx";
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Export DataTable to Excel
//Export DataTable.
if (dataTable != null)
{
worksheet.ImportDataTable(dataTable, true, 3, 1, -1, -1);
}
#endregion
#region Formatting the Report
//Formatting the Report
#region Applying Body Stlye
//Body Style
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(9, Syncfusion.Drawing.Color.FromArgb(239, 242, 247));
bodyStyle.Color = Syncfusion.Drawing.Color.FromArgb(239, 243, 247);
bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
//Apply Style
worksheet.UsedRange.CellStyleName = "BodyStyle";
bodyStyle.EndUpdate();
#endregion
#region Applying Header Style
//Header Style
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(8, Syncfusion.Drawing.Color.FromArgb(182, 189, 218));
headerStyle.Color = Syncfusion.Drawing.Color.FromArgb(182, 189, 218);
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
//Apply Style
worksheet.Range["A1:K3"].CellStyleName = "HeaderStyle";
headerStyle.EndUpdate();
#endregion
//Remove grid lines in the worksheet.
worksheet.IsGridLinesVisible = false;
//Autofit Rows and Columns
worksheet.UsedRange.AutofitRows();
worksheet.UsedRange.AutofitColumns();
//Adjust Row Height.
worksheet.Rows[1].RowHeight = 25;
//Freeze header row.
worksheet.Range["A4"].FreezePanes();
worksheet.Range["C2"].Text = "Customer Details";
worksheet.Range["C2:D2"].Merge();
worksheet.Range["C2"].CellStyle.Font.Size = 14;
worksheet.Range["C2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
#endregion
#region Workbook Save and Close
//Saving the workbook to disk.
//Save the document as a stream and retrun the stream
using (MemoryStream stream = new MemoryStream())
{
//Save the created Excel document to MemoryStream
workbook.SaveAs(stream);
return(stream);
}
#endregion
}
}
}
static List<Str_Sections?> CreateExcelDocument_GetSections(IWorksheet Ws_Parameters)
{
List<Str_Sections?> List_Sections = new List<Str_Sections?>();
Int32[] LineInfo = CreateExcelDocument_ReadLineInfo(Ws_Parameters, CnsExcelKeyword_Sections, CnsExcelKeyword_Sections_End);
Int32 Ct_Start = LineInfo[0];
Int32 Ct_End = LineInfo[1];
Int32 Ct_Sections = 0;
for (Int32 Ct = Ct_Start; Ct <= Ct_End; Ct++)
{
String ExcelText = Ws_Parameters.Range["A" + Ct.ToString()].Characters.Text;
String Type = "";
String Location = "";
if (!(Strings.InStr(ExcelText, "[") > 0))
{
try
{
Ct_Sections++;
Type = Strings.Mid(ExcelText, 1, (Strings.InStr(ExcelText, " ")) - 1);
Location = Strings.Mid(ExcelText, (Strings.InStr(ExcelText, " ")) + 1);
List_Sections.Add(
new Str_Sections()
{
Ct = Ct_Sections,
Type = Type.ToUpper(),
Location = Location.ToUpper()
});
}
catch
{ throw new Exception(@"Invalid Syntax in [#]Sections."); }
}
}
return List_Sections;
}
private async Task <int> TranslateSheet2(IWorksheet currentSheet, List <IRange> arRange, int numPrevRequest)
{
ActionF1 ActionF1 = new ActionF1();
var ienum = arRange.AsEnumerable();
TaskScheduler tsc = TaskScheduler.Current;
int i = 0;
IRange whatIR = null;
int limit = arRange.Count;
limit = Math.Min(limit, _iMaxRequest - numPrevRequest);
for (i = 0; i < limit; i++)
{
whatIR = arRange[i];
//DataTable orgTa = whatIR.GetDataTable(SpreadsheetGear.Data.GetDataFlags.NoColumnHeaders); //error convert type double (set columntype = type of first cell
DataTable orgTa = GetTableFromIrange(whatIR);
string originalText = GetTextFromTable2(orgTa);
if (originalText.Length > 5000)
{
continue;
}
JArray jarr = await ActionF1.GetSingle(originalText, _fromLang, _toLang);
toolStripProgressBar1.Value = (int)((i + 1) * 100 / limit);
Thread.Sleep(480);
List <string> transateText = ActionF1.ReadJArrayRes2(jarr);
////fake start
//JArray jarr = await FakeTrans(originalText);
//List<string> transateText = null;
////fake end
DataTable traTa = GetTableFromText(transateText, orgTa);
//translate sucess
if (traTa != null)
{
for (int im = 0; im < traTa.Rows.Count; im++)
{
object vv = traTa.Rows[im][0];
string[] speStart = new string[] { "\n", "「", "\"", "“", };
if (vv != null && vv.ToString().Trim().Length > 1 &&
(System.Text.RegularExpressions.Regex.IsMatch(vv.ToString().Trim().Substring(0, 1), @"[a-zA-Z0-9]") || speStart.Contains(vv.ToString().Substring(0, 1))))
{
if (currentSheet.Range[whatIR.Address].Cells[im, 0].Comment != null)
{
currentSheet.Range[whatIR.Address].Cells[im, 0].ClearComments();
}
currentSheet.Range[whatIR.Address].Cells[im, 0].AddComment(vv.ToString());
IComment ic = currentSheet.Range[whatIR.Address].Cells[im, 0].Comment;
using (Graphics g = this.CreateGraphics())
{
string item = ic.ToString();
SizeF sizeF = g.MeasureString(item, Font);
ic.Shape.Width = sizeF.Width;
}
}
}
}
else
{
//remain originalText
}
}
return(limit + numPrevRequest);
}
//[-]
static Str_Settings CreateExcelDocument_GetSettings(IWorksheet Ws_Parameters)
{
Str_Settings Rv_Settings = new Str_Settings();
Int32[] LineInfo = CreateExcelDocument_ReadLineInfo(Ws_Parameters, CnsExcelKeyword_Settings, CnsExcelKeyword_Settings_End);
Int32 Ct_Start = LineInfo[0];
Int32 Ct_End = LineInfo[1];
for (Int32 Ct = Ct_Start; Ct <= Ct_End; Ct++)
{
String ExcelText = Ws_Parameters.Range["A" + Ct.ToString()].Characters.Text;
if ((Strings.InStr(ExcelText, "@DocumentLimit") > 0))
{ Rv_Settings.DocumentLimit = ER_Common.Convert_Int32(Strings.Mid(ExcelText, Strings.Len("@DocumentLimit") + 1)); }
else if ((Strings.InStr(ExcelText, "@DocumentWidth") > 0))
{ Rv_Settings.DocumentWidth = ER_Common.Convert_Int32(Strings.Mid(ExcelText, Strings.Len("@DocumentWidth") + 1)); }
else if ((Strings.InStr(ExcelText, "@IsRepeatHeader") > 0))
{ Rv_Settings.IsRepeatHeader = ER_Common.Convert_Boolean(Strings.Mid(ExcelText, Strings.Len("@IsRepeatHeader") + 1)); }
}
return Rv_Settings;
}
private static void AddDataHeader(IWorksheet ws, IEnumerable<ColumnHeader> headers)
{
const int rowIndex = 0;
IRange row = ws.Cells[rowIndex, 0, rowIndex, headers.Count()];
int col = 0;
foreach (var columnHeader in headers)
{
SetColumnAsText(row[rowIndex, col++], columnHeader.Title);
}
row.Font.Bold = true;
row.WrapText = true;
SetColumnWidths(row, (from h in headers select h.Width).ToArray());
}
static void CreateExcelDocument_V3_SubTable(
DataSet Ds_Source
, List<Str_DataTable?> List_Tables
, List<Str_DataTable_Field?> List_Table_Fields
, IWorksheet Ws_Template
, IWorksheet Ws_Document
, ref Int32 Ct_CurrentRow
, List<Str_DataTable?> List_Tables_Group
, DataRow Dr_SourceKey)
{
foreach (Str_DataTable Var_Table in List_Tables_Group)
{
Str_DataTable Table = Var_Table;
ER_Common.Str_ParsedExcelRange PR = ER_Common.ParseExcelRange(Table.Location);
Int32 Table_Ct = Table.Ct - 1;
Int32 Ct_Items = CreateExcelDocument_V3_CountItem(
Table
, (from O in List_Tables select O.Value).ToList()
, Ds_Source
, Dr_SourceKey);
if (Ct_Items > 0)
{
//Set Formatting
Int32 Row_Start = Ct_CurrentRow;
Int32 Row_End = Row_Start + (Ct_Items - 1);
String Location_Source;
String Location_Target;
//Table Formats and Borders
if (Ct_Items == 1)
{
Location_Source = ER_Common.GenerateChr(PR.X1) + (PR.Y1 + 4).ToString() + @":" + ER_Common.GenerateChr(PR.X2) + (PR.Y1 + 4).ToString();
Location_Target = ER_Common.GenerateChr(PR.X1) + Row_End.ToString() + @":" + ER_Common.GenerateChr(PR.X2) + Row_End.ToString();
Ws_Template.Range[Location_Source].Copy(Ws_Document.Range[Location_Target], XlPasteType.xlPasteFormats);
}
else
{
// - Top
Location_Source = ER_Common.GenerateChr(PR.X1) + PR.Y1.ToString() + @":" + ER_Common.GenerateChr(PR.X2) + PR.Y1.ToString();
Location_Target = ER_Common.GenerateChr(PR.X1) + Row_Start.ToString() + @":" + ER_Common.GenerateChr(PR.X2) + Row_Start.ToString();
Ws_Template.Range[Location_Source].Copy(Ws_Document.Range[Location_Target], XlPasteType.xlPasteFormats);
// - Middle
if (Ct_Items > 2)
{
Location_Source = ER_Common.GenerateChr(PR.X1) + (PR.Y1 + 1).ToString() + @":" + ER_Common.GenerateChr(PR.X2) + (PR.Y1 + 1).ToString();
Location_Target = ER_Common.GenerateChr(PR.X1) + (Row_Start + 1).ToString() + @":" + ER_Common.GenerateChr(PR.X2) + (Row_End - 1).ToString();
Ws_Template.Range[Location_Source].Copy(Ws_Document.Range[Location_Target], XlPasteType.xlPasteFormats);
}
// - Bottom
Location_Source = ER_Common.GenerateChr(PR.X1) + (PR.Y1 + 2).ToString() + @":" + ER_Common.GenerateChr(PR.X2) + (PR.Y1 + 2).ToString();
Location_Target = ER_Common.GenerateChr(PR.X1) + Row_End.ToString() + @":" + ER_Common.GenerateChr(PR.X2) + Row_End.ToString();
Ws_Template.Range[Location_Source].Copy(Ws_Document.Range[Location_Target], XlPasteType.xlPasteFormats);
}
//Table Data
String[] Arr_Fields = new String[PR.X2 - PR.X1];
for (Int32 Inner_Ct = 0; Inner_Ct < Arr_Fields.Length; Inner_Ct++)
{
Str_DataTable_Field? Dtf = List_Table_Fields.First(O => O.Value.DataTable_Ct == Table.Ct && O.Value.Position == Inner_Ct);
if (Dtf != null)
{ Arr_Fields[Inner_Ct] = Dtf.Value.Name; }
else
{ Arr_Fields[Inner_Ct] = ""; }
}
String SourceKey = Table.SourceKey;
String TargetKey = Table.TargetKey;
String Condition = "";
String SourceKey_ID;
if (SourceKey != "")
{
if (Dr_SourceKey != null)
{
if (Dr_SourceKey.Table.Columns.Contains(SourceKey))
{
SourceKey_ID = ER_Common.Convert_String(Dr_SourceKey[SourceKey], "0");
Condition = TargetKey + @" = " + @"'" + SourceKey_ID + @"'";
}
}
}
DataRow[] Arr_Data = Ds_Source.Tables[Table_Ct].Select(Condition);
foreach (DataRow Dr in Arr_Data)
{
IRange R = Ws_Document.Range[ER_Common.GenerateChr(PR.X1) + Ct_CurrentRow.ToString() + @":" + ER_Common.GenerateChr(PR.X2) + Ct_CurrentRow.ToString()];
R.Value = ER_Common.ConvertDataTo2DimArray(Dr, Arr_Fields);
Ct_CurrentRow++;
var Inner_List_Table_Group =
(from O in List_Tables_Group
where O.Value.IsSubTable == true && O.Value.GroupName == Table.Name
select O).ToList();
if (Inner_List_Table_Group.Any())
{
CreateExcelDocument_V3_SubTable(
Ds_Source
, List_Tables
, List_Table_Fields
, Ws_Template
, Ws_Document
, ref Ct_CurrentRow
, Inner_List_Table_Group
, Dr);
}
}
}
}
}
private static void AddPopulationTitlesToSheet(IList<string> sexNames, IList<string> timePeriods,
IList<string> populationLabels, IWorksheet ws, int startColumn)
{
int columnsPerSex = populationLabels.Count;
int columnPerPeriod = columnsPerSex * sexNames.Count;
const int sexRow = 1;
const int timeRow = 0;
const int ageRow = 2;
int sexColumn = startColumn;
int ageColumn = startColumn;
int timeColumn = startColumn;
foreach (var timePeriod in timePeriods)
{
int endTimeColumn = timeColumn + columnPerPeriod - 1;
IRange timeRange = ws.Cells[timeRow, timeColumn, timeRow, endTimeColumn];
timeRange.Merge();
FormatAsTitle(timeRange);
timeRange.Value = timePeriod;
timeColumn += columnPerPeriod;
foreach (var sexName in sexNames)
{
int endSexColumn = sexColumn + columnsPerSex - 1;
IRange sexRange = ws.Cells[sexRow, sexColumn, sexRow, endSexColumn];
sexRange.Merge();
FormatAsTitle(sexRange);
sexRange.Value = sexName;
sexColumn += columnsPerSex;
foreach (var populationLabel in populationLabels)
{
IRange ageRange = ws.Cells[ageRow, ageColumn++];
FormatAsTitle(ageRange);
ageRange.NumberFormat = "@";
ageRange.Value = populationLabel;
}
}
}
}
public void CopyToLocation(IWorksheet targetWorksheet, int targetRow, int targetColumn)
{
AssertNotDisposed();
var targetRange = targetWorksheet.GetRange(targetRow, targetColumn, targetRow + EndRow - StartRow,
targetColumn + EndColumn - StartColumn) as Range;
if (targetRange == null)
{
throw new InvalidOperationException("Implementation of this method depends on another Office Interop wrapper.");
}
Excel.Range _range = _GetRange();
Excel.Range _targetRange = targetRange._GetRange();
_range.Copy(_targetRange);
Marshal.ReleaseComObject(_targetRange);
Marshal.ReleaseComObject(_range);
}
public static DataTable NativeExcel_CreateExcelDocument_GetSections(IWorksheet Sheet_Parameters)
{
DataTable Dt_ReturnValue = new DataTable();
Dt_ReturnValue.Columns.Add("Ct", typeof(Int32));
Dt_ReturnValue.Columns.Add("Type", typeof(string));
Dt_ReturnValue.Columns.Add("Location", typeof(string));
Int32 CtStart = 0;
Int32 CtEnd = 0;
Int32 Ct = 0;
for (Ct = 1; Ct <= 65536; Ct++)
{
switch (Sheet_Parameters.Range["A" + Ct.ToString()].Characters.Text)
{
case "[#]Sections":
CtStart = Ct;
break;
case "[#]End_Sections":
CtEnd = Ct;
break;
}
}
Int32 Sections_Ct = 0;
for (Ct = CtStart; Ct <= CtEnd; Ct++)
{
string ExcelText = Sheet_Parameters.Range["A" + Ct.ToString()].Characters.Text;
string Section_Type = "";
string Section_Location = "";
if (!(ExcelText.IndexOf("[") > 0))
{
try
{
Section_Type = ExcelText.Substring(1, ExcelText.IndexOf(' ') - 1);
Section_Location = ExcelText.Substring(ExcelText.IndexOf(' ') + 1);
Sections_Ct++;
DataRow Nr = Dt_ReturnValue.NewRow();
Nr["Ct"] = Sections_Ct;
Nr["Type"] = Section_Type;
Nr["Location"] = Section_Location;
Dt_ReturnValue.Rows.Add(Nr);
}
catch (Exception ex)
{ throw new Exception("Error Occured: NativeExcel_CreateExcelDocument_GetSections: " + ex.Message); }
}
}
return Dt_ReturnValue;
}
private IRange SearchSheet(IWorksheet xlSheet, string what)
{
IRange findedRange = xlSheet.Range.Find(what, null, FindLookIn.Values, LookAt.Whole, SearchOrder.ByColumns, SearchDirection.Next, true);
return(findedRange);
}
public async void ExportGunList(object obj, EventArgs args)
{
exportGunBtn.IsEnabled = false;
//Create an instance of ExcelEngine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Set the default application version as Excel 2013.
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013;
//Create a workbook with a worksheet
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
//Access first worksheet from the workbook instance.
IWorksheet worksheet = workbook.Worksheets[0];
//Enabling formula calculation.
worksheet.EnableSheetCalculations();
worksheet["A1"].Text = "ID";
worksheet["B1"].Text = "Name";
worksheet["C1"].Text = "Type";
worksheet["D1"].Text = "Serial Number";
worksheet["E1"].Text = "Description";
worksheet["F1"].Text = "Value";
worksheet["G1"].Text = "Caliber";
worksheet["H1"].Text = "Manufacturer";
//Set the column width to fit to text.
worksheet.Range["A1:H1"].AutofitColumns();
//Set the row to have wrapped text.
//worksheet.Range["A1:H1"].WrapText = true;
//Set the style for header range.
IRange headingRange = worksheet["A1:H1"];
headingRange.CellStyle.Font.Bold = true;
headingRange.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
headingRange.CellStyle.ColorIndex = ExcelKnownColors.Light_green;
try
{
_gunDatabase = new GunDatabase();
var guns = _gunDatabase.GetGuns();
var i = 2;
foreach (var item in guns)
{
worksheet["A" + i].Text = item.ID.ToString();
worksheet["B" + i].Text = item.GunName;
worksheet["C" + i].Text = item.GunType;
worksheet["D" + i].Text = item.GunSerialNumber;
worksheet["E" + i].Text = item.GunDescription;
worksheet["F" + i].Text = item.GunValue;
worksheet["G" + i].Text = item.GunCaliber;
worksheet["H" + i].Text = item.GunManufacturer;
i++;
}
worksheet.SetColumnWidth(5, 40);
worksheet.UsedRange.WrapText = true;
//Save the workbook to stream in xlsx format.
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
//Save the stream as a file in the device and invoke it for viewing
await DependencyService.Get <ISave>().SaveAndView("GunStockList.xlsx", stream);
}
catch (Exception ex)
{
Debug.WriteLine(@" GunStock.GunPage.ExportGunList ERROR {0}", ex.Message);
}
}
}
private static void AddIndicatorTitle(int startCol, IList<TimePeriod> periods, IWorksheet ws, IndicatorMetadata metadata)
{
int col = startCol;
int endColumn = col + (periods.Count * ColumnsPerIndicator) - 1;
IRange range = ws.Cells[0, col, 0, endColumn];
range.Merge();
range.Value = metadata.Descriptive[IndicatorMetadataTextColumnNames.Name];
// Time
const int dateRowIndex = 1;
const int labelRowIndex = 2;
var timePeriodFormatter = new TimePeriodTextFormatter(metadata);
foreach (TimePeriod timePeriod in periods)
{
var endColumnForPeriod = col + ColumnsPerIndicator - 1;
// Time title
range = ws.Cells[dateRowIndex, col, dateRowIndex, endColumnForPeriod];
range.Merge();
range.Value = timePeriodFormatter.Format(timePeriod);
// Val, LCI, UCI, Count, Denominator
ws.Cells[labelRowIndex, col++].Value = "Value";
ws.Cells[labelRowIndex, col++].Value = "Lower CI";
ws.Cells[labelRowIndex, col++].Value = "Upper CI";
ws.Cells[labelRowIndex, col++].Value = "Count";
ws.Cells[labelRowIndex, col++].Value = "Denom";
}
// Format
range = ws.Cells[0, startCol, StartingDataRow - 1, col];
FormatAsTitle(range);
}
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e)
{
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
// Default version is set as Excel 2007
if (this.rdbExcel2013.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
else
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheet.
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
// Entering the Datas for the chart
sheet.Range["A1"].Text = "Crescent City, CA";
sheet.Range["A1:D1"].Merge();
sheet.Range["A1"].CellStyle.Font.Bold = true;
sheet.Range["B3"].Text = "Precipitation,in.";
sheet.Range["C3"].Text = "Temperature,deg.F";
sheet.Range["A4"].Text = "Jan";
sheet.Range["A5"].Text = "Feb";
sheet.Range["A6"].Text = "March";
sheet.Range["A7"].Text = "Apr";
sheet.Range["A8"].Text = "May";
sheet.Range["A9"].Text = "June";
sheet.Range["A10"].Text = "July";
sheet.Range["A11"].Text = "Aug";
sheet.Range["A12"].Text = "Sept";
sheet.Range["A13"].Text = "Oct";
sheet.Range["A14"].Text = "Nov";
sheet.Range["A15"].Text = "Dec";
sheet.Range["B4"].Number = 10.9;
sheet.Range["B5"].Number = 8.9;
sheet.Range["B6"].Number = 8.6;
sheet.Range["B7"].Number = 4.8;
sheet.Range["B8"].Number = 3.2;
sheet.Range["B9"].Number = 1.4;
sheet.Range["B10"].Number = 0.6;
sheet.Range["B11"].Number = 0.7;
sheet.Range["B12"].Number = 1.7;
sheet.Range["B13"].Number = 5.4;
sheet.Range["B14"].Number = 9.0;
sheet.Range["B15"].Number = 10.4;
sheet.Range["C4"].Number = 47.5;
sheet.Range["C5"].Number = 48.7;
sheet.Range["C6"].Number = 48.9;
sheet.Range["C7"].Number = 50.2;
sheet.Range["C8"].Number = 53.1;
sheet.Range["C9"].Number = 56.3;
sheet.Range["C10"].Number = 58.1;
sheet.Range["C11"].Number = 59.0;
sheet.Range["C12"].Number = 58.5;
sheet.Range["C13"].Number = 55.4;
sheet.Range["C14"].Number = 51.1;
sheet.Range["C15"].Number = 47.8;
sheet.UsedRange.AutofitColumns();
// Adding a New chart to the Existing Worksheet
IChart chart = workbook.Charts.Add();
chart.DataRange = sheet.Range["A3:C15"];
chart.Name = "CrescentCity,CA";
chart.ChartTitle = "Crescent City, CA";
chart.IsSeriesInRows = false;
chart.PrimaryValueAxis.Title = "Precipitation,in.";
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
chart.PrimaryValueAxis.MaximumValue = 14.0;
chart.PrimaryValueAxis.NumberFormat = "0.0";
// Format serie
IChartSerie serieOne = chart.Series[0];
serieOne.Name = "Precipitation,in.";
serieOne.SerieFormat.Fill.FillType = ExcelFillType.Gradient;
serieOne.SerieFormat.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
serieOne.SerieFormat.Fill.GradientColorType = ExcelGradientColor.TwoColor;
serieOne.SerieFormat.Fill.ForeColor = Color.FromArgb(255, 221, 160, 221);
//Show value as data labels
serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
serieOne.DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside;
//Format the second serie
IChartSerie serieTwo = chart.Series[1];
serieTwo.SerieType = ExcelChartType.Line_Markers;
serieTwo.Name = "Temperature,deg.F";
//Format marker
serieTwo.SerieFormat.MarkerStyle = ExcelChartMarkerType.Diamond;
serieTwo.SerieFormat.MarkerSize = 8;
serieTwo.SerieFormat.MarkerBackgroundColor = Color.FromArgb(255, 0, 100, 0);
serieTwo.SerieFormat.MarkerForegroundColor = Color.FromArgb(255, 0, 100, 0);
serieTwo.SerieFormat.LineProperties.LineColor = Color.FromArgb(255, 0, 100, 0);
//Use Secondary Axis
serieTwo.UsePrimaryAxis = false;
//Display secondary axis for the series.
chart.SecondaryCategoryAxis.IsMaxCross = true;
chart.SecondaryValueAxis.IsMaxCross = true;
//Set the title
chart.SecondaryValueAxis.Title = "Temperature,deg.F";
chart.SecondaryValueAxis.TitleArea.TextRotationAngle = 90;
//Hide the secondary category axis
chart.SecondaryCategoryAxis.Border.LineColor = Color.FromArgb(0, 0, 0, 0);
chart.SecondaryCategoryAxis.MajorTickMark = ExcelTickMark.TickMark_None;
chart.SecondaryCategoryAxis.TickLabelPosition = ExcelTickLabelPosition.TickLabelPosition_None;
chart.Legend.Position = ExcelLegendPosition.Bottom;
chart.Legend.IsVerticalLegend = false;
//Excel2013 Filter option
if (rdbExcel2013.IsChecked.Value)
{
chart.Series[1].IsFiltered = true;
chart.Categories[0].IsFiltered = true;
chart.Categories[1].IsFiltered = true;
}
sheet.Move(1);
chart.Activate();
#region Save the Workbook
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "ChartWorksheet";
if (workbook.Version == ExcelVersion.Excel97to2003)
{
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xls"
});
}
else
{
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xlsx",
});
}
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
if (workbook.Version == ExcelVersion.Excel97to2003)
{
storageFile = await local.CreateFileAsync("ChartWorksheet.xls", CreationCollisionOption.ReplaceExisting);
}
else
{
storageFile = await local.CreateFileAsync("ChartWorksheet.xlsx", CreationCollisionOption.ReplaceExisting);
}
}
if (storageFile != null)
{
//Saving the workbook
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
}
else
{
workbook.Close();
excelEngine.Dispose();
}
#endregion
}
private static void AddPracticeAddressHeader(IWorksheet ws)
{
AddDataHeader(ws, new[] {
new ColumnHeader { Title = HeaderPracticeCode, Width = ColumnWidthAreaCode },
new ColumnHeader { Title = "Practice Name", Width = 40 },
new ColumnHeader { Title = "Address", Width = 80 },
new ColumnHeader { Title = "Postcode", Width = 15 }
});
}
public ActionResult Create(string SaveOption)
{
if (SaveOption == null)
{
return(View());
}
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
// Creating new workbook
IWorkbook workbook = application.Workbooks.Create(3);
IWorksheet sheet = workbook.Worksheets[0];
#region Generate Excel
sheet.Range["A2"].ColumnWidth = 30;
sheet.Range["B2"].ColumnWidth = 30;
sheet.Range["C2"].ColumnWidth = 30;
sheet.Range["D2"].ColumnWidth = 30;
sheet.Range["A2:D2"].Merge(true);
//Inserting sample text into the first cell of the first sheet.
sheet.Range["A2"].Text = "EXPENSE REPORT";
sheet.Range["A2"].CellStyle.Font.FontName = "Verdana";
sheet.Range["A2"].CellStyle.Font.Bold = true;
sheet.Range["A2"].CellStyle.Font.Size = 28;
sheet.Range["A2"].CellStyle.Font.RGBColor = Color.FromArgb(0, 0, 112, 192);
sheet.Range["A2"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["A4"].Text = "Employee";
sheet.Range["B4"].Text = "Roger Federer";
sheet.Range["A4:B7"].CellStyle.Font.FontName = "Verdana";
sheet.Range["A4:B7"].CellStyle.Font.Bold = true;
sheet.Range["A4:B7"].CellStyle.Font.Size = 11;
sheet.Range["A4:A7"].CellStyle.Font.RGBColor = Color.FromArgb(0, 128, 128, 128);
sheet.Range["A4:A7"].HorizontalAlignment = ExcelHAlign.HAlignLeft;
sheet.Range["B4:B7"].CellStyle.Font.RGBColor = Color.FromArgb(0, 174, 170, 170);
sheet.Range["B4:B7"].HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["A9:D20"].CellStyle.Font.FontName = "Verdana";
sheet.Range["A9:D20"].CellStyle.Font.Size = 11;
sheet.Range["A5"].Text = "Department";
sheet.Range["B5"].Text = "Administration";
sheet.Range["A6"].Text = "Week Ending";
sheet.Range["B6"].NumberFormat = "m/d/yyyy";
sheet.Range["B6"].DateTime = DateTime.Parse("10/20/2012", CultureInfo.InvariantCulture);
sheet.Range["A7"].Text = "Mileage Rate";
sheet.Range["B7"].NumberFormat = "$#,##0.00";
sheet.Range["B7"].Number = 0.70;
sheet.Range["A10"].Text = "Miles Driven";
sheet.Range["A11"].Text = "Miles Reimbursement";
sheet.Range["A12"].Text = "Parking and Tolls";
sheet.Range["A13"].Text = "Auto Rental";
sheet.Range["A14"].Text = "Lodging";
sheet.Range["A15"].Text = "Breakfast";
sheet.Range["A16"].Text = "Lunch";
sheet.Range["A17"].Text = "Dinner";
sheet.Range["A18"].Text = "Snacks";
sheet.Range["A19"].Text = "Others";
sheet.Range["A20"].Text = "Total";
sheet.Range["A20:D20"].CellStyle.Color = Color.FromArgb(0, 0, 112, 192);
sheet.Range["A20:D20"].CellStyle.Font.Color = ExcelKnownColors.White;
sheet.Range["A20:D20"].CellStyle.Font.Bold = true;
IStyle style = sheet["B9:D9"].CellStyle;
style.VerticalAlignment = ExcelVAlign.VAlignCenter;
style.HorizontalAlignment = ExcelHAlign.HAlignRight;
style.Color = Color.FromArgb(0, 0, 112, 192);
style.Font.Bold = true;
style.Font.Color = ExcelKnownColors.White;
sheet.Range["A9"].Text = "Expenses";
sheet.Range["A9"].CellStyle.Color = Color.FromArgb(0, 0, 112, 192);
sheet.Range["A9"].CellStyle.Font.Color = ExcelKnownColors.White;
sheet.Range["A9"].CellStyle.Font.Bold = true;
sheet.Range["B9"].Text = "Day 1";
sheet.Range["B10"].Number = 100;
sheet.Range["B11"].NumberFormat = "$#,##0.00";
sheet.Range["B11"].Formula = "=(B7*B10)";
sheet.Range["B12"].NumberFormat = "$#,##0.00";
sheet.Range["B12"].Number = 0;
sheet.Range["B13"].NumberFormat = "$#,##0.00";
sheet.Range["B13"].Number = 0;
sheet.Range["B14"].NumberFormat = "$#,##0.00";
sheet.Range["B14"].Number = 0;
sheet.Range["B15"].NumberFormat = "$#,##0.00";
sheet.Range["B15"].Number = 9;
sheet.Range["B16"].NumberFormat = "$#,##0.00";
sheet.Range["B16"].Number = 12;
sheet.Range["B17"].NumberFormat = "$#,##0.00";
sheet.Range["B17"].Number = 13;
sheet.Range["B18"].NumberFormat = "$#,##0.00";
sheet.Range["B18"].Number = 9.5;
sheet.Range["B19"].NumberFormat = "$#,##0.00";
sheet.Range["B19"].Number = 0;
sheet.Range["B20"].NumberFormat = "$#,##0.00";
sheet.Range["B20"].Formula = "=SUM(B11:B19)";
sheet.Range["C9"].Text = "Day 2";
sheet.Range["C10"].Number = 145;
sheet.Range["C11"].NumberFormat = "$#,##0.00";
sheet.Range["C11"].Formula = "=(B7*C10)";
sheet.Range["C12"].NumberFormat = "$#,##0.00";
sheet.Range["C12"].Number = 15;
sheet.Range["C13"].NumberFormat = "$#,##0.00";
sheet.Range["C13"].Number = 0;
sheet.Range["C14"].NumberFormat = "$#,##0.00";
sheet.Range["C14"].Number = 45;
sheet.Range["C15"].NumberFormat = "$#,##0.00";
sheet.Range["C15"].Number = 9;
sheet.Range["C16"].NumberFormat = "$#,##0.00";
sheet.Range["C16"].Number = 12;
sheet.Range["C17"].NumberFormat = "$#,##0.00";
sheet.Range["C17"].Number = 15;
sheet.Range["C18"].NumberFormat = "$#,##0.00";
sheet.Range["C18"].Number = 7;
sheet.Range["C19"].NumberFormat = "$#,##0.00";
sheet.Range["C19"].Number = 0;
sheet.Range["C20"].NumberFormat = "$#,##0.00";
sheet.Range["C20"].Formula = "=SUM(C11:C19)";
sheet.Range["D9"].Text = "Day 3";
sheet.Range["D10"].Number = 113;
sheet.Range["D11"].NumberFormat = "$#,##0.00";
sheet.Range["D11"].Formula = "=(B7*D10)";
sheet.Range["D12"].NumberFormat = "$#,##0.00";
sheet.Range["D12"].Number = 17;
sheet.Range["D13"].NumberFormat = "$#,##0.00";
sheet.Range["D13"].Number = 8;
sheet.Range["D14"].NumberFormat = "$#,##0.00";
sheet.Range["D14"].Number = 45;
sheet.Range["D15"].NumberFormat = "$#,##0.00";
sheet.Range["D15"].Number = 7;
sheet.Range["D16"].NumberFormat = "$#,##0.00";
sheet.Range["D16"].Number = 11;
sheet.Range["D17"].NumberFormat = "$#,##0.00";
sheet.Range["D17"].Number = 16;
sheet.Range["D18"].NumberFormat = "$#,##0.00";
sheet.Range["D18"].Number = 7;
sheet.Range["D19"].NumberFormat = "$#,##0.00";
sheet.Range["D19"].Number = 5;
sheet.Range["D20"].NumberFormat = "$#,##0.00";
sheet.Range["D20"].Formula = "=SUM(D11:D19)";
#endregion
string ContentType = null;
string fileName = null;
if (SaveOption == "ExcelXls")
{
ContentType = "Application/vnd.ms-excel";
fileName = "Sample.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "Sample.xlsx";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
public static DataTable NativeExcel_CreateExcelDocument_GetDataTables(IWorksheet Sheet_Parameters)
{
DataTable Dt_ReturnValue = new DataTable();
Dt_ReturnValue.Columns.Add("Ct", typeof(Int32));
Dt_ReturnValue.Columns.Add("Name", typeof(string));
Dt_ReturnValue.Columns.Add("GroupName", typeof(string));
Dt_ReturnValue.Columns.Add("SourceKey", typeof(string));
Dt_ReturnValue.Columns.Add("TargetKey", typeof(string));
Dt_ReturnValue.Columns.Add("IsSubTable", typeof(string));
Dt_ReturnValue.Columns.Add("Location", typeof(string));
Dt_ReturnValue.Columns.Add("Items", typeof(Int32));
Int32 CtStart = 0;
Int32 CtEnd = 0;
Int32 Ct = 0;
for (Ct = 1; Ct <= 65536; Ct++)
{
switch (Sheet_Parameters.Range["A" + Ct.ToString()].Characters.Text)
{
case "[#]DataTable":
CtStart = Ct;
break;
case "[#]End_DataTable":
CtEnd = Ct;
break;
}
}
if (CtStart == 0 && CtEnd == 0)
{ throw new Exception(@"Invalid Syntax in [#]DataTable."); }
Int32 DataTable_Ct = 0;
for (Ct = CtStart; Ct <= CtEnd; Ct++)
{
string ExcelText = "";
try { ExcelText = Sheet_Parameters.Range["A" + Ct.ToString()].Characters.Text; }
catch { }
string DataTable_Name = "";
string DataTable_GroupName = "";
string DataTable_SourceKey = "";
string DataTable_TargetKey = "";
string DataTable_Location = "";
if (!(ExcelText.IndexOf("[") > 0))
{
try
{
string[] Inner_Arr = ExcelText.Split(' ');
//Continue Here
DataTable_Name = Inner_Arr[0];
DataTable_Location = Inner_Arr[1];
try
{
DataTable_GroupName = Inner_Arr[2];
DataTable_SourceKey = Inner_Arr[3];
DataTable_TargetKey = Inner_Arr[4];
}
catch { }
DataTable_Ct++;
DataRow Nr = Dt_ReturnValue.NewRow();
Nr["Ct"] = DataTable_Ct;
Nr["Name"] = DataTable_Name;
Nr["Location"] = DataTable_Location;
if (DataTable_GroupName.Trim() != "")
{
Nr["GroupName"] = DataTable_GroupName;
Nr["SourceKey"] = DataTable_SourceKey;
Nr["TargetKey"] = DataTable_TargetKey;
Nr["IsSubTable"] = true;
}
Dt_ReturnValue.Rows.Add(Nr);
}
catch (Exception ex)
{ throw new Exception("Error Occured: NativeExcel_CreateExcelDocument_GetDataTables: " + ex.Message); }
}
}
return Dt_ReturnValue;
}
private void FilterData(string outFileName)
{
#region Workbook Initialize
//Get the path of the input file
string inputPath = GetFullTemplatePath(outFileName);
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inputPath, ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets[0];
#endregion
int columnIndex = cmbFirst.SelectedIndex;
if (columnIndex != 6)
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
switch (columnIndex)
{
case 0:
IAutoFilter filter1 = sheet.AutoFilters[0];
filter1.IsAnd = false;
filter1.FirstCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.FirstCondition.DataType = ExcelFilterDataType.String;
filter1.FirstCondition.String = "Owner";
filter1.SecondCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.SecondCondition.DataType = ExcelFilterDataType.String;
filter1.SecondCondition.String = "Sales Representative";
break;
case 1:
IAutoFilter filter2 = sheet.AutoFilters[0];
filter2.AddTextFilter(new string[] { "Owner", "Sales Representative", "Sales Associate" });
break;
case 2:
IAutoFilter filter3 = sheet.AutoFilters[1];
filter3.AddDateFilter(new DateTime(2004, 9, 1, 1, 0, 0, 0), DateTimeGroupingType.month);
filter3.AddDateFilter(new DateTime(2011, 1, 1, 1, 0, 0, 0), DateTimeGroupingType.year);
break;
case 4:
#region Color Filter
sheet.AutoFilters.FilterRange = sheet["A1:C49"];
Color color = Color.Empty;
switch (comboBox1.SelectedIndex)
{
case 0:
color = Color.Red;
break;
case 1:
color = Color.Blue;
break;
case 2:
color = Color.Green;
break;
case 3:
color = Color.Yellow;
break;
case 4:
//Do nothing.
break;
}
if (radioButton3.Checked)
{
IAutoFilter filter = sheet.AutoFilters[0];
filter.AddColorFilter(color, ExcelColorFilterType.CellColor);
}
else
{
IAutoFilter filter = sheet.AutoFilters[2];
filter.AddColorFilter(color, ExcelColorFilterType.FontColor);
}
#endregion
break;
case 5:
#region IconFilter
sheet.AutoFilters.FilterRange = sheet["A4:D44"];
ExcelIconSetType iconset = ExcelIconSetType.ThreeSymbols;
int iconId = 0;
int filterIndex = 0;
bool IdNotSelected = false;
switch (this.comboBox3.SelectedIndex)
{
case 0:
iconset = ExcelIconSetType.ThreeSymbols;
filterIndex = 3;
break;
case 1:
iconset = ExcelIconSetType.FourRating;
filterIndex = 1;
break;
case 2:
iconset = ExcelIconSetType.FiveArrows;
filterIndex = 2;
break;
}
switch (this.comboBoxAdv1.SelectedIndex)
{
case 0:
//Do nothing
break;
case 1:
iconId = 1;
break;
case 2:
iconId = 2;
break;
case 3:
if (comboBox3.SelectedIndex == 0)
{
iconset = (ExcelIconSetType)(-1);
}
else
{
iconId = 3;
}
break;
case 4:
if (comboBox3.SelectedIndex == 1)
{
iconset = (ExcelIconSetType)(-1);
}
else
{
iconId = 4;
}
break;
case 5:
iconset = (ExcelIconSetType)(-1);
break;
default:
MessageBox.Show("Please Select an Icon!");
IdNotSelected = true;
break;
}
if (!IdNotSelected)
{
IAutoFilter filter = sheet.AutoFilters[filterIndex];
filter.AddIconFilter(iconset, iconId);
}
#endregion
break;
case 6:
#region AdvancedFilter
IRange filterRange = sheet.Range["A8:G51"];
IRange criteriaRange = sheet.Range["A2:B5"];
if (rdbFilterInPlace.Checked)
{
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, cbisUnique.Checked);
}
else if (rdbFilterCopy.Checked)
{
IRange range = sheet.Range["I7:O7"];
range.Merge();
range.Text = "FilterCopy";
range.CellStyle.Font.RGBColor = Color.FromArgb(0, 112, 192);
range.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
IRange copyRange = sheet.Range["I8"];
sheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyRange, cbisUnique.Checked);
}
#endregion
break;
default:
case 3:
IAutoFilter filter4 = sheet.AutoFilters[1];
filter4.AddDynamicFilter(DynamicFilterType.Quarter1);
break;
}
#region Workbook Save and Close
workbook.SaveAs(outFileName);
workbook.Close();
#endregion
}
