datagridview / datatable 导出带多表头的文件
1. 写一个导出按钮，点击导出按钮导出数据
private void button4_Click(object sender, EventArgs e)
{
//如果需要导出的dataGridView4中有数据
if (dataGridView4.Rows.Count > 0)
{
string order_id = textOrderDay.Text.ToString();
string string1 = textArtDay.Text.Trim();
string string2 = GetShoeTyesName(art, order_id);
string string3 = GetProcessingUnit(order_id);
string string4 = richTextDay.Text.Trim();
string string5 ="时间";
string type = comboBox3.Text.ToString();
if (type.Equals("送料"))
{
ExtelMyself("送料日配套报表", dataGridView4, string1 , string2 , string3 ,string4 ,string5 );
}
else if (type.Equals("回料"))
{
ExtelMyself("回料日配套报表", dataGridView4, string1 , string2 , string3 ,string4 ,string5 );
}else
{
MessageBox.Show("不能明确配套报表类型");
}
}
}
2. 编写导出数据的函数
public void ExtelMyself(string fileName, DataGridView myDGV, string art,string shoeTypeName,string processing_unit,string po,string date)
{
try
{
//生成的文件存放路径 存在电脑的文档里边
string temp_path = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
ExcelOperate excelOperate = new ExcelOperate();
//建立一个Excel.Application的新进程
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)
{
return;
}
app.Visible = false;
app.UserControl = true;
Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//这里的Add方法里的参数就相当于继承了一个空模板(暂这样理解吧)
Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);
if (worksheet == null)
{
return;
}
worksheet.Rows.NumberFormatLocal = "@"; //设置所有单元格为文本格式
//第一行的标题
worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, myDGV.Columns.Count]].Merge(Missing.Value); //横向合并
//文件名和标题名一样，这是输入标题名
worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 1]].Value2 = fileName;
//设置格式
excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]);//居中
excelOperate.SetFontSize(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 18);//字体大小
worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, myDGV.Columns.Count]].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色连续边框
Dictionary<string, string> temp = new Dictionary<string, string>();
temp.Add("鞋型名称: ", shoeTypeName);
temp.Add("Art: ", art);
temp.Add("制令： ", po);
temp.Add("厂商： ", processing_unit);
temp.Add("日期： ",date);
for(int i = 2; i < 7; i++)
{
worksheet.Range[worksheet.Cells[i, 1], worksheet.Cells[i, myDGV.Columns.Count]].Merge(Missing.Value); //横向合并
//文件名和标题名一样，这是输入标题名
string s123 = temp.ElementAt(i - 2).Key + temp.ElementAt(i - 2).Value;
string s456 = temp.ElementAt(i - 2).ToString();
worksheet.Range[worksheet.Cells[i, 1], worksheet.Cells[i, 1]].Value2 =s123 ;
//设置格式
excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[i, 1], worksheet.Cells[i, 1]);//居中
excelOperate.SetFontSize(worksheet, worksheet.Cells[i, 1], worksheet.Cells[i, 1], 12);//字体大小
worksheet.Range[worksheet.Cells[i, 1], worksheet.Cells[i, myDGV.Columns.Count]].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色连续边框
}
//标题栏
for (int i = 0; i < myDGV.Columns.Count; i++)
{
worksheet.Cells[7, i + 1] = myDGV.Columns[i].HeaderText.ToString();
excelOperate.SetFontSize(worksheet, worksheet.Cells[3, i + 1], worksheet.Cells[7, i + 1], 9);//字体大小
excelOperate.SetBold(worksheet, worksheet.Cells[3, i + 1], worksheet.Cells[7, i + 1]); //黑体
}
worksheet.Range[worksheet.Cells[7, 1], worksheet.Cells[7, myDGV.Columns.Count]].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
for (int i = 0; i < myDGV.Rows.Count; i++)
{
for (int j = 0; j < myDGV.Columns.Count; j++)
{
// myDGV.Rows[r].Cells[i].Value
string data = myDGV.Rows[i].Cells[j].Value.ToString();
worksheet.Cells[8 + i, j + 1] = data;
excelOperate.SetFontSize(worksheet, worksheet.Cells[8 + i, j + 1], worksheet.Cells[8+ i, j + 1], 9);//字体大小
//if (j == 6)
//{
// worksheet.Cells[4 + i, j + 1].
//}
}
// worksheet.Range[worksheet.Cells[4 + i, 1], worksheet.Cells[4 + i, 10]].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//设置边框颜色，不然打印预览，会非常不雅观
}
worksheet.Name = fileName;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
// String tick = DateTime.Now.ToString().Replace("-", "").Replace(":", "").Replace(" ", "") + ".xls";//excel文件名称
// string tick = "日配套报表导出";
String save_path = temp_path + "\\" + fileName + ".xls";
// saveDialog.ShowDialog();
workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程
MessageBox.Show("导出成功");
}
catch(Exception e)
{
MessageBox.Show("导出失败！" );
}
}
3. ExcelOperate 类
using System;
using System.Web;
internal class ExcelOperate
{
private object mValue = System.Reflection.Missing.Value;
public ExcelOperate()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void Merge(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).Merge(mValue);
}
/// <summary>
/// 设置连续区域的字体大小
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="strStartCell">开始单元格</param>
/// <param name="strEndCell">结束单元格</param>
/// <param name="intFontSize">字体大小</param>
public void SetFontSize(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, int intFontSize)
{
CurSheet.get_Range(objStartCell, objEndCell).Font.Size = intFontSize.ToString();
}
/// <summary>
/// 横向打印
/// </summary>
/// <param name="CurSheet"></param>
public void xlLandscape(Microsoft.Office.Interop.Excel._Worksheet CurSheet)
{
CurSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
}
/// <summary>
/// 纵向打印
/// </summary>
/// <param name="CurSheet"></param>
public void xlPortrait(Microsoft.Office.Interop.Excel._Worksheet CurSheet)
{
CurSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
}
/// <summary>
/// 在指定单元格插入指定的值
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="Cell">单元格 如Cells[1,1]</param>
/// <param name="objValue">文本、数字等值</param>
public void WriteCell(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objCell, object objValue)
{
CurSheet.get_Range(objCell, mValue).Value2 = objValue;
}
/// <summary>
/// 在指定Range中插入指定的值
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="StartCell">开始单元格</param>
/// <param name="EndCell">结束单元格</param>
/// <param name="objValue">文本、数字等值</param>
public void WriteRange(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, object objValue)
{
CurSheet.get_Range(objStartCell, objEndCell).Value2 = objValue;
}
/// <summary>
/// 合并单元格，并在合并后的单元格中插入指定的值
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="objValue">文本、数字等值</param>
public void WriteAfterMerge(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, object objValue)
{
CurSheet.get_Range(objStartCell, objEndCell).Merge(mValue);
CurSheet.get_Range(objStartCell, mValue).Value2 = objValue;
}
/// <summary>
/// 为单元格设置公式
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objCell">单元格</param>
/// <param name="strFormula">公式</param>
public void SetFormula(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objCell, string strFormula)
{
CurSheet.get_Range(objCell, mValue).Formula = strFormula;
}
/// <summary>
/// 单元格自动换行
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void AutoWrapText(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).WrapText = true;
}
/// <summary>
/// 设置整个连续区域的字体颜色
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="clrColor">颜色</param>
public void SetColor(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, System.Drawing.Color clrColor)
{
CurSheet.get_Range(objStartCell, objEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
}
/// <summary>
/// 设置整个连续区域的单元格背景色
/// </summary>
/// <param name="CurSheet"></param>
/// <param name="objStartCell"></param>
/// <param name="objEndCell"></param>
/// <param name="clrColor"></param>
public void SetBgColor(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, System.Drawing.Color clrColor)
{
CurSheet.get_Range(objStartCell, objEndCell).Interior.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
}
/// <summary>
/// 设置连续区域的字体名称
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="fontname">字体名称 隶书、仿宋_GB2312等</param>
public void SetFontName(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, string fontname)
{
CurSheet.get_Range(objStartCell, objEndCell).Font.Name = fontname;
}
/// <summary>
/// 设置连续区域的字体为黑体
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetBold(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
{
//CurSheet.get_Range(objStartCell, objEndCell).Font.Bold = true;
CurSheet.get_Range(objStartCell, objEndCell).Font.Size = 12;
}
/// <summary>
/// 设置连续区域的边框：上下左右都为黑色连续边框
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetBorderAll(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
}
/// <summary>
/// 设置连续区域水平居中
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetHAlignCenter(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
/// <summary>
/// 设置连续区域水平居左
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetHAlignLeft(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
/// <summary>
/// 设置连续区域水平居右
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetHAlignRight(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
}
/// <summary>
/// 设置连续区域的显示格式
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="strNF">如"#,##0.00"的显示格式</param>
public void SetNumberFormat(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, string strNF)
{
CurSheet.get_Range(objStartCell, objEndCell).NumberFormat = strNF;
}
public void border(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object aa, object bb)
{
CurSheet.get_Range(aa, bb).Borders.LineStyle = 1;
}
/// <summary>
/// 设置列宽
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="strColID">列标识，如A代表第一列</param>
/// <param name="dblWidth">宽度</param>
public void SetColumnWidth(Microsoft.Office.Interop.Excel._Worksheet CurSheet, string strColID, double dblWidth)
{
((Microsoft.Office.Interop.Excel.Range)CurSheet.Columns.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, CurSheet.Columns, new object[] { (strColID + ":" + strColID).ToString() })).ColumnWidth = dblWidth;
}
/// <summary>
/// 设置列宽
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="dblWidth">宽度</param>
public void SetColumnWidth(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, double dblWidth)
{
CurSheet.get_Range(objStartCell, objEndCell).ColumnWidth = dblWidth;
}
/// <summary>
/// 设置行高
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="dblHeight">行高</param>
public void SetRowHeight(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, double dblHeight)
{
CurSheet.get_Range(objStartCell, objEndCell).RowHeight = dblHeight;
}
/// <summary>
/// 为单元格添加超级链接
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objCell">单元格</param>
/// <param name="strAddress">链接地址</param>
/// <param name="strTip">屏幕提示</param>
/// <param name="strText">链接文本</param>
public void AddHyperLink(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objCell, string strAddress, string strTip, string strText)
{
CurSheet.Hyperlinks.Add(CurSheet.get_Range(objCell, objCell), strAddress, mValue, strTip, strText);
}
/// <summary>
/// 另存为xls文件
/// </summary>
/// <param name="CurBook">Workbook</param>
/// <param name="strFilePath">文件路径</param>
public void Save(Microsoft.Office.Interop.Excel._Workbook CurBook, string strFilePath)
{
CurBook.SaveCopyAs(strFilePath);
}
/// <summary>
/// 保存文件
/// </summary>
/// <param name="CurBook">Workbook</param>
/// <param name="strFilePath">文件路径</param>
public void SaveAs(Microsoft.Office.Interop.Excel._Workbook CurBook, string strFilePath)
{
CurBook.SaveAs(strFilePath, mValue, mValue, mValue, mValue, mValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, mValue, mValue, mValue, mValue, mValue);
}
/// <summary>
/// 另存为html文件
/// </summary>
/// <param name="CurBook">Workbook</param>
/// <param name="strFilePath">文件路径</param>
public void SaveHtml(Microsoft.Office.Interop.Excel._Workbook CurBook, string strFilePath)
{
CurBook.SaveAs(strFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml, mValue, mValue, mValue, mValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, mValue, mValue, mValue, mValue, mValue);
}
/// <summary>
/// 释放内存
/// </summary>
public void Dispose(Microsoft.Office.Interop.Excel._Worksheet CurSheet, Microsoft.Office.Interop.Excel._Workbook CurBook, Microsoft.Office.Interop.Excel._Application CurExcel)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
CurSheet = null;
CurBook.Close(false, mValue, mValue);
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
CurBook = null;
CurExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
CurExcel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch (System.Exception ex)
{
HttpContext.Current.Response.Write("在释放Excel内存空间时发生了一个错误:" + ex);
}
finally
{
foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
//if (pro.StartTime < DateTime.Now)
pro.Kill();
}
System.GC.SuppressFinalize(this);
}
}
最后导出的文件样式
扫一扫
385
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
