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 }