removing a row in spreadsheetgear from a range

You can't do this   range( "1:1").delete - you will get an error that s/sh gear can only delete a full row

You can't refer to the underlying location values

you have to refer to the row of the range as if the range is a full spreadsheet

this is what I did

 public static IRange RemoveHeader(IRange range)

        {

            try

            {

                string startingCol ="a";

                string endingCol = GetExcelColumnName(range.ColumnCount);

                int startRow =  2;

                int endRow = range.RowCount;

                string newrange = string.Format("{0}{1}:{2}{3}", startingCol, startRow, endingCol, endRow);

                if (!range.Union(range.Range[newrange]).Equals(range))

                {

                    throw new EExcelUtilsRangeOutOfBoundsOfData();

                }

                return range[newrange];

            }

            catch

            {

                throw new EExcelUtilsRangeOutOfBoundsOfData();

            }

        }

Spreadsheetgear Issue with Macros

S/shgear lists macros under namedranges

this makes no sense to me

I had to do this to weed them out:

SpreadsheetGear.IRange tempName = name.RefersToRange;

                    //tempName.Name!= null avoids adding macros

                    //wish s/sh gear had a better way of identifying name type

                    if (tempName != null && tempName.Name!= null)

                    {

delete issue in spreadsheetgear

There is an apparent issue.

When we delete a row from a range (e.g. someRange["1:1"].Delete()) this does not make the range 1 row smaller but keeps an empty row at the end of the range

e.g.  when we save the range this is what we get:

-13,     test-13   ,-13,  test-13    ," ""  -13   ""","  gdfgdf  test-13-t,rim   ",2001-12-29,1.7

,,,,,,,   -- new row

This is not Microsoft excel behavior – excel makes the range smaller.

How to tell if a chosen range is in the used section of spreadsheet

this is what I did:
I unioned the existing used range with chosen range and checked for equality

if (!worksheet.UsedRange.Union(chosenrange).Equals(worksheet.UsedRange))
throw new EExcelUtilsRangeOutOfBoundsOfData();

Unformattable dates in s/sh gear and excel

if you enter a value such as "123456789" in excel and format it to a date - excel cannot show it so it displays "######################". If you save to csv , excel will save "######################". spreadsheetgear will save nothing to csv.

Anomolies of Spreadsheetgear with dates

1)Spreadsheetgear converts "-" into "/"
                so if you have a spreadsheet with a date thus

2011-01-03

                  Spreadsheetgear will convert it to
                                 
                                      2011/01/03

2) Spreadsheetgear describes DD/MM/YYYY,MM/DD/YYYY and YYYY/MM/DD with "\/", but not
YYYY-MM-DD

therefore

 
excel format:DD/MM/YYYY sh/g format:dd\/mm\/yyyy - 01/01/2011
excel format:MM/DD/YYYY sh/g format:mm\/dd\/yyyy - 02/01/2011
excel format:YYYY-MM-DD sh/g format:yyyy/mm/dd - 2011/01/03
excel format:DD-MMM-YYYY sh/g format:dd/mmm/yyyy - 04/Jan/2011
excel format:YYYY/MM/DD sh/g format:yyyy\/mm\/dd - 2011/01/05

3) a date coming from a CSV with a value of 04-Jan-2011 will be converted to d-mmm-yy, 4-Jan-11

4) an excel file that has dates formatted as "Date" will be read by excel with the default culture. Therefore, if you open the file with your own custom culture - this will be the format of the "Date" cells.

Spreadsheetgear and dates

when you import dates from a s/sh , then if the format is set SG will get the value and format and allow you to acces the text. if however you are importing a csv - it will attempt to convert to the current culture

how to deal with a different format than the current culture:

CultureInfo TempCulture = (CultureInfo)Thread.CurrentThread.CurrentCulture.Clone();

                    TempCulture.DateTimeFormat.ShortDatePattern = dateFormat;

                    //Thread.CurrentThread.CurrentCulture = TempCulture;

                    _wb = SpreadsheetGear.Factory.GetWorkbookSet(TempCulture).Workbooks.OpenFromStream(stream);

                    _data = _wb.GetDataSet(SpreadsheetGear.Data.GetDataFlags.FormattedText );

 

when converting to datatables make sure to get the formatted text:

DataSet dataSet = wb.GetDataSet(SpreadsheetGear.Data.GetDataFlags.FormattedText );

likewise:

range.CopyFromDataTable(dt, SpreadsheetGear.Data.SetDataFlags.AllText);