使用所有单元格格式将 dataGridView 导出到 Excel [英] Export the dataGridView to Excel with all the cells format
本文介绍了使用所有单元格格式将 dataGridView 导出到 Excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有这个代码，我知道它运行得很快
CopyAlltoClipboard(dataGridViewControl);Microsoft.Office.Interop.Excel.Application xlexcel;Microsoft.Office.Interop.Excel.Workbook xlWorkBook;Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;对象 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);//删除具有行索引的第一列xlWorkBook.SaveAs(fileName);私有无效 CopyAlltoClipboard(DataGridView dataGridViewControl){dataGridViewControl.SelectAll();DataObject dataObj = dataGridViewControl.GetClipboardContent();如果(数据对象！= null)Invoke((Action)(() => { Clipboard.SetDataObject(dataObj); }));}代码工作正常，但它只复制excel的值，不复制单元格格式(换行文本、背景色、字体、边框等)谁能帮我解决这个问题?如何完成此代码并具有与 DataGridView 中完全相同的格式?
解决方案
更新:现已在 GitHub 中提供:
//====================================================//DataGridView Export To HTML by Jeremy Thompson:https://stackoverflow.com/questions/39210329///====================================================公共字符串 ConvertDataGridViewToHTMLWithFormatting(DataGridView dgv){StringBuilder sb = new StringBuilder();//创建html &桌子sb.AppendLine("");sb.AppendLine("");//创建表头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>");//创建表体for (int rowIndex = 0; rowIndex < dgv.Rows.Count; rowIndex++){sb.AppendLine(" ");foreach(dgv.Rows[rowIndex].Cells 中的 DataGridViewCell dgvc){sb.AppendLine(DGVCellToHTMLWithFormatting(dgv, rowIndex, dgvc.ColumnIndex));字符串 cellValue = dgvc.Value == null ?string.Empty : dgvc.Value.ToString();sb.AppendLine(DGVCellFontAndValueToHTML(cellValue, dgvc.Style.Font));sb.AppendLine("</td>");}sb.AppendLine("</tr>");}//表格页脚&html文件的结尾sb.AppendLine("</table></center></body></html>");返回 sb.ToString();}//TODO:添加此处描述的更多单元格样式:https://msdn.microsoft.com/en-us/library/1yef90x0(v=vs.110).aspx公共字符串 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(">");返回 sb.ToString();}公共字符串 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(">");返回 sb.ToString();}公共字符串 DGVCellColorToHTML(颜色前景色，颜色背景色){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("颜色:#");sb.Append(foreColor.R.ToString("X2") + foreColor.G.ToString("X2") + foreColor.B.ToString("X2"));sb.Append("; 背景色:#");sb.Append(backColor.R.ToString("X2") + backColor.G.ToString("X2") + backColor.B.ToString("X2"));}else if (foreColor.Name != "0" && backColor.Name == "0"){sb.Append("颜色:#");sb.Append(foreColor.R.ToString("X2") + foreColor.G.ToString("X2") + foreColor.B.ToString("X2"));}else//if (foreColor.Name == "0" && backColor.Name != "0"){sb.Append("背景色:#");sb.Append(backColor.R.ToString("X2") + backColor.G.ToString("X2") + backColor.B.ToString("X2"));}sb.Append(";"");返回 sb.ToString();}公共字符串 DGVCellFontAndValueToHTML(字符串值，字体字体){//如果没有设置字体，则假定它是默认的，因为有人会在 HTML 或 Excel 中if (font == null || font == this.Font && !(font.Bold | font.Italic | font.Underline | font.Strikeout)) 返回值；StringBuilder sb = new StringBuilder();sb.Append(" ");if (font.Bold) sb.Append("");if (font.Italic) sb.Append("");if (font.Strikeout) sb.Append("");//元素在 HTML 4.01 中被弃用.新的 HTML 5 标签是: text-decoration: underlineif (font.Underline) sb.Append("");字符串大小 = string.Empty;if (font.Size != this.Font.Size) size = "font-size:" + font.Size + "pt;";//<字体>HTML5 不支持标签.改用 CSS 或跨度.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>");返回 sb.ToString();}公共字符串 DGVCellAlignmentToHTML(DataGridViewContentAlignment align){if (align == DataGridViewContentAlignment.NotSet) return string.Empty;字符串水平对齐 = 字符串.空；字符串垂直对齐 = 字符串.空；单元格对齐(对齐，参考水平对齐，参考垂直对齐)；StringBuilder sb = new StringBuilder();sb.Append(" align='");sb.Append(horizontalAlignment);sb.Append("' valign='");sb.Append(verticalAlignment);sb.Append("'");返回 sb.ToString();}私有无效单元格对齐(DataGridViewContentAlignment 对齐，参考字符串水平对齐，参考字符串垂直对齐){切换(对齐){案例 DataGridViewContentAlignment.MiddleRight:水平对齐 = "右";垂直对齐=中间"；休息;案例 DataGridViewContentAlignment.MiddleLeft:水平对齐 = "左";垂直对齐=中间"；休息;案例 DataGridViewContentAlignment.MiddleCenter:水平对齐 = "中心";垂直对齐=中间"；休息;案例 DataGridViewContentAlignment.TopCenter:水平对齐 = "中心";垂直对齐=顶部"；休息;案例 DataGridViewContentAlignment.BottomCenter:水平对齐 = "中心";垂直对齐=底部"；休息;案例 DataGridViewContentAlignment.TopLeft:水平对齐 = "左";垂直对齐=顶部"；休息;案例 DataGridViewContentAlignment.BottomLeft:水平对齐 = "左";垂直对齐=底部"；休息;案例 DataGridViewContentAlignment.TopRight:水平对齐 = "右";垂直对齐=顶部"；休息;案例DataGridViewContentAlignment.BottomRight:水平对齐 = "右";垂直对齐=底部"；休息;默认值://DataGridViewContentAlignment.NotSet水平对齐 = "左";垂直对齐=中间"；休息;}}//简单的复制 - 在 Winform 应用程序中复制/粘贴所有这些代码！公共 Form1(){初始化组件()；}private void Form1_Load(对象发送者，EventArgs e){string configFile = System.IO.Path.Combine(Application.StartupPath.Replace("\bin\Debug", ""), "testData.csv");列表行 = System.IO.File.ReadAllLines(configFile).Select(x => x.Split(',')).ToList();DataTable dataTable = new DataTable();dataTable.Columns.Add("测试");dataTable.Columns.Add("one");dataTable.Columns.Add("二");dataTable.Columns.Add("三");rows.ForEach(x => { dataTable.Rows.Add(x); });this.dgv.DataSource = 数据表；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){字符串 dgvToHTMLTable = ConvertDataGridViewToHTMLWithFormatting(dgv);Clipboard.SetText(dgvToHTMLTable);}TestData.csv:
<块引用>Magic,Abra,Cadabra,Boom!
Coding,Fun,YeeHaa,ABS TableName
你好，世界，Population.html，表 1.
人口统计，310102.xls，Comp.html，表 2.
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?
解决方案 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.
这篇关于使用所有单元格格式将 dataGridView 导出到 Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
登录
关闭
扫码关注1秒登录
发送“验证码”获取
|
15天全站免登陆