mganss / excelmapper Goto Github PK

Hi There,
I need a similar Mapper for php/symfony in my project. anyone who could tell me if it exist something like that, it will be great.
Thanks a lot.

Could you help put the telegram group link in your readme.md? Thanks in advance!

Telegram group：https://t.me/npoidevs

Hello! Thanks again for the great product, I keep coming back to it.

Would it be possible to add a feature like "start inserting at row n" or "skip rows xyz" when saving a sheet?

I've a template sheet I need to populate where the first row is supposed to be a banner of sorts.

Is there a way to skip none blank rows when reading in? The headers for the spreadsheets I'm working with begin at row 3?

Hey,

is there a way to specify the worksheet to read from, the workbook I need to read from had 4 sheets that can't be deleted?

Cheers.

Hi there

thank you for your mapper.

Is there any ability to map lists within custom objects?

rgds

Ben

I got an issue when write to excel with Column attribute provided, the data is not writing to excel file.
However it is working fine when remove the attributes.

 public class DataItem
    {
        [Ignore]
        public string Bql { get; set; }

        [Ignore]
        public int Id { get; }

        [Column(2)]
        public string OriginalBql { get; }

        [Column(1)]
        public string Title { get; }

        [Column(3)]
        public string TranslatedBql { get; set; }
    }

...

Dear ExcelMapper Developers,

Please help.

How can one fix this project load error -- default XML namespace of the project must be the MSBuild XML namespace -- quickly and completely?

Here are the details.

I just downloaded a zip, unzipped it, opened in Visual Studio 2015, and got the following load errors.

(1)

C:\Users\mfk1234\Documents\DevNotes\ExcelMapper-master\ExcelMapper-master\ExcelMapper\ExcelMapper.csproj : error : The default XML namespace of the project must be the MSBuild XML namespace. If the project is authored in the MSBuild 2003 format, please add xmlns="http://schemas.microsoft.com/developer/msbuild/2003" to the element. If the project has been authored in the old 1.0 or 1.2 format, please convert it to MSBuild 2003 format. C:\Users\abc1234\Documents\DevNotes\ExcelMapper-master\ExcelMapper-master\ExcelMapper\ExcelMapper.csproj

(2)

C:\Users\abc1234\Documents\DevNotes\ExcelMapper-master\ExcelMapper-master\ExcelMapper.Tests\ExcelMapper.Tests.csproj : error : The default XML namespace of the project must be the MSBuild XML namespace. If the project is authored in the MSBuild 2003 format, please add xmlns="http://schemas.microsoft.com/developer/msbuild/2003" to the element. If the project has been authored in the old 1.0 or 1.2 format, please convert it to MSBuild 2003 format. C:\Users\abc1234\Documents\DevNotes\ExcelMapper-master\ExcelMapper-master\ExcelMapper.Tests\ExcelMapper.Tests.csproj

Please advise.

Thanks.

-- Mark Kamoski

...

Not really sure if it should be considered a bug or just a limitation, but if you've got a header row with newlines in the text of the cell, the fetch method throws that column out of the column list. (Why anyone would set a sheet up that way, I don't know; I just work with what they give me.)

It's easily remedied by "ignoring" the header row and mapping via indices, so it's not a pressing issue either way, but it was confusing at first.

The Data source I have been working with has changed from CSV to an Excel Table.
I've been using CSVHelper and now wish to use ExcelMapper.
The Excel data I'm importing contains the string "NULL" instead of an empty null value in the null cells.

For CSVHelper you can define a ConvertUsing helper for the Mapper.
How would you suggest I do the same for ExcelMapper?

    public class QFDataLoad
    {
        public int EventID { get; set; }
        public string EventType { get; set; }
        public DateTime? Reported { get; set; }
    }

    public sealed class QFDataLoadMap : ClassMap<QFDataLoad>
    {
        public QFDataLoadMap()
        {
            Map(m => m.EventID).ConvertUsing(row => { 
                             return int.Parse(row.GetField("Event ID")); });            
            Map(m => m.EventType).Name("Event Type");
            Map(m => m.Started).ConvertUsing(row => { 
                             return CreateDBDateTime(row.GetField("Reported")); });
        }

        public static DateTime? CreateDBDateTime(string date)
        {
            DateTime result;
            if (DateTime.TryParse(date, out result))
            {
                return result;
            }
            return null;
        }

Hi,

Currently column names are index based but it should default to Excel like column name eg: A-Z, AA-AZ ... as these are more intuitive and symmetrical to Excel.

Also, it would be great to skip adding index based columns when Header is already present as it duplicates the whole data.

Thanks

Hi, I was wondering if it's possible to map to dynamic objects.

For example, consider an ETL scenario in which we doesn't know/care what columns a worksheet contains. We just want to get its data and load it into another system.

I looked through the Fetch overloads, but I think all of them require a type parameter...right? We could dynamically create a type and pass it in, but it would be nice if the library did that for us.

I'm imagining a new overload like:

IEnumerable<dynamic> Fetch(string sheetName)

Is there any interest in a PR for this?

I needed to generate a largish Excel file for testing. I created 100K objects using AutoBogus and saved them to a file. With ExcelMapper, the Save operation took 15 minutes (!). The equivalent code with NPOI.Mapper took 10 seconds. Also, the file written by ExcelMapper was corrupt and gave me an error message:

Here's the code I used; I can provide the Excel file if you want

public void CreateFile()
{
    var people = AutoFaker.Generate<Order>(100000);
    var xl = new ExcelMapper();
    xl.Save(@"c:\myfile.xlsx", people);
}

public class Order
{
    public string Subcode { get; set; }
    public string CustomerName { get; set; }
    public uint Control_No { get; set; }
    public uint Web_Id { get; set; }
    public string Applicant { get; set; }
    public string SSN { get; set; }
    public uint COC_Number { get; set; }
    public DateTime Specimen_Date { get; set; }
    public string Lab { get; set; }
    public int ProductID { get; set; }
}

Is there a simple way to not create a header when passing a list of objects? I just want the data. Can it go by column index?
Also is there a way to autofit columns in a sheet?

When converting a cell double date value, the following code

should return DateTime.FromOADate(cell.NumericCellValue) instead of cell.DateCellValue. The return of cell.DateCellValue causes an exception in the NPOI library. Here the Stack Trace:

Exception: Object reference not set to an instance of an object.

at NPOI.XSSF.UserModel.XSSFWorkbook.IsDate1904()
at NPOI.XSSF.UserModel.XSSFCell.get_DateCellValue()
at Ganss.Excel.ExcelMapper.GetCellValue(ICell cell, ColumnInfo targetColumn)
at Ganss.Excel.ExcelMapper.<Fetch>d__54.MoveNext()

Is there a way to pull a string raw value instead of the formula?

Got some fields with formatting (mm:ss) which excel counts as time and can not adjust to text without losing the formatting, then this is used to calculate other times. This is use to show length in minutes and seconds, but gets pulled in as a full dateTime and causing some issues. The field is marked as a string but wanting to just bring in the raw value in the field as written without the formula

Thanks

Thanks for your effort, ExcelMapper is so cool and easy to use, and I'm trying to embed it in some asp net core projects where async I/O support is extremely important in such scenarios, so could we expect some async version of Save() and Fetch() methods like:
await new ExcelMapper().SaveAsync("products.xlsx", products, "Products");
var products = await new ExcelMapper("products.xlsx") { HeaderRow = false }.FetchAsync<Product>();

My problem scenario is i have various kind of excel that i want to read. Ahead of reading i don't know what type of excel it is. I need to determine the type by reading the header columns. So Is there any way to get the column list without specifying the type for example instead of this

var excel = new ExcelMapper("products.xlsx").Fetch<Product>()

Thanks.

It would be nice to use C# 9 record types as POCOs.

I am a relatively new user to this library and I am getting one issue while importing data from excel. if any column of the first row is empty then other row's data for that column is also setting property value to its default value even if there is data in that row for that column. any help would be appreciated.

Hello,

Could you consider adding a constructor to inject the workbook please?

    public ExcelMapper(IWorkbook workbook)
    {
        Workbook = workbook;
    }

I need to verify column headings exist correctly for multiple sheets and have borrowed some code from ExcelMapper that loads in a workbook to do this.

If it were possible to pass this workbook in the constructor of ExcelMapper it would mean not having to load the Excel file for every sheet I want to fetch - I tried with stream but it closes it after the first read.

Thanks

Chris

Hi. Using Win10 in English but my MS Office is in Turkish. Excel Mapper returns null for the values in "I" column. My first guess is the language or regional settings is the culprit because we have an "İ" letter (I with a dot) in Turkish language. Something is messed up. Any chance to fix this up?

Thanks.

Instead of crashing with a System.NullReferenceException when you pass in a sheet name that doesn't exist to Fetch, it would be friendlier to detect it and throw a custom exception. I believe WorkbookFactory returns null when the sheet is not found.
Let me know if this makes sense to you too. I could work on this.

i am trying to save multiple sheets in single excel but there is no option for that. the "Save" extension takes only one IEnumreble .
how to store multiple sheets in single excel
please do the need full

The test ExcelMapperConvertExceptionTests.EmptyConstructorTest checks if the automatic message from the exception is Exception of type '{typeof(ExcelMapperConvertException).FullName}' was thrown., but my installation localizes the message to my language, so the test breaks... I'd argue that this test doesn't add much in terms of coverage, so it shouldn't exist, since it'd be weird to check for localized messages.

Hello!

I've got a spreadsheet that has a formula as its final column (in the header row) that I'm trying to run through my API.

ExcelMapper works great if I delete that column from the spreadsheet, but throws an exception if I leave it in. (Cannot get a text value from a numeric formula cell). I've tried setting the Ignore attribute on my mapping class for the requisite column, but it doesn't seem to help.

Any tips? I would just remove the column from the spreadsheet but it's sent to my system by another department, so I don't have any control over it.

Hello.

Is it possible to realize feature like an append data to the end of the current opened file?
The situation: I have some block of identically structured data. I want to write these blocks each below other (with, possibly, same delimiters). Current realization of the Save method rewrite current saved file:

var saveFile = new ExcelMapper() { HeaderRow = true, };
saveFile.Save<OutputDTO>("outputFile.xlsx", object1);
saveFile.Save<OutputDTO>("outputFile.xlsx", object2);

Thank you! It's a really great package! I spent a lot of time, thinking about realization of my own, but your package covers all of my problems I need to solve. Pls, do not abandon it!

Below is an example excel data -

+----+-----------------+--------------+
| SL |      Date       |      P1      |
+----+-----------------+--------------+
|    | Opening Balance | USD 10254.66 |
|  1 | 01-07-2020      | 445.25       |
|  2 | 01-07-2020      | 234.80       |
|  3 | 02-07-2020      | 13.00        |
|    | Total           | USD 10947.71 |
+----+-----------------+--------------+

Currently my code -

    public static async IAsyncEnumerable<TReceipt> ReadBankEntry(string file)
    {
        using (FileStream stream = new FileStream(file, FileMode.Open, FileAccess.ReadWrite))
        {
            ExcelMapper mapper = new ExcelMapper() 
            { 
                HeaderRowNumer = 0 // zero is the default
                MinRowNumber = 2 // actual data starts from here (skipping row=1)
                // MaxRowNumber = ? this is dynamic and can change in every excel file.
            };
            // my mapping table here
            foreach (TReceipt bankEntry in await mapper.FetchAsync<TReceipt>(stream, "Sheet1"))
            {
                yield return bankEntry;
            }
        };
    }

As you can see in the last row -
Firstly, is there a way to stop reading or skip rows when the first column is empty but the row isn't?
Second, is there a way to stop reading or skip row when one of the columns does not have a certain type? Or perhaps stop reading or skip row when there is an exception?

Hi, How are you?
First of all this library is amazing.
But, I have a Excel with sereral Sheets with 27 columns, for some reason not all columns are returning data, I had to comment out the following line: 283 (.Where (c =>! HeaderRow || (c.CellType == CellType.String &&! string.IsNullOrWhiteSpace (c.StringCellValue) ))) from ExcelMapper.cs so that the library could recognize and return all the data in the spreadsheet, it seems that it is ignoring the property, I saw that there is an issue closing reporting something similar, but I'm using the mapping via index and not by name...
TesteExcelMapper.zip

This is probably a long shot, but how hard would it be to implement a feature where you could apply a single custom mapping rule to multiple columns in one go? For instance, I've got a sheet from another department I need to work with that has a bunch of columns (like 10) that should map to boolean properties, but the sheet itself has strings "Y" or "N" in the columns.

I could use .AddMapping on all of the requisite properties, but then I'm repeating essentially the same code a bunch of times; maybe you have some suggestions on how to handle that?

If it makes sense to add some sort of feature like that to ExcelMapper I'd be happy to take a look at it if you have any thoughts on where to start.

Hi, running same code serially works but running parallel breaks it and throws

Unable to cast object of type 'System.String' to type 'System.DateTime'.

This throws cast exception

but same with serially works

Thanks

netcore support?

Hello,

I encountered an issue when processing an excel file with null rows (not empty rows) between valid rows. While NPOI knows the proper size of the sheet collection, the loop condition while (i <= MaxRowNumber && (row = sheet.GetRow(i)) != null) on line 420 of Fetch(ISheet sheet, Type type, Func<string, object, object> valueParser = null) stops looping through the collection if it hits a null row, even if there are more valid rows later on in the sheet.

This is because the index is still looping through the row keys rather than the collection's physical index, see image for reference, GetRow looks for 19 which is null and never proceeds to 19 the collection since its key is 22.

As a stopgap, in my application I preprocess the file by replacing these null rows with empty rows. Thanks.

Hello,

We need to comply with a mandated requirement of quote prefixing any cell staring with =, +, -, $ or @ to mitigate the risk of Excel Injection.

The application is written in .Net Core and the Excel file is generated a from an API call.

I have implemented this by adding a mapping and it works well until the garbage collection runs.
At this point
var columnIndex = !hasColumnsByIndex ? j : columnsByIndex.First(c => c.Value == getter.Value).Key; on line 438 of ExcelMapper.cs throws an exception because the Sequence contains no matching element
at System.Linq.Enumerable.First[TSource](IEnumerable1 source, Func2 predicate)

The code is comparing the ColumnInfo objects and while they look the same in a debug session they don't match and therefore they're not returned from Linq query. I can get this working reliably by changing the code to compare the property names.

Is there a way to override GenerateCellSetter? I also tried providing a custom TypeMapperFactory but couldn't get that working and it looked like I'd need to duplicate half of the code anyway.

If I created a PR with a new global setting and attribute would you consider merging it into the codebase?

The setting/attribute might be something likeQuotePrefixSuspectValues and if this is set then it uses the following to set the value:

var injectionCharacters = new[] { '=', '-', '+', '@', '%' };
c.SetCellValue(o.ToString().IndexOfAny(injectionCharacters) > -1 ? $"'{o}" : o.ToString());

What do you think?

Thanks

Chris

I would like to record metadata about each cell. For example, I would like to know that particular value was mapped from a cell with a background color of yellow and text formatting was not strike through.

Likewise I would like to be able to set metadata for each value that I want to write to excel so that I can color a cell and format the text according to my own business logic.

Is there any support for this?

Hi, using SkipBlankRows = false but still columns are skipped in the Dynamic object. I have checked this flag from fd36b0b and it worked at one place but not working at other.

Thanks

Hello,

Is ExcelMapper supports CSV format ?

Best regards,

Hi, I would like to know if there is a way to ignore the format of a cell and just take the plain text value.

for example, if I have a cell with value 10-11 of date format then avoid the 11/10/2020 conversion and just get "10-11" string

Thanks!

It looks like only the last mapper.AddMapping<ProductRow>("colname", p => p.propname1).SetPropertyUsing(blabla...) is used.
It would be useful to allow

mapper.AddMapping<ProductRow>("colname", p => p.propname1).SetPropertyUsing(blabla...)
mapper.AddMapping<ProductRow>("colname", p => p.propname2).SetPropertyUsing(blabla2...)

Hello,

I'm using your library (which i find very useful and straightforward !!). I did put my "Excel-To-Core objects" part in a separate module to use it in another project if i had to.

The problem i encountered is that when i attempt to re-parse the data another time (after an insertion failure in my database or a user request), I get a NullReferenceException:
object reference not set to an instance of an object with the following stack trace:
at NPOI.Util.LocaleUtil.GetLocaleCalendar(TimeZone timeZone) at NPOI.Util.LocaleUtil.GetLocaleCalendar() at NPOI.SS.UserModel.DateUtil.GetJavaCalendar(Double date, Boolean use1904windowing, TimeZone timeZone, Boolean roundSeconds) at NPOI.SS.UserModel.DateUtil.GetJavaDate(Double date, Boolean use1904windowing) at NPOI.SS.UserModel.DateUtil.GetJavaDate(Double date) at Ganss.Excel.ExcelMapper.GetCellValue(ICell cell, ColumnInfo targetColumn) at Ganss.Excel.ExcelMapper.<Fetch>d__58.MoveNext() at System.Linq.Enumerable.<OfTypeIterator>d__611.MoveNext()
at System.Linq.Enumerable.WhereEnumerableIterator1.ToList() at

I don't know how to fix this issue. I can provide more informations if needed.
Have a nice day,

Is it possible to start mapping from/to specific sheet row?
I have cases with multi tables in one sheet.

Hi Team,

I'm from NPOI team. I'd like to let you know that NPOI has released 2.4, which supports .NET standard. If you are willing to, you can replace the dotnetcore.NPOI to NPOI package. I think it can help make life easier. :)

It would be nice to have the library use JSON serializer/deserialize by default rather than having to implement it with a custom handler

I'm trying to map a class without property atrributes to load a Excel sheet. Here is my approach:

var excel = new ExcelMapper(filePath);
excel.AddMapping<Model>(0, p => p.Property1);
excel.AddMapping<Model>(1, p => p.Property2);
...
var result = excel.Fetch<Model>();

and

var excel = new ExcelMapper();
excel.AddMapping<Model>(0, p => p.Property1);
excel.AddMapping<Model>(1, p => p.Property2);
...
var result = excel.Fetch<Model>(filePath, 0);

In both cases the mapping has no effect, The mapper resolves the properties using the column names in the first row. Tried with a multilanguage excel file with same format but different column names.

Am I doing something wrong? Any help is welcomed!

Hello,

Thank you for your work! Is there a reason that you have implemented your own CustomAttribute and not used the one from System.ComponentModel.DataAnnotations.Schema namespace?

Is there a way to ignore empty cells and not add a value? My problem is that if i have a column with a decimal it will add 0 to it or if i have a date column that is empty it will add a date. I know i can use a null able value for some items but that wont work for required items in the class.

My end goal is to allow the user to upload the excel file and then catch issues with the excel upload with data annotations. So if they accidentally leave a column blank i don't want a value there. Then i can use model state validation with data annotations to log the errors and display it back to the user.

How do you set style just in the header? Like bold word or diferent font size just in header?

Hello again!

Nowadays I write ASP.NET Core backend. So, I want to use your library as a servic, but if I want to use annotaions for columns, I should strongly add a reference to the ExcelMapper library in the main application, not only in my service realization. And I cannot "wrap" Column attribute by my own specifications derived from ColumnAttribute because ColumnAttribute class is marked as "sealed".
Is it possible to "unseal" it? Or maybe you know another elegant solution for this problem?

Thank you!

We are using the ExcelMapper class like so:

var excelMapper = new ExcelMapper(payload.FileStream);
excelMapper.SkipBlankRows = true;
ProductMapper.Configure(excelMapper);
Payload.Items = excelMapper.Fetch<ImportModel>().ToList();

Our ImportModel class looks like this:

public class ImportModel
{
	[Name("Color", "COLOR", "Color Name", "ColorName")]
	public string Color { get; set; }
}

The problem is, sometimes our Excel documents header columns contain one or more trailing whitespace. For example, instead of the column header being Color Name it will read Color Name .

Is there any way to use ExcelMapper to trim whitespace such that the column will still be correctly identified on Fetch()?

When importing a file with Guidvalues, it crashes on the SetProperty method in TypeMapper.cs.
It throws the following error: Invalid cast from 'System.String' to 'System.Guid'
How it is now:

public void SetProperty(object o, object val)
        {
            object v;
            if (SetProp != null)
                v = SetProp(val);
            else if (IsNullable && (val == null || (val is string s && s.Length == 0)))
                v = null;
            else
                v = Convert.ChangeType(val, PropertyType, CultureInfo.InvariantCulture);
            Property.SetValue(o, v, null);
        }

How I fixed it:

public void SetProperty(object o, object val)
        {
            object v;
            if (SetProp != null)
                v = SetProp(val);
            else if (IsNullable && (val == null || (val is string s && s.Length == 0)))
                v = null;
            else if (PropertyType == typeof(Guid))
                v = Guid.Parse(val.ToString());
            else
                v = Convert.ChangeType(val, PropertyType, CultureInfo.InvariantCulture);
            Property.SetValue(o, v, null);
        }

It still throws an error for invalid Guids this way.

Anyway, Awesome work!