This project has moved. For the latest updates, please go here.

if the month has changed, then write data to a new worksheet

May 25, 2012 at 10:05 AM

task : if the month has changed, then write data to a new worksheet

( old worksheet - save and employ can be see old worksheet )

I tried to create an array of worksheet...

I could not...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ClosedXML.Excel; 

namespace test
{
    class test
    {
        static void Main(string[] args)
        {

            string file = "C:\\array.xls";
            

            int j = DateTime.Now.Month;  

            var wb = new XLWorkbook();

            int k;
            

            Object[] a = new Object[12];
            
            //wb.Worksheets [] b = new wb.Worksheets[12];
            //ClosedXML [] a = new ClosedXML[12]; // error 
            
               a[j] =  wb.Worksheets.Add("Sheet" + j, wb.Worksheets.Count);

               var worksheet = wb.Worksheets.Add("Sheet" + j, wb.Worksheets.Count);

            
            string DATA = "ASDF"; // test write data
            a.Cell(1, 1).Value = DATA; // error
            worksheet.Cell(1, 1).Value = DATA;

            wb.SaveAs(file);
          }
    }
}

please help, me!

Coordinator
May 25, 2012 at 4:04 PM

I don't know what you're trying to do but here's something:

String sheetName = String.Format("Month {0}", DateTime.Now.Month);

IXLWorksheet worksheet;
if (!workbook.Worksheets.TryGetWorksheet(sheetName, out worksheet))
   worksheet = workbook.AddWorksheet(sheetName);

// Use worksheet...

May 29, 2012 at 7:19 AM

Thank you very much!

tell me how to set and the array of sheets ? , please

May 29, 2012 at 3:18 PM

It is not clear to me why you would want to create your own array of sheets. Perhaps you want to save them in an array by month so you can easily access them using the month as the index to the array. If that is what you are trying to do, there are a few problems with your code.

First, your array has 12 elements indexed from 0 to 11 but the month value from DateTime.Now.Month runs from 1 to 12.

Second, you are creating a brand new workbook which has no sheets in it to start with.

Third, I see you figured out that Worksheets is defined as an IEnumerable collection and cannot be accessed with an array with an index []. You can access them by position, but that would assume that the position (which does start from 1 rather than from 0) but that would assume that the position would be guaranteed to be the same as the month.

Fourth, you are doing the Worksheets.Add twice.

Fifth, if you already have worksheets in the file, you need to have a way to determine which month they are. It might be something in the name of the worksheet, or a data element, or the position. But keep in mind that all of those things could get changed by the users. Hopefully they won't change those things, but they could.

Sixth, you don't need to indicate a position when you do the add worksheet, unless you want the worksheet to be in a specific position. Also, Worksheets.Count would be a bad value to use for the position since it could be zero which is not a valid position.

Anyway, here is some code which runs through the existing worksheets and saves them in an array. If a worksheet does not already exist for that month, it creates one. The logic for the GetMonth() is up to you.

string file = "C:\\array.xlsx";
XLWorkbook wb = new XLWorkbook(file);
IXLWorksheet[] wsArray = new IXLWorksheet[13];
int month;
foreach (IXLWorksheet ws in wb.Worksheets)
{
        month = GetMonth(ws); // find the worksheet's month from 1 to 12
        if (month < 1 || month > 12)
        {
                 // error finding the month
        }
        else
        {
                wsArray[month] = ws;
        }
}
month = DateTime.Now.Month;
IXLWorksheet myWs = wsArray[month];
if (myWs == null )
{
        myWs = wb.Worksheets.Add("Sheet " + month);
}