Interop Excel C# Create a new worksheet for each week

-1

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 all the data correct and stored into 1 worksheet. I would like the project to make new excel Worksheet every week. Store the data in a new worksheet by week. Here is what I currently have:

   //Create new Excel WorkBook
        public void CreateExcelWorkBook(string filepath)
        {
            object misValue = System.Reflection.Missing.Value;            
            try
            {
                //xlApp.Visible = true;
                if (File.Exists(filepath)) { return; }
                else
                {
                    xlWorkBook = xlApp.Workbooks.Add(misValue);
                    xlWorkSheet = xlWorkBook.Worksheets[1];
                    xlWorkSheet.Name = "Week1";
                    ////Scale Titles                    
                    Excel.Range scaleTitle = xlWorkSheet.get_Range("A1:C2");
                    scaleTitle.Merge();
                    scaleTitle.Value = "Scale 1";
                    scaleTitle.Font.Bold = true;
                    scaleTitle.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                    ///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";                    
                    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);
                }
            }
            catch (Exception ex){ MessageBox.Show(ex.Message); }
        }

public void AppendAdvise2Excel(string path, string data, string project, string sequence)
        {
            try
            {   /// Determine if Excel Workbook is Open or Not.
                if (!IsExcelOpen(path)) { OpenExcel(path); }
                xlWorkSheets = xlWorkBook.Worksheets;
                xlWorkSheet = xlWorkSheets.get_Item("Week1");
                Excel.Range xlRange = xlWorkSheet.UsedRange;
                int columnCount = xlRange.Columns.Count;
                int rowCount = xlRange.Rows.Count;
                int columnRows = GetRowsForColumn(7);
                /// Add New Values to their Specific Rows & Columns.
                xlWorkSheet.Cells[columnRows + 1, 1] = project;
                xlWorkSheet.Cells[columnRows + 1, 2] = data;
                xlWorkSheet.Cells[columnRows + 1, 3] = DateTime.Now.ToString();
                xlWorkSheet.Cells[columnRows + 1, 4] = sequence;
                /// Save the Newly Added Row.
                xlWorkBook.Save();
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }
 private int GetRowsForColumn(int columnNumber)
        {
            int columnCount = 0;
            try
            {
                Excel.XlDirection goUp = Excel.XlDirection.xlUp;
                xlWorkSheets = xlWorkBook.Worksheets;
                xlWorkSheet = xlWorkSheets.get_Item("Week1");

                columnCount = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, columnNumber].End(goUp).Row;
            }
            catch(Exception ex) { MessageBox.Show(ex.Message); }
            return columnCount;
        }
public void OpenExcel(string filePath)
        {           
            try
            {
                if (File.Exists(filePath))
                {   // File is created and already opened
                    if (IsExcelOpen(filePath)) { return; }
                    else
                    {   // File is created but not open, Opening now.
                        xlWorkBook = xlApp.Workbooks.Open(filePath);
                        xlWorkSheets = xlWorkBook.Worksheets;
                        xlWorkSheet = xlWorkSheets.get_Item(1);
                    }
                }
                else
                {   // File is NOT created, Call Create()
                    CreateExcelWorkBook(filePath);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                //MessageBox.Show("Error Opening/Creating Excel work book.");
            }
        }
        // Check if Excel is Open or Not.
        public bool IsExcelOpen(string filepath)
        {
            bool isValid = true;

            try
            {   //System.Runtime.InteropServices.COMException: 'Operation unavailable (Exception from HRESULT: 0x800401E3 (MK_E_UNAVAILABLE))'
                xlApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
            if (File.Exists(filepath)) {lApp.Workbooks.get_Item("Scale_Data"); }
                else { isValid = false; }
            }
            catch (Exception ex) { isValid = false; }
            return isValid;
        }

HERE is my SOLUTION! where Worksheet is the Month Number.

 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;
c#
winforms
office-interop
excel-interop
asked on Stack Overflow Aug 30, 2019 by Bigbear • edited Sep 11, 2019 by Bigbear

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0