C# DataGridView导出到Excel，并格式化。

最新推荐文章于 2023-04-11 14:25:53 发布

heaven&earth

最新推荐文章于 2023-04-11 14:25:53 发布

阅读量1k

点赞数

本文链接：https://blog.csdn.net/xiaozhao_19/article/details/38661293

版权

C#经验/实例专栏收录该内容

58 篇文章 0 订阅

订阅专栏

近日，要设法用C#做一个''EXCEL模板“，以便与玻璃自动化切割软件数据互通。

代码修改，保存如下：

#region DataGridView导出到Excel，有一定的判断性(com操作excel法　)
/// <summary>
///方法，导出DataGridView中的数据到Excel文件
/// </summary>
/// <remarks>
/// add com "Microsoft Excel 11.0 Object Library"
/// using Excel=Microsoft.Office.Interop.Excel;
/// using System.Reflection;
/// </remarks>
/// <param name= "dgv"> DataGridView </param>
private void DataGridViewToExcel2(DataGridView dgv)
{
#region 验证可操作性

//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog();
//文件名称
dlg.FileName = "订单" + clsPublicData.OrderID + "(" + System.DateTime.Now.ToString("yyyy年MM月dd日HH时mm分ss秒") + ")";
//默认文件后缀
dlg.DefaultExt = "xls";
//文件后缀列表 )|*.
dlg.Filter = "EXCEL文件(*.xls)|*.xls|所有文件（*.*）|*.* ";
//默认路径是系统当前路径
dlg.InitialDirectory = @"E:\DataOutput\";
//打开保存对话框
if (dlg.ShowDialog() == DialogResult.Cancel) return;
//返回文件路径
string fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
{ return; }
//定义表格内数据的行数和列数
int rowscount = dgv.Rows.Count;
int colscount = dgv.Columns.Count;
//行数必须大于0
if (rowscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}

//列数必须大于0
if (colscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}

//行数不可以大于65536
if (rowscount > 65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}

//列数不可以大于255
if (colscount > 255)
{
MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}

//验证以fileNameString命名的文件是否存在，如果存在删除它
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
#endregion

DataTableToDictionary();

Excel.Application objExcel = null;
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
Excel.Range objrange = null;
try
{
//申明对象
objExcel = new Microsoft.Office.Interop.Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
//设置EXCEL不可见
objExcel.Visible = true;
objExcel.DisplayAlerts = false;

向Excel中写入表格的表头
objrange = objsheet.get_Range("A1", "O1");
objrange.ColumnWidth = "7";
objrange.Font.Name = "宋体";
objrange.Font.Size = "10";
objrange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
objrange.WrapText = true;
objrange.Font.Bold = true;

objrange = objsheet.get_Range("A1", Type.Missing);
objrange.Value2 = "DIMXPZ X尺寸";
objrange = objsheet.get_Range("B1", Type.Missing);
objrange.Value2 = "DIMYPZ Y尺寸";
objrange = objsheet.get_Range("C1", Type.Missing);
objrange.Value2 = "QTAPZ 数量";
objrange = objsheet.get_Range("D1", Type.Missing);
objrange.Value2 = "CLIENTE 客户";
objrange = objsheet.get_Range("E1", Type.Missing);
objrange.Value2 = "CODMAT材料（必须输）";
objrange.ColumnWidth = "10";
objrange = objsheet.get_Range("F1", Type.Missing);
objrange.Value2 = "ORDINE订单";
objrange = objsheet.get_Range("G1", Type.Missing);
objrange.Value2 = "NOTE备注";
//objrange.EntireRow.AutoFit();
objrange = objsheet.get_Range("H1", Type.Missing);
objrange.Value2 = "MOLATURA磨四边";
objrange = objsheet.get_Range("I1", Type.Missing);
objrange.Value2 = "MY1 X1磨边量";
objrange = objsheet.get_Range("J1", Type.Missing);
objrange.Value2 = "MY1 Y1磨边量";
objrange = objsheet.get_Range("K1", Type.Missing);
objrange.Value2 = "MX2 X2磨边量";
objrange = objsheet.get_Range("L1", Type.Missing);
objrange.Value2 = "MY2 Y2磨边量";
objrange = objsheet.get_Range("M1", Type.Missing);
objrange.Value2 = "RACK玻璃架";
objrange = objsheet.get_Range("N1", Type.Missing);
objrange.Value2 = "PRIOPZ优化级";
objrange = objsheet.get_Range("O1", Type.Missing);
objrange.Value2 = "NOTE备注1";

//向Excel中逐行逐列写入表格中的数据
for (int row = 0; row <= dgv.RowCount - 1; row++)
{
try
{
objExcel.Cells[row + 2, 1] = dgv.Rows[row].Cells[3].Value.ToString().Trim();
objExcel.Cells[row + 2, 2] = dgv.Rows[row].Cells[4].Value.ToString().Trim();
objExcel.Cells[row + 2, 3] = dgv.Rows[row].Cells[5].Value.ToString().Trim();
objExcel.Cells[row + 2, 4] = lblCustomName.Text.Trim();
objExcel.Cells[row + 2, 6] = lblOrderID.Text.Trim().Substring(4);
objExcel.Cells[row + 2, 8] = "3";
objExcel.Cells[row + 2, 7] = dgv.Rows[row].Cells[12].Value.ToString().Trim();
objExcel.Cells[row + 2, 15] = dic[Convert.ToInt32(dgv.Rows[row].Cells[11].Value.ToString().Trim())];
objExcel.Cells[row + 2, 14] = 0;
}
catch (Exception)
{

}

}

objrange = objsheet.get_Range("E2", "E" + (dgv.RowCount + 1).ToString());
objrange.Interior.ColorIndex = 19;

// objsheet.Cells.Select();
objsheet.Cells.Columns.AutoFit();

//保存文件
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{

objsheet = null;
objWorkbook = null;
objExcel = null;
}
}

#endregion

private void DataTableToDictionary()
{
AccessDbClass mydb = new AccessDbClass();
DataTable dt = new DataTable();
dt = mydb.SelectToDataTable("select * from TypeInfo");
foreach (DataRow dr in dt.Rows)
dic.Add(Convert.ToInt32(dr[0].ToString()), dr[1].ToString());
}

效果如下：