Int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference)) Gets the column index of the cell with data (Note: I did not test the code below, but the general idea should help): DataRow tempRow = dt.NewRow() If it is less than then you add blank data to your tempRow, otherwise just read in the value contained in the cell. Then you can iterate over the Cells and check to see what the cell reference is compared to the columnIndex. If (index = 0 & colLetters.Count() = 2)ĬolumnIndex = columnIndex = null ? (indexValue + 1) * 26 : columnIndex + ((indexValue + 1) * 26) ĬolumnIndex = columnIndex = null ? indexValue : columnIndex + indexValue The first letter of a two digit column needs some extra calculations Int? indexValue = Letters.IndexOf(col1.ElementAt(index)) If (colLetters.Count() col1 = colLetters.ElementAt(index).ToCharArray().ToList()
String colLetters = Regex.Split(columnName, "(+)") ĬolLetters = colLetters.Where(s => !string.IsNullOrEmpty(s)).ToArray() Public static int? GetColumnIndexFromName(string columnName) / Zero based index if the conversion was successful otherwise null / A length of three can be implemented when needed. / Note: This method will only handle columns with a length of up to two (ie. / Given just the column name (no row index), it will return the zero based column index. Match match = regex.Match(cellReference) Create a regular expression to match the column name portion of the cell name. Public static string GetColumnName(string cellReference) / Given a cell name, parses the specified cell to get the column name.
to do that you will need two utility functions to get the Column Name from the cell reference and to then translate that column name into a zero based index: private static List Letters = new List() To get the functionality that you need, you can traverse over the Cells as you are doing above, but you will need to check what the value the Cell is referencing and determine if any Cells have been skipped. The data saved corresponds to A1 and C1 and that no cells with null values are saved. Here you will see that the data corresponds to the first row and that only two cells worth of data are saved for that row. I then opened up the excel document in the Open XML Productivity Tool and here is the XML that was stored: I made an example excel document with a string in cell reference A1 and C1.
Your options are to insert blank data in the range of cells you are going to traverse or programmatically figure out a cell was skipped and adjust your index appropriately. 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. This makes sense since Excel will not store a value for a cell that is null.
0 kommentar(er)
