Export the dataGridView to Excel with all the cells format
I have this code that I know that it works fast
CopyAlltoClipboard(dataGridViewControl);
Microsoft.Office.Interop.Excel.Application xlexcel;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlexcel = new Excel.Application();
xlexcel.Visible = true;
xlWorkBook = xlexcel.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Name = page.Name;
Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Range["A1"]).EntireColumn.Delete(null); // delete the first column that has rows indexes
xlWorkBook.SaveAs(fileName);
private void CopyAlltoClipboard(DataGridView dataGridViewControl)
{
dataGridViewControl.SelectAll();
DataObject dataObj = dataGridViewControl.GetClipboardContent();
if (dataObj != null)
Invoke((Action)(() => { Clipboard.SetDataObject(dataObj); }));
}
The code works fine, but it does copy only the values is the excel, doesn't copy also the cells format (wrap text, backcolor, font, borders etc) Can anyone help me on this one? How to complete this code the have the exact format like in DataGridView?
Solution 1:
Update: Now available in GitHub: https://github.com/MeaningOfLights/DataGridToHTML
I'm struggling to understand why this isn't a duplicate. There are examples all over the net and here.
To my surprise & after a lot of research there are no thorough examples of Exporting DataGridView to HTML or Excel with formatting anywhere on the internet - until now :)
Looking at this code in your question you have found out how slow it is to copy large datasets with Interop and have opted to use the Clipboard instead:
dataGridViewControl.SelectAll();
DataObject dataObj = dataGridViewControl.GetClipboardContent();
if (dataObj != null)
Invoke((Action)(() => { Clipboard.SetDataObject(dataObj); }));
The crux of this question is - using the Clipboard on a DataGridView does not contain the Cell Formatting. Because the clipboard doesn't contain the formatting you're back to the original slow performance problem of having to set Cell Styles individually, which using Interop is very, very slow.
In this case it might work better for your project to create Excel files using XML instead of Interop. While I first thought this would be a good workaround and the other answer here by DartAlex demonstrates that, I thought I'd code up an answer you can use with the Clipboard method. Getting a HTML Copy of the DataGridView with formatting and pasting that into Excel:
DataGridView To HTML Table with Formatting and then into Excel
//====================================================
//DataGridView Export To HTML by Jeremy Thompson: https://stackoverflow.com/questions/39210329/
//====================================================
public string ConvertDataGridViewToHTMLWithFormatting(DataGridView dgv)
{
StringBuilder sb = new StringBuilder();
//create html & table
sb.AppendLine("<html><body><center><table border='1' cellpadding='0' cellspacing='0'>");
sb.AppendLine("<tr>");
//create table header
for (int i = 0; i < dgv.Columns.Count; i++)
{
sb.Append(DGVHeaderCellToHTMLWithFormatting(dgv, i));
sb.Append(DGVCellFontAndValueToHTML(dgv.Columns[i].HeaderText, dgv.Columns[i].HeaderCell.Style.Font));
sb.AppendLine("</td>");
}
sb.AppendLine("</tr>");
//create table body
for (int rowIndex = 0; rowIndex < dgv.Rows.Count; rowIndex++)
{
sb.AppendLine("<tr>");
foreach (DataGridViewCell dgvc in dgv.Rows[rowIndex].Cells)
{
sb.AppendLine(DGVCellToHTMLWithFormatting(dgv, rowIndex, dgvc.ColumnIndex));
string cellValue = dgvc.Value == null ? string.Empty : dgvc.Value.ToString();
sb.AppendLine(DGVCellFontAndValueToHTML(cellValue, dgvc.Style.Font));
sb.AppendLine("</td>");
}
sb.AppendLine("</tr>");
}
//table footer & end of html file
sb.AppendLine("</table></center></body></html>");
return sb.ToString();
}
//TODO: Add more cell styles described here: https://msdn.microsoft.com/en-us/library/1yef90x0(v=vs.110).aspx
public string DGVHeaderCellToHTMLWithFormatting(DataGridView dgv, int col)
{
StringBuilder sb = new StringBuilder();
sb.Append("<td");
sb.Append(DGVCellColorToHTML(dgv.Columns[col].HeaderCell.Style.ForeColor, dgv.Columns[col].HeaderCell.Style.BackColor));
sb.Append(DGVCellAlignmentToHTML(dgv.Columns[col].HeaderCell.Style.Alignment));
sb.Append(">");
return sb.ToString();
}
public string DGVCellToHTMLWithFormatting(DataGridView dgv, int row, int col)
{
StringBuilder sb = new StringBuilder();
sb.Append("<td");
sb.Append(DGVCellColorToHTML(dgv.Rows[row].Cells[col].Style.ForeColor, dgv.Rows[row].Cells[col].Style.BackColor));
sb.Append(DGVCellAlignmentToHTML(dgv.Rows[row].Cells[col].Style.Alignment));
sb.Append(">");
return sb.ToString();
}
public string DGVCellColorToHTML(Color foreColor, Color backColor)
{
if (foreColor.Name == "0" && backColor.Name == "0") return string.Empty;
StringBuilder sb = new StringBuilder();
sb.Append(" style=\"");
if (foreColor.Name != "0" && backColor.Name != "0")
{
sb.Append("color:#");
sb.Append(foreColor.R.ToString("X2") + foreColor.G.ToString("X2") + foreColor.B.ToString("X2"));
sb.Append("; background-color:#");
sb.Append(backColor.R.ToString("X2") + backColor.G.ToString("X2") + backColor.B.ToString("X2"));
}
else if (foreColor.Name != "0" && backColor.Name == "0")
{
sb.Append("color:#");
sb.Append(foreColor.R.ToString("X2") + foreColor.G.ToString("X2") + foreColor.B.ToString("X2"));
}
else //if (foreColor.Name == "0" && backColor.Name != "0")
{
sb.Append("background-color:#");
sb.Append(backColor.R.ToString("X2") + backColor.G.ToString("X2") + backColor.B.ToString("X2"));
}
sb.Append(";\"");
return sb.ToString();
}
public string DGVCellFontAndValueToHTML(string value,Font font)
{
//If no font has been set then assume its the default as someone would be expected in HTML or Excel
if (font == null || font == this.Font && !(font.Bold | font.Italic | font.Underline | font.Strikeout)) return value;
StringBuilder sb = new StringBuilder();
sb.Append(" ");
if (font.Bold) sb.Append("<b>");
if (font.Italic) sb.Append("<i>");
if (font.Strikeout) sb.Append("<strike>");
//The <u> element was deprecated in HTML 4.01. The new HTML 5 tag is: text-decoration: underline
if (font.Underline) sb.Append("<u>");
string size = string.Empty;
if (font.Size != this.Font.Size) size = "font-size: " + font.Size + "pt;";
//The <font> tag is not supported in HTML5. Use CSS or a span instead.
if (font.FontFamily.Name != this.Font.Name)
{
sb.Append("<span style=\"font-family: ");
sb.Append(font.FontFamily.Name);
sb.Append("; ");
sb.Append(size);
sb.Append("\">");
}
sb.Append(value);
if (font.FontFamily.Name != this.Font.Name) sb.Append("</span>");
if (font.Underline) sb.Append("</u>");
if (font.Strikeout) sb.Append("</strike>");
if (font.Italic) sb.Append("</i>");
if (font.Bold) sb.Append("</b>");
return sb.ToString();
}
public string DGVCellAlignmentToHTML(DataGridViewContentAlignment align)
{
if (align == DataGridViewContentAlignment.NotSet) return string.Empty;
string horizontalAlignment = string.Empty;
string verticalAlignment = string.Empty;
CellAlignment(align, ref horizontalAlignment, ref verticalAlignment);
StringBuilder sb = new StringBuilder();
sb.Append(" align='");
sb.Append(horizontalAlignment);
sb.Append("' valign='");
sb.Append(verticalAlignment);
sb.Append("'");
return sb.ToString();
}
private void CellAlignment(DataGridViewContentAlignment align, ref string horizontalAlignment, ref string verticalAlignment)
{
switch (align)
{
case DataGridViewContentAlignment.MiddleRight:
horizontalAlignment = "right";
verticalAlignment = "middle";
break;
case DataGridViewContentAlignment.MiddleLeft:
horizontalAlignment = "left";
verticalAlignment = "middle";
break;
case DataGridViewContentAlignment.MiddleCenter:
horizontalAlignment = "centre";
verticalAlignment = "middle";
break;
case DataGridViewContentAlignment.TopCenter:
horizontalAlignment = "centre";
verticalAlignment = "top";
break;
case DataGridViewContentAlignment.BottomCenter:
horizontalAlignment = "centre";
verticalAlignment = "bottom";
break;
case DataGridViewContentAlignment.TopLeft:
horizontalAlignment = "left";
verticalAlignment = "top";
break;
case DataGridViewContentAlignment.BottomLeft:
horizontalAlignment = "left";
verticalAlignment = "bottom";
break;
case DataGridViewContentAlignment.TopRight:
horizontalAlignment = "right";
verticalAlignment = "top";
break;
case DataGridViewContentAlignment.BottomRight:
horizontalAlignment = "right";
verticalAlignment = "bottom";
break;
default: //DataGridViewContentAlignment.NotSet
horizontalAlignment = "left";
verticalAlignment = "middle";
break;
}
}
//Easy repro - copy/paste all this code in a Winform app!
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
string configFile = System.IO.Path.Combine(Application.StartupPath.Replace("\\bin\\Debug", ""), "testData.csv");
List<string[]> rows = System.IO.File.ReadAllLines(configFile).Select(x => x.Split(',')).ToList();
DataTable dataTable = new DataTable();
dataTable.Columns.Add("testing");
dataTable.Columns.Add("one");
dataTable.Columns.Add("two");
dataTable.Columns.Add("three");
rows.ForEach(x => { dataTable.Rows.Add(x); });
this.dgv.DataSource = dataTable;
dgv.Columns[0].HeaderCell.Style.Font = new Font(this.Font, FontStyle.Strikeout);
dgv[0, 0].Style.BackColor = Color.Aqua;
dgv[1, 0].Style.Alignment = DataGridViewContentAlignment.BottomRight;
dgv[2, 0].Style.Font = new Font(new FontFamily("Calibri"),(float)16);
dgv[3, 0].Style.ForeColor = Color.Red;
dgv[0, 1].Style.Font = new Font(this.Font, FontStyle.Bold);
dgv[1, 1].Style.Font = new Font(this.Font, FontStyle.Underline);
dgv[2, 1].Style.Font = new Font(this.Font, FontStyle.Italic);
dgv[3, 1].Style.Font = new Font(this.Font, FontStyle.Bold | FontStyle.Underline);
dgv[3, 1].Style.ForeColor = Color.Green;
dgv[3, 1].Style.BackColor = Color.Yellow;
dgv[0, 2].Style.Font = new Font(new FontFamily("Times New Roman"), (float)18);
dgv[1, 2].Style.Font = new Font(new FontFamily("Georgia"), (float)12);
dgv[2, 2].Style.Font = new Font(new FontFamily("Arial"), (float)14);
dgv[3, 2].Style.Font = new Font(new FontFamily("Verdana"), (float)18);
dgv[0, 3].Style.Font = new Font(new FontFamily("Courier New"), (float)11);
dgv[1, 3].Style.Font = new Font(new FontFamily("Lucida Console"), (float)18);
dgv[2, 3].Style.Font = new Font(new FontFamily("Times"), (float)14);
dgv[3, 3].Style.Font = new Font(new FontFamily("serif"), (float)12);
}
private void button1_Click(object sender, EventArgs e)
{
string dgvToHTMLTable = ConvertDataGridViewToHTMLWithFormatting(dgv);
Clipboard.SetText(dgvToHTMLTable);
}
TestData.csv:
Magic,Abra,Cadabra,Boom!
Coding,Fun,YeeHaa,ABS TableName
Hello,world,Population.html,TABLE 1.
Demography,310102.xls,Comp.html,TABLE 2.
Solution 2:
The crux of your question is using the Clipboard on a DataGridView does not contain Cell Formatting. Because the Clipboard doesn't contain the formatting you're back to the original slow performance problem of having to set Cell Styles individually, which using Interop is very, very slow.
In this case it will work better to create Excel files using XML instead of Interop. Below is a method using ClosedXML to export a DataGridView to Excel with formatting.
using ClosedXML.Excel;
public void ExportToExcelWithFormatting(DataGridView dataGridView1)
{
string fileName;
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "xls files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveFileDialog1.Title = "To Excel";
saveFileDialog1.FileName = this.Text + " (" + DateTime.Now.ToString("yyyy-MM-dd") + ")";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
fileName = saveFileDialog1.FileName;
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add(this.Text);
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
worksheet.Cell(1, i + 1).Value = dataGridView1.Columns[i].Name;
}
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cell(i + 2, j + 1).Value = dataGridView1.Rows[i].Cells[j].Value.ToString();
if (worksheet.Cell(i + 2, j + 1).Value.ToString().Length > 0)
{
XLAlignmentHorizontalValues align;
switch (dataGridView1.Rows[i].Cells[j].Style.Alignment)
{
case DataGridViewContentAlignment.BottomRight:
align = XLAlignmentHorizontalValues.Right;
break;
case DataGridViewContentAlignment.MiddleRight:
align = XLAlignmentHorizontalValues.Right;
break;
case DataGridViewContentAlignment.TopRight:
align = XLAlignmentHorizontalValues.Right;
break;
case DataGridViewContentAlignment.BottomCenter:
align = XLAlignmentHorizontalValues.Center;
break;
case DataGridViewContentAlignment.MiddleCenter:
align = XLAlignmentHorizontalValues.Center;
break;
case DataGridViewContentAlignment.TopCenter:
align = XLAlignmentHorizontalValues.Center;
break;
default:
align = XLAlignmentHorizontalValues.Left;
break;
}
worksheet.Cell(i + 2, j + 1).Style.Alignment.Horizontal = align;
XLColor xlColor = XLColor.FromColor(dataGridView1.Rows[i].Cells[j].Style.SelectionBackColor);
worksheet.Cell(i + 2, j + 1).AddConditionalFormat().WhenLessThan(1).Fill.SetBackgroundColor(xlColor);
worksheet.Cell(i + 2, j + 1).Style.Font.FontName = dataGridView1.Font.Name;
worksheet.Cell(i + 2, j + 1).Style.Font.FontSize = dataGridView1.Font.Size;
}
}
}
worksheet.Columns().AdjustToContents();
workbook.SaveAs(fileName);
//MessageBox.Show("Done");
}
}