Can you paste a block of cells in one shot using Excel Interop w/o using the clipboard?

I'm trying to transfer data from a DataSet into an Excel workbook. Unfortunately, I need more control than I can get by simply linking to Excel via ADO.NET and using standard SQL to select and insert the data, so I'm using excel interop.

My original algorithm involved looping through all the tables/rows/items of the data set and individually setting the Formula of each cell in Excel. This worked, but it was taking nearly half a minute to transfer all the data.

I decided to开发者_如何学Python try a different solution: convert each table to a tab-delimited string (using a combination of StringBuilder and string.Join(), copying the string to the clipboard, and using interop to call the Paste command on the Excel Worksheet object.

This also works and cuts the time by a little more than 50%, but I'm a little paranoid about using the clipboard to transfer data. What happens if the user starts doing other things involving the clipboard while the transfer is occurring? I also wonder if it might be even faster if I could paste a string directly rather than having to use the clipboard as an intermediary.

So, that's my question...is there some command available that would allow me to "paste" a block of data at once in Excel directly from a C# string, without without having to use the clipboard?

---

Create a 2-dimensional object array with the same dimensions as your target range (the first array index is the row count; the second is the column count). Then set the range's value property with that array.

Example:

void SetRange(Worksheet worksheet, DataSet dataSet)
{
    object[] values = GetValuesFromDataSet(dataSet);
    int rowCount = values.GetUpperBound(0);
    int columnCount = values.GetUpperBound(1);
    Range range = worksheet.Range(worksheet.Cells(1, 1), worksheetCells.(rowCount, columnCount));
    range.Value = values;
}

---

You can use HttpResponse object and its write() method to flush the string into excel. Create an excel template string first. Refer to this example.

private static string getWorkbookTemplate()
{
    StringBuilder sb = new StringBuilder(818);
    sb.AppendFormat(@"<?xml version=""1.0""?>{0}", Environment.NewLine);
    sb.AppendFormat(@"<?mso-application progid=""Excel.Sheet""?>{0}", Environment.NewLine);
    sb.AppendFormat(@"<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""{0}", Environment.NewLine);
    sb.AppendFormat(@" xmlns:o=""urn:schemas-microsoft-com:office:office""{0}", Environment.NewLine);
    sb.AppendFormat(@" xmlns:x=""urn:schemas-microsoft-com:office:excel""{0}", Environment.NewLine);
    sb.AppendFormat(@" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""{0}", Environment.NewLine);
    sb.AppendFormat(@" xmlns:html=""http://www.w3.org/TR/REC-html40"">{0}", Environment.NewLine);
    sb.AppendFormat(@" <Styles>{0}", Environment.NewLine);
    sb.AppendFormat(@"  <Style ss:ID=""Default"" ss:Name=""Normal"">{0}", Environment.NewLine);
    sb.AppendFormat(@"   <Alignment ss:Vertical=""Bottom""/>{0}", Environment.NewLine);
    sb.AppendFormat(@"   <Borders/>{0}", Environment.NewLine);
    sb.AppendFormat(@"   <Font ss:FontName=""Verdana"" x:Family=""Swiss"" ss:Size=""12"" ss:Color=""#0000A0""/>{0}", Environment.NewLine);
    sb.AppendFormat(@"   <Interior/>{0}", Environment.NewLine);
    sb.AppendFormat(@"   <NumberFormat/>{0}", Environment.NewLine);
    sb.AppendFormat(@"   <Protection/>{0}", Environment.NewLine);
    sb.AppendFormat(@"  </Style>{0}", Environment.NewLine);
    sb.AppendFormat(@"  <Style ss:ID=""s62"">{0}", Environment.NewLine);
    sb.AppendFormat(@"   <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""{0}", Environment.NewLine);
    sb.AppendFormat(@"    ss:Bold=""1""/>{0}", Environment.NewLine);
    sb.AppendFormat(@"  </Style>{0}", Environment.NewLine);
    sb.AppendFormat(@"  <Style ss:ID=""s63"">{0}", Environment.NewLine);
    sb.AppendFormat(@"   <NumberFormat ss:Format=""Short Date""/>{0}", Environment.NewLine);
    sb.AppendFormat(@"  </Style>{0}", Environment.NewLine);
    sb.AppendFormat(@" </Styles>{0}", Environment.NewLine);
    sb.Append(@"{0}\r\n</Workbook>");
    return sb.ToString();
}

private static string replaceXmlChar(string input)
{
    input = input.Replace("&", "&amp");
    input = input.Replace("<", "&lt;");
    input = input.Replace(">", "&gt;");
    input = input.Replace("\"", "&quot;");
    input = input.Replace("'", "&apos;");
    return input;
}

private static string getCell(Type type, object cellData)
{
    Object data = (cellData is DBNull) ? "" : cellData;
    if (type.Name.Contains("Int") || type.Name.Contains("Double") || type.Name.Contains("Decimal")) return string.Format("<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>", data);
    if (type.Name.Contains("Date") && data.ToString() != string.Empty)
    {
        return string.Format("<Cell ss:StyleID=\"s63\"><Data ss:Type=\"DateTime\">{0}</Data></Cell>", Convert.ToDateTime(data).ToString("yyyy-MM-dd"));
    }
    return string.Format("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", replaceXmlChar(data.ToString()));
}
private static string getWorksheets(DataSet source)
{
    StringWriter sw = new StringWriter();
    if (source == null || source.Tables.Count == 0)
    {
        sw.Write("<Worksheet ss:Name=\"Sheet1\">\r\n<Table>\r\n<Row><Cell><Data ss:Type=\"String\"></Data></Cell></Row>\r\n</Table>\r\n</Worksheet>");
        return sw.ToString();
    }
    foreach (DataTable dt in source.Tables)
    {
        if (dt.Rows.Count == 0)
            sw.Write("<Worksheet ss:Name=\"" + replaceXmlChar(dt.TableName) + "\">\r\n<Table>\r\n<Row><Cell  ss:StyleID=\"s62\"><Data ss:Type=\"String\"></Data></Cell></Row>\r\n</Table>\r\n</Worksheet>");
        else
        {
            //write each row data                
            int sheetCount = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if ((i % rowLimit) == 0)
                {
                    //add close tags for previous sheet of the same data table
                    if ((i / rowLimit) > sheetCount)
                    {
                        sw.Write("\r\n</Table>\r\n</Worksheet>");
                        sheetCount = (i / rowLimit);
                    }
                    sw.Write("\r\n<Worksheet ss:Name=\"" + replaceXmlChar(dt.TableName) +
                             (((i / rowLimit) == 0) ? "" : Convert.ToString(i / rowLimit)) + "\">\r\n<Table>");
                    //write column name row
                    sw.Write("\r\n<Row>");
                    foreach (DataColumn dc in dt.Columns)
                        sw.Write(string.Format("<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">{0}</Data></Cell>", replaceXmlChar(dc.ColumnName)));
                    sw.Write("</Row>");
                }
                sw.Write("\r\n<Row>");
                foreach (DataColumn dc in dt.Columns)
                    sw.Write(getCell(dc.DataType, dt.Rows[i][dc.ColumnName]));
                sw.Write("</Row>");
            }
            sw.Write("\r\n</Table>\r\n</Worksheet>");
        }
    }

    return sw.ToString();
}
public static string GetExcelXml(DataTable dtInput, string filename)
{
    string excelTemplate = getWorkbookTemplate();
    DataSet ds = new DataSet();
    ds.Tables.Add(dtInput.Copy());
    string worksheets = getWorksheets(ds);
    string excelXml = string.Format(excelTemplate, worksheets);
    return excelXml;
}

public static string GetExcelXml(DataSet dsInput, string filename)
{
    string excelTemplate = getWorkbookTemplate();
    string worksheets = getWorksheets(dsInput);
    string excelXml = string.Format(excelTemplate, worksheets);
    return excelXml;
}

public static void ToExcel(DataSet dsInput, string filename, HttpResponse response)
{
    string excelXml = GetExcelXml(dsInput, filename);
    response.Clear();
    response.AppendHeader("Content-Type", "application/vnd.ms-excel");
    response.AppendHeader("Content-disposition", "attachment; filename=" + filename);
    response.Write(excelXml);
    response.Flush();
    response.End();
}

public static void ToExcel(DataTable dtInput, string filename, HttpResponse response)
{
    DataSet ds = new DataSet();
    ds.Tables.Add(dtInput.Copy());
    ToExcel(ds, filename, response);
}

---

Can you not just create a CSV string, and use:

File.WriteAllText()

---

I recommend creating a library in VB.NET which supports optional parameters (cleaner code), and then call it from C#. Like the other answers it all depends on the Range.Value2 calls.

here is some sample code in VB.NET

Public Function GetObjectArray(ByVal range_ref As Range) As Object(,)
    If range_ref.Count > 0 Then
        Return CType(range_ref.Value2, Object(,))
    Else
        Return New Object(,) {{range_ref.Value2}}
    End If
End Function

Public Sub SetObjectArray(ByVal range_ref As Range, ByVal values As Object(,))
    If range_ref.Count > 0 Then
        range_ref.Value2 = values
    Else
        range_ref.Value2 = values(0, 0)
    End If
End Sub

The reason for checking the count, is because for a singular reference .Value2 returns a value and not an array that can be cast to object[,],

You can actually convert the object[,] array back and from a double[,] array using a trick posted here with Array.Copy().

Public Function GetValueArray(ByVal range_ref As Range) As Double(,)
    Dim temp As Object(,) = GetObjectArray(range_ref)
    Dim N As Integer = temp.GetLength(0)
    Dim M As Integer = temp.GetLength(1)
    Dim res As Double(,) = New Double(N - 1, M - 1) {}
    Array.Copy(temp, res, temp.Length)
    Return res
End Function

Public Sub SetValueArray(ByVal range_ref As Range, ByVal values As Double(,))
    Dim N As Integer = values.GetLength(0)
    Dim M As Integer = values.GetLength(1)
    Dim temp As Object(,) = Array.CreateInstance( _
        GetType(Object), _
        New Integer() {N, M}, _
        New Integer() {1, 1})
    Array.Copy(values, temp, values.Length)
    SetObjectArray(range_ref, temp)
End Sub