Open XML stores dates as the number of days from 1 Jan 1900. Well, skipping the incorrect 29 Feb 1900 as a valid day. You should be able to find out algorithms to help you calculate the correct value. I believe some developers use DateTime.FromOADate() as a helper. Also, the Cell class has the DataType … Read more
I wrote this quick example. It works for me. I only tested it with one dataset with one table inside, but I guess that may be enough for you. Take into consideration that I treated all cells as String (not even SharedStrings). If you want to use SharedStrings you might need to tweak my sample … Read more
The s attribute references a style xf entry in styles.xml. The style xf in turn references a number format mask. To identify a cell that contains a date, you need to perform the style xf -> numberformat lookup, then identify whether that numberformat mask is a date/time numberformat mask (rather than, for example, a percentage … Read more
I think this should do what you’re asking. The other function is there just to deal with if you have shared strings, which I assume you do in your column headers. Not sure this is perfect, but I hope it helps. static void Main(string[] args) { DataTable dt = new DataTable(); using (SpreadsheetDocument spreadSheetDocument = … Read more
If you use a MemoryStream you can save the changes to a new file like this: byte[] byteArray = File.ReadAllBytes(“c:\\data\\hello.docx”); using (MemoryStream stream = new MemoryStream()) { stream.Write(byteArray, 0, (int)byteArray.Length); using (WordprocessingDocument wordDoc = WordprocessingDocument.Open(stream, true)) { // Do work here } // Save the file with the new name File.WriteAllBytes(“C:\\data\\newFileName.docx”, stream.ToArray()); }
This makes sense since Excel will not store a value for a cell that is null. If you open your file using the Open XML SDK 2.0 Productivity Tool and traverse the XML down to the cell level you will see that only the cells that have data are going to be in that file. … Read more
All strings in an Excel worksheet are stored in a array like structure called the SharedStringTable. The goal of this table is to centralize all strings in an index based array and then if that string is used multiple times in the document to just reference the index in this array. That being said, the … Read more
Take a look at Linq-to-Excel. It’s pretty neat. var book = new LinqToExcel.ExcelQueryFactory(@”File.xlsx”); var query = from row in book.Worksheet(“Stock Entry”) let item = new { Code = row[“Code”].Cast<string>(), Supplier = row[“Supplier”].Cast<string>(), Ref = row[“Ref”].Cast<string>(), } where item.Supplier == “Walmart” select item; It also allows for strongly-typed row access too.