Page tree

Versions Compared


  • This line was added.
  • This line was removed.
  • Formatting was changed.


Code Block

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());

// Column on 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;

// 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))
    // Find the size of the array, based on the starting location.
    longest =  new int[endColumn - startingColumn];

    // For each row in the DataTable...
    foreach (DataRow row in dts.Rows)
        // For each column, based on that row...
        for (int i = startingColumn; i < endColumn; i++)
            // Find the temporary length of that row.
            temp = row[i].ToString().Length;

            // Is it the longest in the column so 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, _

'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

While this will not be as accurate as AutoFitWidth, it will be significantly faster.