...
If you have a large area that you want to autofit, and do not know beforehand how wide the cell contents might be, it will be much faster to find the longest string and then set the width of the columns in characters. For example, if you have several thousand rows, then you could say:
Code Block |
---|
//Used toPOSSIBLE store the maximum length, in characters int maxChars = 0CORRECT CODE: DataTable dts = GetData(Page.MapPath(@"data\PersonsInfoV2.csv")); // Column on which the custom autofit starts, to be set by user. int startingColumn = int.Parse(dts.Rows[0][9].ToString()); //Loop throughColumn each row of data for (int rowon which the custom autofit ends, to be set by user. (Should be set to the row AFTER the last column to be autofit.) int endColumn = int.Parse(dts.Rows[1][9].ToString()); // Array of the longest variables in each column. int[] longest; // Temporary variable for keeping track of current location int temp = 0; row < lastRow; row++) // The loop to check on the width of each column. Only start if the given starting and ending places are valid. if ((startingColumn < dts.Columns.Count)&&(endColumn < dts.Columns.Count)&&(startingColumn < endColumn)) { //If thisFind cellthe containssize moreof charactersthe thanarray, based on the starting location. longest = new int[endColumn - startingColumn]; //our previous maximum, then update maxChars For each row in the DataTable... foreach (DataRow row in dts.Rows) { // For each column, based on that row... maxChars = Math.Max( for (int i = startingColumn; i < endColumn; i++) { // Find the temporary length of that row. ws.Cells[row, columnNumber].Value. temp = row[i].ToString().Length,; maxChars); } //Get Is it the columnlongest propertiesin for the column weso want to adjust the width of ColumnProperties columnProperties = ws.GetColumnProperties(columnNumber); //Set the width in characters to the maximum //number of characters a cell in the column has columnProperties.WidthInChars = maxChars;far? If yes, set it as such. (Longest subtracts startColumn from index to keep starting index at 0.) if (temp > longest[i-startingColumn]) { longest[i-startingColumn] = temp; } } } // After looping through, set the width of each column to the longest. // You can change this function to change where in the output file for (int i = startingColumn; i < endColumn; i++) { ColumnProperties columnProperties; columnProperties = ws.GetColumnProperties(i); columnProperties.WidthInChars = longest[i-startingColumn]; } } |
Code Block | ||
---|---|---|
| ||
'Used to store the maximum length, in characters Dim maxChars As Integer = 0 'Loop through each row of data For row As Integer = 0 To lastRow 'If this cell contains more characters than 'our previous maximum, then update maxChars maxChars = Math.Max( _ ws.Cells(row, columnNumber).Value.ToString().Length, _ maxChars) Next 'Get the column properties for the column we want to adjust the width of Dim columnProperties As ColumnProperties = ws.GetColumnProperties(columnNumber) 'Set the width in characters to the maximum 'number of characters a cell in the column has columnProperties.WidthInChars = maxChars |
...