Office Interop Excel C# organize sheets by week [Answered]RSS
2 replies
Last post Sep 11, 2019 01:55 PM by ExceedingLife
‹ Previous Thread|Next Thread ›
‹ Previous Thread|Next Thread ›
Last post Sep 11, 2019 01:55 PM by ExceedingLife
Member
20 Points
128 Posts
Office Interop Excel C# organize sheets by week
Aug 30, 2019 01:58 PM|ExceedingLife|LINK
Hello everyone,
I am currently working on a project that records data to Excel from C#. I am using the Office Interop Excel library.
I currently have the recording of the data correct but it only records all of the data to 1 sheet that I created using code to create the excel file. I would like the project to make new excel files 1 each week and when the new one is created at the beginning of the week I would like the records to be saved to the new excel file. 1 other thing is in my file I would like to center align the value in the merged text box.
public void CreateExcelWorkBook(string filepath) { object misValue = System.Reflection.Missing.Value; // Check if Microsoft Excel is on the computer. if (xlApp == null) { MessageBox.Show("Check if Excel is installed properly on your computer."); return; } try { xlApp.Visible = true; if (File.Exists(filepath)) { return; } else { xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = xlWorkBook.Worksheets[1]; xlWorkSheet.Name = "Scale_Data"; ///Format Column Widths xlWorkSheet.Columns["A"].ColumnWidth = 15; xlWorkSheet.Columns["B"].ColumnWidth = 20; xlWorkSheet.Columns["C"].ColumnWidth = 20; xlWorkSheet.Columns["F"].ColumnWidth = 15; xlWorkSheet.Columns["G"].ColumnWidth = 20; xlWorkSheet.Columns["H"].ColumnWidth = 20; ////Scale Titles Excel.Range scaleTitle = xlWorkSheet.get_Range("A1:C2"); scaleTitle.Merge(); scaleTitle.Value = "Scale 1"; scaleTitle.Font.Bold = true; Excel.Range scaleTitle2 = xlWorkSheet.get_Range("F1:H2"); scaleTitle2.Merge(); scaleTitle2.Value = "Scale 2"; scaleTitle2.Font.Bold = true; ///Add Header Titles xlWorkSheet.Cells[3, 1] = "Project ID"; xlWorkSheet.Cells[3, 2] = "Scale Weight"; xlWorkSheet.Cells[3, 3] = "Time Stamp"; xlWorkSheet.Cells[3, 4] = "S ID"; xlWorkSheet.Cells[3, 6] = "Project ID"; xlWorkSheet.Cells[3, 7] = "Scale Weight"; xlWorkSheet.Cells[3, 8] = "Time Stamp"; xlWorkSheet.Cells[3, 9] = "S ID"; //xlWorkSheet.Cells[1, 4] = "Project"; ///Format HEADER Row Excel.Range headers = xlWorkSheet.get_Range("A3", "I3"); headers = xlWorkSheet.Rows[3]; headers.Interior.Color = Color.Gray; headers.Font.Color = Color.White; headers.Font.Bold = true; xlWorkBook.SaveAs(filepath); } public void AppendExcelWorkBook(string filePath, string data, string project, string sequence) { if (IsExcelOpen(filePath)) { try { /// Get current WorkBook and what's used. xlWorkSheets = xlWorkBook.Worksheets; xlWorkSheet = xlWorkSheets.get_Item("Scale_Data"); Excel.Range range = xlWorkSheet.UsedRange; int columnCount = range.Columns.Count; int rowCount = range.Rows.Count; /// Add New Values to specific rows // Project ID xlWorkSheet.Cells[rowCount + 1, 1] = project; // Scale Weight xlWorkSheet.Cells[rowCount + 1, 2] = data; // Time Stamp xlWorkSheet.Cells[rowCount + 1, 3] = DateTime.Now.ToString(); /// Save on NEW ROW ADDED xlWorkBook.Save(); } catch(Exception ex) { MessageBox.Show(ex.Message); } } else { try { /// Open the file in Excel & setup for appending. OpenExcel(filePath); xlWorkSheets = xlWorkBook.Worksheets; xlWorkSheet = xlWorkSheets.get_Item("Scale_Data"); Excel.Range range = xlWorkSheet.UsedRange; int columnCount = range.Columns.Count; int rowCount = range.Rows.Count; /// Add New Values to specific rows // Project ID xlWorkSheet.Cells[rowCount + 1, 1] = project; // Scale Weight xlWorkSheet.Cells[rowCount + 1, 2] = data; // Time Stamp xlWorkSheet.Cells[rowCount + 1, 3] = DateTime.Now.ToString(); /// Save on NEW ROW ADDED xlWorkBook.Save(); //////Excel.Range titleRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[2, 3]); //////titleRange.Merge(true); ////Excel.Range scaleTitle = xlWorkSheet.get_Range("A1:C2"); //////scaleTitle.Style.HorizontalAlighment = Excel.XlHAlign.xlHAlignCenter; //// //xlWorkSheet.Cells[1, 1].value = "Scale 1"; //////xlWorkSheet.get_Range("A1:C2").Merge(); //Worksheet.Cells[1].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;Contributor
2820 Points
803 Posts
Re: Office Interop Excel C# organize sheets by week
Sep 02, 2019 08:31 AM|Yongqing Yu|LINK
Hi ExceedingLife,
To achieve this function each week, you need to create a console app that does what you're looking for, then use the Windows "Scheduled Tasks" functionality to have that console app executed at the time you need it to run.
You can refer to this link : How to call a method daily, at specific time, in C#?
For this issue, you can add this statement to your code to ensure the cell is in the center :
Here is the result:
Best Regards,
YongQing.
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
20 Points
128 Posts
Re: Office Interop Excel C# organize sheets by week
Sep 11, 2019 01:55 PM|ExceedingLife|LINK
Here is my solution where Worksheet is the Month Number
static DateTime dateText = DateTime.Now.Date; int month = dateText.Month; public void AppendExcelAdvise2(string filePath, string data, string po, string product, string id, string item, int worksheet) { try { /// Determine if Excel Workbook is Open or Not. if (!IsExcelOpen(filePath, worksheet)) { OpenExcel(filePath, worksheet); } xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application"); /// Get the Current WorkBook and WorkSheet. xlWorkSheets = xlWorkBook.Worksheets; xlWorkSheet = xlWorkSheets.get_Item(worksheet); int columnRows = GetRowsForColWS(12, worksheet); //int columnRows = GetRowsForColumn(12); /// Add New Values to their Specific Rows & Columns. // Production Order # xlWorkSheet.Cells[columnRows + 1, 8] = po;