Optional OmitColumns parameter on excel WriteRange

Hi,

This would be best with RowNumber, but has merits on it’s own too.

Would it be possible to add an optional int OmitColumns parameter to WriteRange? In the array one would specify column indexes to omit in the WriteRange.

Right now if you have a complex DT where you want to write most of it, but not all, the only way is to either create a deep copy and delete the columns, build a new DT column by column, or write it in chunks using WriteValue (which is painfully slow due to saving after each write).

Most common usage scenario is when one is keeping track of some additional data with the rows (be it original row number, or something completely different) or when you’re doing a Read->Process->Write cycle like this:

  1. Assume a sample Excel with 10000 rows, 8 columns. Last column has only header (Status) and no values.
  2. Read the excel to DT.
  3. When processing update the Status column in the DT.
  4. After processing all rows, use WriteRange with OmitColumns(Enumerable.Range(0,7)) and StartingCell=“H2”. This would perform a batch write of 10000 values. Using the same with WriteValue, especially for network shared files, is not even close in speed.

Regards.

2 Likes

Wow, what an ancient post!

I could use the option to omit colums on WriteRange right now. Good suggestion.