Excel数据到datagridview 里面 （流读取）
System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog(); if (fd.ShowDialog() == DialogResult.OK) { DataTable DT = Table(fd.FileName); int CellCount = DT.Columns.Count; int RowCount = DT.Rows.Count; List<string> list = new List<string>(); for (int a = 0; a <= CellCount - 1; a++) { list.Add(DT.Rows[0][a].ToString()); } dataGridViewX1.DataSource = DT; DT.Rows.RemoveAt(0); for (int b = 0; b < CellCount; b++) { dataGridViewX1.Columns[b].HeaderCell.Value = list[b]; dataGridViewX1.Columns[b].Name = list[b]; } DevComponents.DotNetBar.Controls.DataGridViewX Da = new DevComponents.DotNetBar.Controls.DataGridViewX(); }
public DataTable Table(string ST) { // string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ST + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'"; string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ST + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'"; OleDbConnection myConn = new OleDbConnection(strCon); string strCom = " SELECT * FROM [Sheet1$] "; myConn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); DataSet myDataSet = new DataSet(); myCommand.Fill(myDataSet, "[Sheet1$]"); myConn.Close(); return myDataSet.Tables[0]; }
C# datagridview 数据到Excel 流导出
DataTable DT = new DataTable(); DataRow DR = DT.NewRow(); for(int a = 0 ;a<dataGridViewX1.Columns.Count;a++) { DT.Columns.Add(dataGridViewX1.Columns[a].HeaderCell.Value.ToString(),Type.GetType("System.String")); } int RowCount = dataGridViewX1.Rows.Count; int CellCount = dataGridViewX1.Columns.Count; for (int a = 0; a < RowCount-1; a++) {//新实例化数据 DR = DT.NewRow(); for (int b = 0; b < CellCount; b++) { // dataRow 构建行数据 构建一条添加一条 DR[b] = dataGridViewX1.Rows[a].Cells[b].Value.ToString(); } DT.Rows.Add(DR); } //dataGridViewX1.Rows.Clear(); DataGridViewExportToExcel(DT, "导出Excel"); }
public void DataGridViewExportToExcel(DataTable Tab, string strTitle) { SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Excel files (*.xls)|*.xls"; saveFileDialog.FilterIndex = 0; saveFileDialog.RestoreDirectory = true; saveFileDialog.CreatePrompt = false; saveFileDialog.FileName = strTitle + ".xls"; if (saveFileDialog.ShowDialog() == DialogResult.Cancel) //导出时，点击【取消】按钮 { return; } Stream myStream = saveFileDialog.OpenFile(); StreamWriter sw = new StreamWriter(myStream,System.Text.Encoding.GetEncoding(-0)); string strHeaderText = ""; try { //写标题 for (int i = 0; i < Tab.Columns.Count; i++) { if (i > 0) { strHeaderText += " "; } strHeaderText += Tab.Columns[i].ToString(); } sw.WriteLine(strHeaderText); //写内容 string strItemValue = ""; for (int j = 0; j < Tab.Rows.Count; j++) { strItemValue = ""; for (int k = 0; k < Tab.Columns.Count; k++) { if (k > 0) { strItemValue += " "; } strItemValue += Tab.Rows[j][k].ToString(); } sw.WriteLine(strItemValue); //把dgv的每一行的信息写为sw的每一行 } } catch (Exception ex) { MessageBox.Show(ex.Message, "软件提示"); throw ex; } finally { sw.Close(); myStream.Close(); } }
导出Excel （worksheet）
public void Exportdatagridviewtoexcel(DataGridView mydgv) { if (mydgv.Rows.Count == 0) { MessageBox.Show(" 没有数据可供导出！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } else { SaveFileDialog savedialog = new SaveFileDialog(); savedialog.DefaultExt = "xlsx"; savedialog.Filter = "microsoft office execl files (*.xlsx)|*.xlsx"; savedialog.FilterIndex = 0; savedialog.RestoreDirectory = true; savedialog.Title = "导出数据到excel表格"; savedialog.ShowDialog(); if (savedialog.FileName.IndexOf(":") < 0) return; //被点了取消 //Microsoft.office.interop.excel.application xlapp = new microsoft.office.interop.excel.application(); Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application(); if (xlapp == null) { MessageBox.Show("可能您的机子未安装excel，无法创建excel对象！", "系统提示 ", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } Microsoft.Office.Interop.Excel.Workbooks workbooks = xlapp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 //定义表格内数据的行数和列数 int rowscount = mydgv.Rows.Count; int colscount = mydgv.Columns.Count; //行数不可以大于65536 if (rowscount > 65536) { MessageBox.Show("数据行记录超过65536行，不能保存！", "系统提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //列数不可以大于255 if (colscount > 256) { MessageBox.Show("数据列记录超过256列，不能保存！", "系统提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //写入大标题 string text = US_GV.UserDeptName + "—库存初始化报表"; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 10]).MergeCells = true; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 10]).Font.Size = 17; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 10]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 10]).Font.Bold = true; worksheet.Cells[1, 1] = text; //写入列名 for (int i = 0; i < mydgv.ColumnCount; i++) { worksheet.Cells[3, i + 1] = mydgv.Columns[i].HeaderText; } //写入数值 for (int r = 0; r < mydgv.Rows.Count; r++) { for (int i = 0; i < mydgv.ColumnCount; i++) { if (mydgv[i, r].ValueType == typeof(string)) { worksheet.Cells[r + 4, i + 1] = "" + mydgv.Rows[r].Cells[i].Value.ToString().Replace(" ", ""); ;//将长数值转换成文本 } else { worksheet.Cells[r + 4, i + 1] = mydgv.Rows[r].Cells[i].Value.ToString().Replace(" ", ""); ; } } System.Windows.Forms.Application.DoEvents(); } worksheet.Columns.EntireColumn.AutoFit();//列宽自适应 if (savedialog.FileName != "") { try { workbook.Saved = true; workbook.SaveCopyAs(savedialog.FileName); } catch (Exception ex) { MessageBox.Show("导出文件时出错,文件可能正被打开！..." + ex.Message, "系统提示 ", MessageBoxButtons.OK, MessageBoxIcon.Error); } } //GC.Collect();//强行销毁 MessageBox.Show("数据导出成功！ ", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
导出Excel 属性设置 /// <summary> /// 单元格背景色及填充方式 /// </summary> /// <param name="startRow">起始行</param> /// <param name="startColumn">起始列</param> /// <param name="endRow">结束行</param> /// <param name="endColumn">结束列</param> /// <param name="color">颜色索引</param> public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color) { Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]); range.Interior.ColorIndex = color; range.Interior.Pattern = Pattern.Solid; } /// <summary> /// 单元格背景色及填充方式 /// </summary> /// <param name="startRow">起始行</param> /// <param name="startColumn">起始列</param> /// <param name="endRow">结束行</param> /// <param name="endColumn">结束列</param> /// <param name="color">颜色索引</param> /// <param name="pattern">填充方式</param> public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color, Pattern pattern) { Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]); range.Interior.ColorIndex = color; range.Interior.Pattern = pattern; } /// <summary> /// 设置行高 /// </summary> /// <param name="startRow">起始行</param> /// <param name="endRow">结束行</param> /// <param name="height">行高</param> public void SetRowHeight(int startRow, int endRow, int height) { //获取当前正在使用的工作表 Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet; Excel.Range range = (Excel.Range)worksheet.Rows[startRow.ToString() + ":" + endRow.ToString(), System.Type.Missing]; range.RowHeight = height; } /// <summary> /// 自动调整行高 /// </summary> /// <param name="columnNum">列号</param> public void RowAutoFit(int rowNum) { //获取当前正在使用的工作表 Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet; Excel.Range range = (Excel.Range)worksheet.Rows[rowNum.ToString() + ":" + rowNum.ToString(), System.Type.Missing]; range.EntireColumn.AutoFit(); } /// <summary> /// 设置列宽 /// </summary> /// <param name="startColumn">起始列(列对应的字母)</param> /// <param name="endColumn">结束列(列对应的字母)</param> /// <param name="width"></param> public void SetColumnWidth(string startColumn, string endColumn, int width) { //获取当前正在使用的工作表 Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet; Excel.Range range = (Excel.Range)worksheet.Columns[startColumn + ":" + endColumn, System.Type.Missing]; range.ColumnWidth = width; } /// <summary> /// 设置列宽 /// </summary> /// <param name="startColumn">起始列</param> /// <param name="endColumn">结束列</param> /// <param name="width"></param> public void SetColumnWidth(int startColumn, int endColumn, int width) { string strStartColumn = GetColumnName(startColumn); string strEndColumn = GetColumnName(endColumn); //获取当前正在使用的工作表 Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet; Excel.Range range = (Excel.Range)worksheet.Columns[strStartColumn + ":" + strEndColumn, System.Type.Missing]; range.ColumnWidth = width; } /// <summary> /// 自动调整列宽 /// </summary> /// <param name="columnNum">列号</param> public void ColumnAutoFit(string column) { //获取当前正在使用的工作表 Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet; Excel.Range range = (Excel.Range)worksheet.Columns[column + ":" + column, System.Type.Missing]; range.EntireColumn.AutoFit(); } /// <summary> /// 自动调整列宽 /// </summary> /// <param name="columnNum">列号</param> public void ColumnAutoFit(int columnNum) { string strcolumnNum = GetColumnName(columnNum); //获取当前正在使用的工作表 Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet; Excel.Range range = (Excel.Range)worksheet.Columns[strcolumnNum + ":" + strcolumnNum, System.Type.Missing]; range.EntireColumn.AutoFit(); } /// <summary> /// 字体颜色 /// </summary> /// <param name="startRow">起始行</param> /// <param name="startColumn">起始列</param> /// <param name="endRow">结束行</param> /// <param name="endColumn">结束列</param> /// <param name="color">颜色索引</param> public void FontColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color) { Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]); range.Font.ColorIndex = color; } /// <summary> /// 字体样式(加粗,斜体,下划线) /// </summary> /// <param name="startRow">起始行</param> /// <param name="startColumn">起始列</param> /// <param name="endRow">结束行</param> /// <param name="endColumn">结束列</param> /// <param name="isBold">是否加粗</param> /// <param name="isItalic">是否斜体</param> /// <param name="underline">下划线类型</param> public void FontStyle(int startRow, int startColumn, int endRow, int endColumn, bool isBold, bool isItalic, UnderlineStyle underline) { Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]); range.Font.Bold = isBold; range.Font.Underline = underline; range.Font.Italic = isItalic; } /// <summary> /// 单元格字体及大小 /// </summary> /// <param name="startRow">起始行</param> /// <param name="startColumn">起始列</param> /// <param name="endRow">结束行</param> /// <param name="endColumn">结束列</param> /// <param name="fontName">字体名称</param> /// <param name="fontSize">字体大小</param> public void FontNameSize(int startRow, int startColumn, int endRow, int endColumn,string fontName, int fontSize) { Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]); range.Font.Name = fontName; range.Font.Size = fontSize; } /// <summary> /// 打开一个存在的Excel文件 /// </summary> /// <param name="fileName">Excel完整路径加文件名</param> public void Open(string fileName) { myExcel = new Excel.Application(); myWorkBook = myExcel.Workbooks.Add(fileName); myFileName = fileName; } /// <summary> /// 保存Excel /// </summary> /// <returns>保存成功返回True</returns> public bool Save() { if (myFileName == "") { return false; } else { try { myWorkBook.Save(); return true; } catch (Exception ex) { return false; } } } /// <summary> /// Excel文档另存为 /// </summary> /// <param name="fileName">保存完整路径加文件名</param> /// <returns>保存成功返回True</returns> public bool SaveAs(string fileName) { try { myWorkBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); return true; } catch (Exception ex) { return false; } } /// <summary> /// 关闭Excel /// </summary> public void Close() { myWorkBook.Close(Type.Missing, Type.Missing, Type.Missing); myExcel.Quit(); myWorkBook = null; myExcel = null; GC.Collect(); } /// <summary> /// 关闭Excel /// </summary> /// <param name="isSave">是否保存</param> public void Close(bool isSave) { myWorkBook.Close(isSave, Type.Missing, Type.Missing); myExcel.Quit(); myWorkBook = null; myExcel = null; GC.Collect(); } /// <summary> /// 关闭Excel /// </summary> /// <param name="isSave">是否保存</param> /// <param name="fileName">存储文件名</param> public void Close(bool isSave,string fileName) { myWorkBook.Close(isSave, fileName, Type.Missing); myExcel.Quit(); myWorkBook = null; myExcel = null; GC.Collect(); } #region 私有成员 private string GetColumnName(int number) { int h, l; h = number / 26; l = number % 26; if (l == 0) { h -= 1; l = 26; } string s = GetLetter(h) + GetLetter(l); return s; } private string GetLetter(int number) { switch (number) { case 1: return "A"; case 2: return "B"; case 3: return "C"; case 4: return "D"; case 5: return "E"; case 6: return "F"; case 7: return "G"; case 8: return "H"; case 9: return "I"; case 10: return "J"; case 11: return "K"; case 12: return "L"; case 13: return "M"; case 14: return "N"; case 15: return "O"; case 16: return "P"; case 17: return "Q"; case 18: return "R"; case 19: return "S"; case 20: return "T"; case 21: return "U"; case 22: return "V"; case 23: return "W"; case 24: return "X"; case 25: return "Y"; case 26: return "Z"; default: return ""; } } #endregion } /// <summary> /// 水平对齐方式 /// </summary> public enum ExcelHAlign { 常规 = 1, 靠左, 居中, 靠右, 填充, 两端对齐, 跨列居中, 分散对齐 } /// <summary> /// 垂直对齐方式 /// </summary> public enum ExcelVAlign { 靠上 = 1, 居中, 靠下, 两端对齐, 分散对齐 } /// <summary> /// 线粗 /// </summary> public enum BorderWeight { 极细 = 1, 细 = 2, 粗 = -4138, 极粗 = 4 } /// <summary> /// 线样式 /// </summary> public enum LineStyle { 连续直线 = 1, 短线 = -4115, 线点相间 = 4, 短线间两点 = 5, 点 = -4118, 双线 = -4119, 无 = -4142, 少量倾斜点 = 13 } /// <summary> /// 下划线方式 /// </summary> public enum UnderlineStyle { 无下划线 = -4142, 双线 = - 4119, 双线充满全格 = 5, 单线 = 2, 单线充满全格 = 4 } /// <summary> /// 单元格填充方式 /// </summary> public enum Pattern { Automatic = -4105, Checker = 9, CrissCross = 16, Down = -4121, Gray16 = 17, Gray25 = -4124, Gray50 = -4125, Gray75 = -4126, Gray8 = 18, Grid = 15, Horizontal = -4128, LightDown = 13, LightHorizontal = 11, LightUp = 14, LightVertical = 12, None = -4142, SemiGray75 = 10, Solid = 1, Up = -4162, Vertical = -4166 } /// <summary> /// 常用颜色定义,对就Excel中颜色名 /// </summary> public enum ColorIndex { 无色 = -4142, 自动 = -4105, 黑色 = 1, 褐色 = 53, 橄榄 = 52, 深绿 = 51, 深青 = 49, 深蓝 = 11, 靛蓝 = 55, 灰色80 = 56, 深红 = 9, 橙色 = 46, 深黄 = 12, 绿色 = 10, 青色 = 14, 蓝色 = 5, 蓝灰 = 47, 灰色50 = 16, 红色 = 3, 浅橙色 = 45, 酸橙色 = 43, 海绿 = 50, 水绿色 = 42, 浅蓝 = 41, 紫罗兰 = 13, 灰色40 = 48, 粉红 = 7, 金色 = 44, 黄色 = 6, 鲜绿 = 4, 青绿 = 8, 天蓝 = 33, 梅红 = 54, 灰色25 = 15, 玫瑰红 = 38, 茶色 = 40, 浅黄 = 36, 浅绿 = 35, 浅青绿 = 34, 淡蓝 = 37, 淡紫 = 39, 白色 = 2 } }