DBGridEh导出EXCEL

合集下载

DBGridEh导出Excel等格式文件

DBGridEh导出Excel等格式⽂件uses DBGridEhImpExp;{-------------------------------------------------------------------------------过程名: TfrmEmplyExport.OutToFile作者: han⽇期: 2007.08.08参数: IADO : TADOQuery; DgEh : TDBGridEh返回值: ⽆说明: 导出查询结果到⽂件-------------------------------------------------------------------------------}procedure TfrmEmplyExport.OutToFile(IADO : TADOQuery; DgEh : TDBGridEh);varExpClass:TDBGridEhExportclass;Ext:String;FSaveDialog: TSaveDialog;begintryif not IADO.IsEmpty thenbeginFSaveDialog := TSaveDialog.Create(Self);FSaveDialog.Filter:='Excel ⽂档 (*.xls)|*.XLS|Text files (*.txt)|*.TXT|Comma separated values (*.csv)|*.CSV|HTML file (*.htm)|*.HTM|Word ⽂档 (*.rtf)|*.RTF';if FSaveDialog.Execute and (trim(FSaveDialog.FileName)<>'') thenbegincase FSaveDialog.FilterIndex of1: begin ExpClass := TDBGridEhExportAsXLS; Ext := 'xls'; end;2: begin ExpClass := TDBGridEhExportAsText; Ext := 'txt'; end;3: begin ExpClass := TDBGridEhExportAsCSV; Ext := 'csv'; end;4: begin ExpClass := TDBGridEhExportAsHTML; Ext := 'htm'; end;5: begin ExpClass := TDBGridEhExportAsRTF; Ext := 'rtf'; end;end;if ExpClass <> nil thenbeginif UpperCase(Copy(FSaveDialog.FileName,Length(FSaveDialog.FileName)-2,3)) <> UpperCase(Ext) thenFSaveDialog.FileName := FSaveDialog.FileName + '.' + Ext;if FileExists(FSaveDialog.FileName) thenbeginif application.MessageBox('⽂件名已存在，是否覆盖 ', '提⽰', MB_ICONASTERISK or MB_OKCANCEL)<>idok thenexit;end;Screen.Cursor := crHourGlass;SaveDBGridEhToExportFile(ExpClass,DgEh,FSaveDialog.FileName,true);Screen.Cursor := crDefault;MessageBox(Handle, '导出成功 ', '提⽰', MB_OK +MB_ICONINFORMATION);end;end;FSaveDialog.Destroy;end;excepton e: exception dobeginApplication.MessageBox(PChar(e.message), '错误', MB_OK + MB_ICONSTOP);end;end;end;。

dbgrideh快速导出excel

dbgrideh快速导出excel采用BIFF8规发二进制导出写Excel文件的绿色函数，速度爆快. 收藏//采用BIFF8格式规范二进制流写入XLS文件function TsycFunctionSet.sycExportDateToExcel(ASender: TDBGrid): Boolean;var arXlsBegin: array[0..5] of Word ;var arXlsEnd: array[0..1] of Word;var arXlsString: array[0..5] of Word;var arXlsNumber: array[0..4] of Word;var arXlsInteger: array[0..4] of Word;var arXlsBlank: array[0..4] of Word;var i : Integer;var Col, Row: WORD;var aBookMark: TBookMark;var aFileStream: TFileStream;var aSaveDlg : TSaveDialog;var xlsFileName : string;//--------------内嵌写单元格函数开始-----------------------procedure XLSWriteStringCell(nRow,nCol : WORD;AValue: String);//写字符串数据var L: Word;var _str : AnsiString;begin_str:=AnsiString(AValue);//强制类型转换,兼容Delphi2009,采用String类型要乱码,Delphi2007以下此行代码无所谓L := Length(_str);//Length返回的字符个数,采用AnsiString也就是字节长度arXlsString[1] := 8 + L;arXlsString[2] := nRow;arXlsString[3] := nCol;arXlsString[5] := L;aFileStream.WriteBuffer(arXlsString, SizeOf(arXlsString));aFileStream.WriteBuffer(Pointer(_str)^, L);//字节长度end;procedure XLSWriteIntegerCell(nRow,nCol : WORD;AValue: Integer);//写整数var V: DWORD;beginarXlsInteger[2] := nRow;arXlsInteger[3] := nCol;aFileStream.WriteBuffer(arXlsInteger, SizeOf(arXlsInteger));V := (AValue shl 2) or 2;aFileStream.WriteBuffer(V, 4);end;procedure XLSWriteFloatCell(nRow,nCol : WORD;AValue: Double);//写浮点数beginarXlsNumber[2] := nRow;arXlsNumber[3] := nCol;aFileStream.WriteBuffer(arXlsNumber, SizeOf(arXlsNumber));aFileStream.WriteBuffer(AValue, 8);end;//--------------内嵌写单元格函数结束-----------------------begin//-----------------------主函数体开始------------------------//单元格记录的BIFF8定义,直所以用代码初始化数组，就是为了函数绿色嘛。

把WinForm的DataGridView的数据导出到Excel三种方法

1.#region DataGridView数据显示到Excel2./// <summary>3./// 打开Excel并将DataGridView控件中数据导出到Excel4./// </summary>5./// <param name="dgv">DataGridView对象</param>6./// <param name="isShowExcle">是否显示Excel界面</param>7./// <remarks>8./// add com "Microsoft Excel 11.0 Object Library"9./// using Excel=Microsoft.Office.Interop.Excel;10./// </remarks>11./// <returns> </returns>12.public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)13.{14.if(dgv.Rows.Count == 0)15.return false;16.//建立Excel对象17.Excel.Application excel = new Excel.Application();18.excel.Application.Workbooks.Add(true);19.excel.Visible = isShowExcle;20.//生成字段名称21.for(int i = 0; i < dgv.ColumnCount; i++)22.{23.excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;24.}25.//填充数据26.for(int i = 0; i < dgv.RowCount - 1; i++)27.{28.for(int j = 0; j < dgv.ColumnCount; j++)29.{30.if(dgv[j, i].ValueType == typeof(string))31.{32.excel.Cells[i + 2, j + 1] = "'"+ dgv[j, i].Value.ToString();33.}34.else35.{36.excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();38.}39.}40.return true;41.}42.#endregion43.44.#region DateGridView导出到csv格式的Excel45./// <summary>46./// 常用方法，列之间加\t，一行一行输出，此文件其实是csv文件，不过默认可以当成Excel打开。

如何使用c_将Winform下DataGridView中内容导出到Excel

原文地址：转：winform中将datagirdview中数据导出到EXCEL中作者：雪枫如何使用c#将Winform下DataGridView中内容导出到Excel?方法1 ：添加dll引用右击选择你所在的项目的“引用”，选择“添加引用”。

弹出“添加引用”对话框。

选择“COM”选项卡。

选择“Microsoft Excel 12.0 Object Library”单击“确定”按钮。

说明：如何发现导入的程序集不能正常工作，可能是由于office安装的缘故，请确保自定义安装office时，选择“.net可编程性支持”：代码：public static bool ExportForDataGridview(DataGridView gridView, string fileName, bool isShowExcle) {//建立Excel对象Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();try{if (app == null){return false;}app.Visible = isShowExcle;Workbooks workbooks = app.Workbooks;_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);Sheets sheets = workbook.Worksheets;_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);if (worksheet == null){return false;}string sLen = "";//取得最后一列列名char H = (char)(64 + gridView.ColumnCount / 26);char L = (char)(64 + gridView.ColumnCount % 26);if (gridView.ColumnCount < 26){sLen = L.ToString();}else{sLen = H.ToString() + L.ToString();//标题string sTmp = sLen + "1";Range ranCaption = worksheet.get_Range(sTmp, "A1"); string[] asCaption = new string[gridView.ColumnCount]; for (int i = 0; i < gridView.ColumnCount; i++){asCaption[i] = gridView.Columns[i].HeaderText;}ranCaption.Value2 = asCaption;//数据object[] obj = new object[gridView.Columns.Count];for (int r = 0; r < gridView.RowCount - 1; r++){for (int l = 0; l < gridView.Columns.Count; l++){if (gridView[l, r].ValueType == typeof(DateTime)){obj[l] = gridView[l, r].Value.ToString();}else{obj[l] = gridView[l, r].Value;}}string cell1 = sLen + ((int)(r + 2)).ToString();string cell2 = "A" + ((int)(r + 2)).ToString();Range ran = worksheet.get_Range(cell1, cell2);ran.Value2 = obj;}workbook.SaveCopyAs(fileName);workbook.Saved = true;}finally{//关闭erControl = false;app.Quit();}return true;}方法2用流保存成xls文件. 这种方法比较好,不用引用Excel组件. 下面是具体例子,可以参考using System.IO;using System.Text;/// <summary>/// 另存新档按钮/// </summary>private void SaveAs() //另存新档按钮导出成Excel{SaveFileDialog saveFileDialog = new SaveFileDialog();saveFileDialog.Filter = "Execl files (*.xls)|*.xls";saveFileDialog.FilterIndex = 0;saveFileDialog.RestoreDirectory = true;saveFileDialog.CreatePrompt = true;saveFileDialog.Title = "Export Excel File To";saveFileDialog.ShowDialog();Stream myStream;myStream = saveFileDialog.OpenFile();StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0)); string str = "";try{//写标题for (int i = 0; i < dgvAgeWeekSex.ColumnCount; i++){if (i > 0){str += "t";}str += dgvAgeWeekSex.Columns[i].HeaderText;}sw.WriteLine(str);//写内容for (int j = 0; j < dgvAgeWeekSex.Rows.Count; j++){string tempStr = "";for (int k = 0; k < dgvAgeWeekSex.Columns.Count; k++){if (k > 0){tempStr += "t";}tempStr += dgvAgeWeekSex.Rows[j].Cells[k].Value.ToString();}sw.WriteLine(tempStr);}sw.Close();myStream.Close();}catch (Exception e){MessageBox.Show(e.ToString());}finally{sw.Close();myStream.Close();}}C# WinForm下DataGridView导出Excel 的实现1.说明:导出的效率说不上很高,但至少是可以接收的.参考网上很多高效导出Excel的方法,实现到时能够实现的,导出速度也很快,不过缺陷在与不能很好的进行单元格的格式化,比如上图中的"拼音码"字段中的值"000000000012120",在导出后就显示"12120",挺郁闷的!o(∩_∩)o,废话不说了,进入正题.......2.首先添加Excel引用3.实现代码/// <summary>/// DataGridView导出Excel/// </summary>/// <param name="strCaption">Excel文件中的标题</param>/// <param name="myDGV">DataGridView 控件</param>/// <returns>0:成功;1ataGridView中无记录;2:Excel无法启动;9999:异常错误</returns>private int ExportExcel(string strCaption, DataGridView myDGV){int result = 9999;// 列索引，行索引，总列数，总行数int ColIndex = 0;int RowIndex = 0;int ColCount = myDGV.ColumnCount;int RowCount = myDGV.RowCount;if (myDGV.RowCount == 0){result = 1;}// 创建Excel对象Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();if (xlApp == null){result = 2;}try{// 创建Excel工作薄Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];// 设置标题Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, ColCount]); //标题所占的单元格数与DataGridView中的列数相同range.MergeCells = true;xlApp.ActiveCell.FormulaR1C1 = strCaption;xlApp.ActiveCell.Font.Size = 20;xlApp.ActiveCell.Font.Bold = true;xlApp.ActiveCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;// 创建缓存数据object[,] objData = new object[RowCount + 1, ColCount];//获取列标题foreach (DataGridViewColumn col in myDGV.Columns){objData[RowIndex, ColIndex++] = col.HeaderText;}// 获取数据for (RowIndex = 1; RowIndex < RowCount; RowIndex++){for (ColIndex = 0; ColIndex < ColCount; ColIndex++)if (myDGV[ColIndex, RowIndex - 1].ValueType == typeof(string)|| myDGV[ColIndex, RowIndex - 1].ValueType == typeof(DateTime))//这里就是验证DataGridView单元格中的类型,如果是string或是DataTime类型,则在放入缓存时在该内容前加入" ";{objData[RowIndex, ColIndex] = "" + myDGV[ColIndex, RowIndex - 1].Value;}else{objData[RowIndex, ColIndex] = myDGV[ColIndex, RowIndex - 1].Value;}}System.Windows.Forms.Application.DoEvents();}// 写入Excelrange = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount, ColCount]);range.Value2 = objData;//保存xlBook.Saved = true;xlBook.SaveCopyAs("C:\测试" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");//返回值result = 0;}catch (Exception err){result = 9999;}finally{xlApp.Quit();GC.Collect(); //强制回收}return result;}4.调用方法(上图中"生成Excel文件"按钮的onClick事件)private void button4_Click(object sender, EventArgs e){int result = this.ExportExcel("测试", this.dataGridView1); //this.dataGridView1ataGridView控件MessageBox.Show(result.ToString());。

winform中DataGridView导出Excel（使用NPOI，速度最快的一种方法）

winform中DataGridView导出Excel（使⽤NPOI，速度最快的⼀种⽅法）1,在⽹上搜索到的⼀般是这种通⽤的⽅法，这个⽅法速度太慢了，代码如下private void ExportExcel(string fileName, DataGridView myDGV){string saveFileName = "";SaveFileDialog saveDialog = new SaveFileDialog();saveDialog.DefaultExt = "xls";saveDialog.Filter = "Excel⽂件|*.xls";saveDialog.FileName = fileName;saveDialog.ShowDialog();saveFileName = saveDialog.FileName;if (saveFileName.IndexOf(":") < 0) return; //被点了取消Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();if (xlApp == null){MessageBox.Show("⽆法创建Excel对象，可能您的机⼦未安装Excel");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//写⼊标题for (int i = 0; i < myDGV.ColumnCount; i++){worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;}//写⼊数值for (int r = 0; r < myDGV.Rows.Count; r++){for (int i = 0; i < myDGV.ColumnCount; i++){worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;}System.Windows.Forms.Application.DoEvents();}worksheet.Columns.EntireColumn.AutoFit();//列宽⾃适应if (saveFileName != ""){try{workbook.Saved = true;workbook.SaveCopyAs(saveFileName);}catch (Exception ex){MessageBox.Show("导出⽂件时出错,⽂件可能正被打开！\n" + ex.Message);}}xlApp.Quit();GC.Collect();//强⾏销毁MessageBox.Show("⽂件： " + fileName + ".xls 保存成功", "信息提⽰", MessageBoxButtons.OK, rmation);}2,最近研究了半天，使⽤NOPI的⽅法真是的太快了，秒存的感觉【2.1】使⽤：private void btn_export_Click(object sender, EventArgs e){try{ExcelHelper.ExportToExcel(this.dgv_data);new FrmConfirmSingle("⽇志导出", "⽇志记录导出成功") { TopMost = true }.ShowDialog();}catch (Exception exception){new FrmConfirmSingle("⽇志导出", "⽇志记录导出失败:"+exception.Message) { TopMost = true }.ShowDialog();}}　【2.2】主要代码如下：/// <summary>/// 由DataGridView导出Excel/// </summary>/// <param name="grid"></param>/// <param name="sheetName"></param>/// <param name="filePath"></param>/// <returns></returns>public static string ExportToExcel(DataGridView grid, string sheetName = "result", string filePath = null){if (grid.Rows.Count <= 0) return null;if (string.IsNullOrEmpty(filePath)){filePath = GetSaveFilePath();}IWorkbook workbook = CreateWorkbook(isCompatible);ICellStyle cellStyle = GetCellStyle(workbook);ISheet sheet = workbook.CreateSheet(sheetName);IRow headerRow = sheet.CreateRow(0);for (int i = 0; i < grid.Columns.Count; i++){ICell cell = headerRow.CreateCell(i);cell.SetCellValue(grid.Columns[i].Name);cell.CellStyle = cellStyle;}int rowIndex = 1;foreach (DataGridViewRow row in grid.Rows){IRow dataRow = sheet.CreateRow(rowIndex);for (int n = 0; n < grid.Columns.Count; n++){dataRow.CreateCell(n).SetCellValue((row.Cells[n].Value ?? "").ToString());}rowIndex++;}AutoColumnWidth(sheet, stCellNum - 1);FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);workbook.Write(fs);fs.Dispose();sheet = null;headerRow = null;workbook = null;MessageBox.Show("⽂件： " + filePath + ".xls 保存成功", "信息提⽰", MessageBoxButtons.OK, rmation); return filePath;}　【2.3】下⾯是我整理的通⽤EXCEL的帮助类，⾮常的实⽤using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.IO;using System.Data;using System.Windows.Forms;using erModel;using erModel;using erModel;using System.Collections;using pilerServices;/** 须在项⽬中添加引⽤ NPOI.dll NPOI.OOXML.dll NPOI.OPenXml4Net.dll*/namespace AutomaticStoreMotionDal{public static class ExcelHelper{/// <summary>/// 获取要保存的⽂件名称（含完整路径）/// </summary>/// <returns></returns>public static string GetSaveFilePath(){SaveFileDialog saveFileDig = new SaveFileDialog();saveFileDig.Filter = "Excel Office97-2003(*.xls)|*.xls|Excel Office2007及以上(*.xlsx)|*.xlsx";saveFileDig.FileName = DateTime.Now.ToString("yyyyMMddHHmmss");saveFileDig.FilterIndex = 0;saveFileDig.OverwritePrompt = true;string dir = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);//获取当前系统桌⾯路径saveFileDig.InitialDirectory = dir;string filePath = null;if (saveFileDig.ShowDialog() == DialogResult.OK){filePath = saveFileDig.FileName;}return filePath;}/// <summary>/// 打开⽂件对话框，并返回⽂件的路径/// </summary>/// <returns></returns>public static string GetOpenFilePath(){//创建对话框的对象ofd.Multiselect = true;//设置对话框的初始⽬录ofd.InitialDirectory = @"C:\Users\Administrator\Desktop";//设置对话框打开⽂件的类型ofd.Filter = "Excel⽂件(.xls)|*.xls|Excel⽂件(.xlsx)|*.xlsx";//展⽰对话框ofd.ShowDialog();//获得在打开对话框中选中的⽂件的路径string filePath = ofd.FileName;//全路径return filePath;}/// <summary>/// 判断Excel⽂件是否为兼容模式（.xls)/// </summary>/// <param name="filePath"></param>/// <returns></returns>public static bool GetIsCompatible(string filePath){return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);}/// <summary>/// 创建⼯作薄/// </summary>/// <param name="isCompatible">true就是.xls</param>/// <returns></returns>public static IWorkbook CreateWorkbook(bool isCompatible){if (isCompatible){return new HSSFWorkbook();}else{return new XSSFWorkbook();}}/// <summary>/// 创建⼯作薄(依据⽂件流)/// </summary>/// <param name="isCompatible"></param>/// <param name="stream"></param>/// <returns></returns>public static IWorkbook CreateWorkbook(bool isCompatible, Stream stream) {if (isCompatible){return new HSSFWorkbook(stream);}else{return new XSSFWorkbook(stream);}}#region 传⼊⼀个⽂件路径，返回⼀个IWorkbook对象/// <summary>/// 传⼊⼀个⽂件路径，返回⼀个IWorkbook对象/// </summary>/// <param name="filepath"></param>/// <returns></returns>public static IWorkbook CreateWorkbook(string filepath){IWorkbook workbook = null;bool isCompatible = ExcelHelper.GetIsCompatible(filepath);using (FileStream fs = File.Open(filepath, FileMode.Open,FileAccess.Read, FileShare.ReadWrite)){//把xls⽂件读⼊workbook变量⾥，之后就可以关闭了workbook = ExcelHelper.CreateWorkbook(isCompatible, fs);fs.Close();}return workbook;}#endregion#region 打开⼀个excel⽂件，设置单元格的值，再保存⽂件/// <summary>/// 打开⼀个excel⽂件，设置单元格的值，再保存⽂件/// </summary>/// <param name="ExcelPath"></param>/// <param name="sheetname"></param>/// <param name="column"></param>public static bool SetCellValue(String ExcelPath, String sheetname, int column, int row, String value) {bool returnb = false;try{IWorkbook wk = null;bool isCompatible = ExcelHelper.GetIsCompatible(ExcelPath);using (FileStream fs = File.Open(ExcelPath, FileMode.Open,FileAccess.Read, FileShare.ReadWrite)){//把xls⽂件读⼊workbook变量⾥，之后就可以关闭了wk = ExcelHelper.CreateWorkbook(isCompatible, fs);fs.Close();}//把xls⽂件读⼊workbook变量⾥，之后就可以关闭了//ISheet sheet = wk.GetSheet(sheetname);ISheet sheet = wk.GetSheetAt(0);ICell cell = sheet.GetRow(row).GetCell(column);cell.SetCellValue(value);using (FileStream fileStream = File.Open(ExcelPath,FileMode.OpenOrCreate, FileAccess.ReadWrite)){wk.Write(fileStream);fileStream.Close();}returnb = true;}catch (Exception){returnb = false;throw;}return returnb;}#endregion#region 打开⼀个⽂件，读取excel⽂件某个单元格的值（多少⾏，多少列）/// <summary>/// 打开⼀个⽂件，读取excel⽂件某个单元格的值（多少⾏，多少列）/// </summary>/// <param name="ExcelPath"></param>/// <param name="sheetname"></param>/// <param name="column"></param>/// <param name="row"></param>/// <returns></returns>public static String GetCellValue(string ExcelPath, String sheetname, int column, int row){String returnStr = null;try{IWorkbook wk = null;bool isCompatible = ExcelHelper.GetIsCompatible(ExcelPath);using (FileStream fs = File.Open(ExcelPath, FileMode.Open,FileAccess.Read, FileShare.ReadWrite)){//把xls⽂件读⼊workbook变量⾥，之后就可以关闭了wk = ExcelHelper.CreateWorkbook(isCompatible, fs);fs.Close();}//把xls⽂件读⼊workbook变量⾥，之后就可以关闭了//ISheet sheet = wk.GetSheet(sheetname);ISheet sheet = wk.GetSheetAt(0);ICell cell = sheet.GetRow(row).GetCell(column);returnStr = cell.ToString();}catch (Exception){returnStr = "Exception";throw;}return returnStr;}#endregion#region 打开⼀个⽂件，删除多少⾏以后的数据（是删除，不是清空数据）/// <summary>/// 打开⼀个⽂件，删除多少⾏以后的数据（是删除，不是清空数据）/// </summary>/// <param name="fileMatchPath"></param>/// <param name="rowIndex">从多少⾏后开始删除</param>public static void DelRowsData(string fileMatchPath, int startRowIndex){IWorkbook wk = null;bool isCompatible = ExcelHelper.GetIsCompatible(fileMatchPath);using (FileStream fs = File.Open(fileMatchPath, FileMode.Open,FileAccess.Read, FileShare.ReadWrite)){//把xls⽂件读⼊workbook变量⾥，之后就可以关闭了ISheet sheet = wk.GetSheetAt(0);for (int i = startRowIndex; i <= stRowNum; i++){if (sheet.GetRow(i) == null){i++;continue;}sheet.RemoveRow(sheet.GetRow(i));}//转为字节数组MemoryStream stream = new MemoryStream();wk.Write(stream);var buf = stream.ToArray();//保存为Excel⽂件这种⽅式能保存.xls和.xlsx⽂件using (FileStream fs = new FileStream(fileMatchPath, FileMode.Create, FileAccess.Write)){fs.Write(buf, 0, buf.Length);fs.Flush();}}#endregion/// <summary>/// 创建表格头单元格/// </summary>/// <param name="sheet"></param>/// <returns></returns>private static ICellStyle GetCellStyle(IWorkbook workbook){ICellStyle style = workbook.CreateCellStyle();style.FillPattern = FillPattern.SolidForeground;style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;return style;}/// <summary>/// 遍历打印⼆维数组/// </summary>/// <param name="array"></param>public static void PrintTwoArrayTest(object[,] array){Console.WriteLine("============测试打印⼆维数组==============");int row = array.GetLength(0);int column = array.GetLength(1);for (int r = 0; r < row; r++){for (int c = 0; c < column; c++){if (array[r, c] != null){string value = array[r, c].ToString();Console.Write($"{value} |");}}Console.WriteLine();}}/// <summary>/// 传⼊2个⼆维数组，进⾏条件匹配替换，返回替换后的⼀个⼆维数组/// </summary>/// <param name="refArray">参考的数组</param>/// <param name="matchArray">带替换的数组</param>/// <param name="refColumn01">参考列1</param>/// <param name="refColumn02">参考列2</param>/// <param name="refColTarget01">被复制的值的列1</param>/// <param name="matchColumn01">带替换的参考列1</param>/// <param name="matchColumn02">带替换的参考列2</param>/// <param name="matchColTarget01">带粘贴的值的列1</param>/// <returns></returns>public static string[,] GetMatchArray(string[,] refArray, string[,] matchArray, int refColumn01, int refColumn02, int refColTarget01, int matchColumn01, int matchColumn02, int matchColTarget01) {Console.WriteLine("============遍历2个⼆维数,匹配替换==============");int row = refArray.GetLength(0);int column = matchArray.GetLength(1);int row02 = matchArray.GetLength(0);int iMatch = 0;for (int r = 0; r < row; r++){string value01 = refArray[r, refColumn01];//第1列的数据string value02 = refArray[r, refColumn02];//第2列的数据if (value01 != null && value02 != null){if (value01.Length > 0 | value02.Length > 0){for (int r02 = 0; r02 < row02; r02++){{matchArray[r02, matchColTarget01] = refArray[r, refColTarget01];iMatch++;Console.WriteLine($"匹配了{iMatch}次");}}}}}return matchArray;}/// <summary>/// 传⼊2个数组，根据相同条件匹配，吧ref的⽬标写⼊match中/// </summary>/// <param name="refArray">参考的数组</param>/// <param name="matchArray">带替换的数组</param>/// <param name="refColumn01"></param>/// <param name="refColTarget01"></param>/// <param name="matchColumn01"></param>/// <param name="matchColTarget01"></param>/// <returns></returns>public static string[,] GetMatchArray(string[,] refArray, string[,] matchArray, int refColumn01, int refColTarget01, int matchColumn01, int matchColTarget01) {Console.WriteLine("============遍历2个⼆维数,匹配替换==============");int row = refArray.GetLength(0);int column = matchArray.GetLength(1);int row02 = matchArray.GetLength(0);int iMatch = 0;for (int r = 0; r < row; r++){string value01 = string.Empty;value01 = refArray[r, refColumn01];//遍历第⼀个数组第1列的数据//value01 = value01.Trim();if (value01 != null){if (value01.Length > 0){for (int r02 = 0; r02 < row02; r02++){string match01 = string.Empty;match01 = matchArray[r02, matchColumn01];//遍历第⼀个数组第1列的数据//match01 = match01.Trim();if (value01 == match01){matchArray[r02, matchColTarget01] = refArray[r, refColTarget01];iMatch++;Console.WriteLine($"匹配了{iMatch}次");}}}}}return matchArray;}/// <summary>/// 遍历⼀个数组，如果第⼆列的数值⼤于等于第⼀列的数值，替换字符串/// </summary>/// <param name="matchArray"></param>/// <param name="refColumn01"></param>/// <param name="refColumn02"></param>/// <param name="sValue"></param>/// <returns></returns>public static string[,] GetMatchArray(string[,] matchArray, int refColumn01, int refColumn02, string sValue){Console.WriteLine("============遍历2个⼆维数,匹配替换==============");int row = matchArray.GetLength(0);int column = matchArray.GetLength(1);int iMatch = 0;for (int r = 0; r < row; r++){string value01 = matchArray[r, refColumn01];//第1列的数据string value02 = matchArray[r, refColumn02];//第2列的数据try{int i01 = Convert.ToInt32(value01);int i02 = Convert.ToInt32(value02);if (i01 >= i02){matchArray[r, refColumn02] = sValue + $"（数量：{value02}）";}}catch{}}return matchArray;}#region 打开excel⽂件，获取某⼀⾏的数据/// <param name="filepath">⽂件全路径</param>/// <param name="iRow">哪⼀⾏的数据</param>/// <param name="sheet_Number">哪⼀个sheet表</param>/// <returns></returns>public static ArrayList GetRowData(string filepath, int sheet_Number, int iRow){ArrayList arrayList = new ArrayList();bool isCompatible = ExcelHelper.GetIsCompatible(filepath);using (FileStream fsRead = File.OpenRead(filepath)){IWorkbook workbook = ExcelHelper.CreateWorkbook(isCompatible, fsRead);ISheet sheet = workbook.GetSheetAt(sheet_Number - 1);IRow currentRow = sheet.GetRow(iRow - 1);for (int c = 0; c < stCellNum; c++){//获取每个单元格(r⾏c列的数据）ICell cell = currentRow.GetCell(c);//获取单元格的内容string value = string.Empty;if (cell != null){value = cell.ToString(); //如果单元格为空，这⾥会报错的arrayList.Add(value);}}return arrayList;}}#endregion/// <summary>/// 打开excel⽂件，根据某⼀⾏的数据，根据字符串内容，返回这个字符串所在的列的索引/// </summary>/// <param name="filepath"></param>/// <param name="iRow"></param>/// <param name="sheet_Number">从1开始的</param>/// <param name="s1">注意字符串的顺序</param>/// <param name="s2"></param>/// <param name="s3"></param>/// <returns></returnsspublic static ArrayList GetDataIndexs(string filepath, int sheet_Number, int iRow, string s1, string s2, string s3) {ArrayList arrayList = new ArrayList();bool isCompatible = ExcelHelper.GetIsCompatible(filepath);using (FileStream fsRead = File.OpenRead(filepath)){IWorkbook workbook = ExcelHelper.CreateWorkbook(isCompatible, fsRead);ISheet sheet = workbook.GetSheetAt(sheet_Number - 1);IRow currentRow = sheet.GetRow(iRow - 1);for (int c = 0; c < stCellNum; c++){//获取每个单元格(r⾏c列的数据）ICell cell = currentRow.GetCell(c);//获取单元格的内容string value = string.Empty;if (cell != null){value = cell.ToString(); //如果单元格为空，这⾥会报错的if (value == s1 | value == s2 || value == s3){arrayList.Add(c);}}}Console.WriteLine("==========测试打印索引值============");foreach (var a in arrayList){Console.WriteLine($"{a} |");}return arrayList;}}/// <summary>/// 打开excel⽂件，根据某⼀⾏的字符串，然后这个字符串所在列的索引/// </summary>/// <param name="filepath"></param>/// <param name="sheet_Number"></param>/// <param name="iRow"></param>/// <param name="sValue"></param>/// <returns></returns>public static int GetDataIndex(string filepath, int sheet_Number, int iRow, string sValue){int i = 0;bool isCompatible = ExcelHelper.GetIsCompatible(filepath);using (FileStream fsRead = File.OpenRead(filepath)){IWorkbook workbook = ExcelHelper.CreateWorkbook(isCompatible, fsRead);ISheet sheet = workbook.GetSheetAt(sheet_Number - 1);IRow currentRow = sheet.GetRow(iRow - 1);for (int c = 0; c < stCellNum; c++){//获取每个单元格(r⾏c列的数据）ICell cell = currentRow.GetCell(c);value = cell.ToString(); //如果单元格为空，这⾥会报错的if (value == sValue){i = c;}}}}return i;}/// <summary>/// 打开⼀个⽂件，把第⼏⾏的数据取出来，返回⼀个字典单元格的值：列的索引/// </summary>/// <param name="filepath"></param>/// <param name="sheet_Number">第⼏张⼯作表（从1开始）</param>/// <param name="iRow">第⼏⾏（从1开始）</param>/// <returns></returns>public static Dictionary<string, int> GetDataDictionary(string filepath, int sheet_Number, int iRow){Dictionary<string, int> DataDict = new Dictionary<string, int>();bool isCompatible = ExcelHelper.GetIsCompatible(filepath);using (FileStream fsRead = File.OpenRead(filepath)){IWorkbook workbook = ExcelHelper.CreateWorkbook(isCompatible, fsRead);ISheet sheet = workbook.GetSheetAt(sheet_Number - 1);IRow currentRow = sheet.GetRow(iRow - 1);for (int c = 0; c < stCellNum; c++){//获取每个单元格(r⾏c列的数据）ICell cell = currentRow.GetCell(c);//获取单元格的内容string value = string.Empty;if (cell != null){value = cell.ToString(); //如果单元格为空，这⾥会报错的if (!DataDict.ContainsKey(value)){if (value == "*预计交货⽇期" | value == "预计交货⽇期"){value = "*预计交货⽇期";}DataDict.Add(value, c);}else{if (filepath.Contains("销售订单")) //销售订单模板的第⼆个备注填写收货地址{if (value == "备注") //如果有两个备注{//DataDict.Add("采购员", c);DataDict.Add("收货地址", c);}}}}}//Console.WriteLine("================开始遍历字典===============");//foreach (KeyValuePair<string, int> kv in DataDict)//通过KeyValuePair遍历元素//{// Console.WriteLine($"Key:{kv.Key},Value:{kv.Value}");//}return DataDict;}}/// <summary>/// 打开⼀个⽂件，根据第⼏张表第⼏⾏的中的两个字符，返回值：⼀个字典/// </summary>/// <param name="filepath"></param>/// <param name="sheet_Number"></param>/// <param name="strColumnKey"></param>/// <param name="strColumnValue"></param>/// <returns></returns>public static Dictionary<string, string> GetDataDictionary(string filepath, int sheet_Number, int iRow, string strColumnKey, string strColumnValue) {Dictionary<string, int> dic = GetDataDictionary(filepath, 1, iRow);int iColumnKey = dic[strColumnKey];int iColumnValue = dic[strColumnValue];Dictionary<string, string> DataDict = new Dictionary<string, string>();bool isCompatible = ExcelHelper.GetIsCompatible(filepath);using (FileStream fsRead = File.OpenRead(filepath)){IWorkbook workbook = ExcelHelper.CreateWorkbook(isCompatible, fsRead);ISheet sheet = workbook.GetSheetAt(sheet_Number - 1);for (int i = 0; i <= stRowNum; i++)ICell cellKey = rowdata.GetCell(iColumnKey);//如果rowdata是null,这⾥报错ICell cellValue = rowdata.GetCell(iColumnValue);if (cellKey != null && cellValue != null){if (!DataDict.ContainsKey(cellKey.ToString())){string strCellKey = cellKey.ToString();string strCellValue = cellValue.ToString();DataDict.Add(strCellKey, strCellValue);}}}}return DataDict;}}/// <summary>/// ⾃适应列宽/// </summary>/// <param name="sheet"></param>/// <param name="cols"></param>public static void AutoColumnWidth(ISheet sheet, int cols){for (int col = 0; col <= cols; col++){sheet.AutoSizeColumn(col);//⾃适应宽度，但是其实还是⽐实际⽂本要宽int columnWidth = sheet.GetColumnWidth(col) / 256;//获取当前列宽度for (int rowIndex = 1; rowIndex <= stRowNum; rowIndex++){IRow row = sheet.GetRow(rowIndex);ICell cell = row.GetCell(col);if (cell != null){int contextLength = Encoding.UTF8.GetBytes(cell.ToString()).Length;//获取当前单元格的内容宽度 columnWidth = columnWidth < contextLength ? contextLength : columnWidth;}}sheet.SetColumnWidth(col, columnWidth * 200);//经过测试200⽐较合适。

把WinForm的DataGridView的数据导出到Excel三种方法

把WinForm的DataGridView的数据导出到Excel三种方法导出WinForm的DataGridView数据到Excel有多种方法，下面将详细介绍三种常用的方法：方法一：使用Microsoft.Office.Interop.Excel库这是一种常用的方法，使用Microsoft.Office.Interop.Excel库可以直接操作Excel文件。

首先，需要在项目中添加对Microsoft Office 的引用。

然后，可以按照以下步骤导出数据：1. 创建一个Excel应用程序对象：```csharpusing Excel = Microsoft.Office.Interop.Excel;Excel.Application excelApp = new Excel.Application(;```2.创建一个工作簿对象：```csharpExcel.Workbook workbook =excelApp.Workbooks.Add(Type.Missing);```3.创建一个工作表对象：```csharpExcel.Worksheet worksheet = workbook.ActiveSheet;```4. 将DataGridView中的数据导入到Excel中：```csharpfor (int i = 0; i < dataGridView.Rows.Count; i++)for (int j = 0; j < dataGridView.Columns.Count; j++)worksheet.Cells[i + 1, j + 1] =dataGridView.Rows[i].Cells[j].Value.ToString(;}```5. 保存Excel文件并关闭Excel应用程序：```csharpworkbook.SaveAs("路径\\文件名.xlsx");excelApp.Quit(;```方法二：使用OpenXml库OpenXml是一种用于操作Office文件的开放式标准。

DBGridEh多行表头导出至Excel研究

步骤四：表美化报
图６合并行结果示意图
冀甓鲁名 ■
证蕴嚣ｌ而箍确嚣ｌ
Ｉ锄士比曩ｆ ‘ ，Ｉ，Ｉｆｏ锄隹棒钟帮机，ｆｆ｝嚣ｌｊｏ蝴ｍｆ
ｎ《ｌ ∞ 肿）１ ‘ ｍＩｍＩｌｍｊ珥晒士蕾ｌｌ１ｌ１１Ｔｏｏｏ
维普资讯
第２卷１
第１期
电脑开发与应用
多行表头导出至＠研究 Ⅱ
解放军理工大学柏林元严骏凌海风朱亮
Ｄｌｈ第三方控件Ｅｌ、ＢｉＥｅｉｐｈｉＤＧｒｈｂｄ步骤～：元格字段分解单
不变。流程图如图４所示，果如图５结所
不。
‘ ｓＩｌ＿嚣伽刊
” ｛ｊ
Ｉｌ。０
图５合并列结果示意图
然后把三个字段分别填到该列的三行里。依此类推，标题行的每一格单元格进行对类似处理：先判断有几个 “ ” 遍历标题首，行的所有单元格，录每个单元格的“” 记Ｉ数目，出最大数ｎ则需要再插入 ”个空找，行。然后把每个单元格的字段分解，别分
ＳｖＤＧｉｈＥｐｒｉａｅＢｒＥＴｏｘｏｔｌｄＦｅ函数把报表大字段个数，即需增加的空白行数。结果
导出到Ｅｃｌ件中，用户可以对报表如图３示。ｘｅ文则所的格式、容、眉页脚、面设置、印内页页打设置等进行全面修改，是该方法在从但ＤＧｒＥ中导出到Ｅｃｌ过程中，Ｂｉｈｄｘｅ的多

DxDbGrid与DbGridEh表格使用及导出Excel

DxDbGrid与DbGridEh表格使用及导出Excel分类：Delphi 2011-05-16 09:47 110人阅读评论(0) 收藏举报前言：二者都是非常不错的第三方表格控件，都可实现多表头的表格及分组汇总功能；在导出 Excel 方面，个人觉得 DxDbGrid 做的比DbGridEh 出色，几乎是 Grid 原样导出， DbGridEh 导出表格模式的单元格与 Grid 显示有所出入。

一、所用版本及安装：1 、版本： DevExQuantumGrid v3.22 Pro for D7 、 EhLib 5.2.842 、安装： DevExQuantumGrid 直接 Setup.exe 就 OK ； EhLib 在Delphi7 的安装稍微啰嗦点，具体步骤参考 readme.txt 或如下步骤：(1). 将 EhLib 5.2.84 解压缩到目标目录。

(2). 打开 Delphi 7 ，将 EhLib 的 /Delphi7 子目录加到 Delphi 的 Library path 。

( 菜单操作路径为：Tools|Environment Options...|Library|Library path)(3). 将 EhLib 目标安装目录中的 common 和 DataService子目录的文件移动到 EhLib 的 /Delphi7 子目录中。

(4). 在 Delphi 7 中打开 EhLib70.dpk ，编译，但不要安装。

(5). 在 Delphi 7 中打开 DclEhLib70.dpk ，编译并安装。

(6). 组件面板中出现一个 EhLib 的组件页。

(7). 打开附带的 DEMOS ，编译并运行，测试安装成功。

二、使用 DxDbGrid1 、窗体拖入 dxDBGrid1 、 ADOConnection1 、 ADOQuery1 、DataSource1 、 Button1 、 SaveDialog1 ，然后完成数据的链接及相关控件关联，如何操作，你应该懂的；接下来完成如下图所示的一个表格：2 、双击 dxDBGrid1 ，在 Bands 栏增加 TdxTreeListBand 并填写Caption ，注意要将 dxDBGrid1 的 ShowBands 属性设置为 True 才能显示 Bands 栏；同样双击 dxDBGrid1 ，在 Columns 栏，添加多个dxGridColumn （根据需要选择不同的类型）并使其与数据库字段形成关联，涉及如下几个属性， BandIndex 选择对应的 Band 从而形成二级表头， Caption 、 FieldName 、 HeaderAlignment 、 width 等，如此完成了基本的表格设计，如果喜欢表头平滑更改 LookAndFeel 属性为 lfFlat 即可。

C# winform Datagridview导出Excel两个方法

saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出文件保存路径";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
//strName储存保存EXCEL路径
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true); ;
//生成字段名称，逐条写，无效率
for (int i = 0; i < gridView.ColumnCount; i++)
{
/// </summary>
/// <param name="gridView"></param>
/// <param name="saveFileDialog"></param>
public void ToExcel1(DataGridView gridView, SaveFileDialog saveFileDialog)
{
try
{
if (gridView.Rows.Count == 0)

DBGridEh导入到Excel

-----------------------------------------HmExcel.pas-----------------------------------------Delphi中DBGridEh导出数据到Excel中，支持多级（最多三级）层级列标题、支持页脚汇总栏导出。

日期、数字等形式的字符串类型自动使用Text格式，保持原有数据类型，防止Excel自作多情删除数字前导0，或将“-”、识别为日期等。

用法：将HmExcel.pas放置在Delphi的搜索路径下，或程序路径下添加该单元到工程中，然后Use该单元，或者直接将HmExcel添加到uses中。

然后就可以使用ExpToExcel 函数了。

unit HmExcel;interfaceusesForms,ComObj,DBGridEh,SysUtils,DB,Variants;function ExpToExcel(dbg:TDBGridEh;title:string;subTitle:string=''):Boolean;implementation{编写:HanMon 2006-07-10 最后修改:2012-09-11功能:DBGridEh导出到Excel,支持多表头合并(最多三列)，合计栏导出声明:Procedure ExpToExcel(dbg:TDBGridEh;title:string;subTitle:string='');参数:1.dbg :控件,三方控件EhLib控件包中DBGridEh控件实例2.title :字符,导出Excel的第一行，默认字体24号3.subTitle :字符:子标题,默认为当前日期，可以传入导出的时间段注意:1.只支持最多三层标题的合并(多了也是浪费)2.多层标题中如(ABC|A,ABC,ABC|C)在表格中支持合并,即第二行第二列为空ABCA, ,C由于没有实际意义,本单元不支持,只支持如(ABC|A,ABC|B,ABC|C)严格以分割符'|'来识别3.如果有Footer,本单元只支持一行Footer导出}// 01.将数字列转换为Excel格式的字母列function getXlsCol(const col:integer):string;varm,n:integer;beginif (col<=0)or(col>256) thenresult:='Error'//异常(Error)else if col<=26 then beginm:=col+64;result:=chr(m);end else beginm:=(col-1)div 26;n:=col-26*m+64;result:=getXlsCol(m)+chr(n);end;end;{-------------------------------------------------}// 02.获得分层标题的层次数function getLevel(aTitle:string):integer;vari:integer;beginResult:=1;for i:=1 to length(aTitle) doif aTitle[i]='|' thenResult:=Result+1;end;{-------------------------------------------------}// 03.获得分层标题的最大层次function getMaxLevel(dbg:TDBGridEh):integer;varm,i:integer;beginResult:=1;for i:=0 to dbg.Columns.Count-1 do beginm:=getLevel(dbg.Columns[i].Title.Caption);if m>Result thenResult:=m;end;end;{-------------------------------------------------}// 04.获得同一层的结束位置(level=1顶层level=2次顶层)procedure getBeginEndCol(dbg:TDBGridEh;start,level:integer;var c1,c2:integer); vars,s1:string;beginc1:=start;c2:=start;s:=dbg.Columns[start].Title.Caption;if level=1 then beginif pos('|',s)=0 thenexit;s:=copy(s,1,pos('|',s))end else if level=2 then begins1:=copy(s,1,pos('|',s));s:=copy(s,pos('|',s)+1,maxInt);if pos('|',s)=0 thenexit;s:=s1+copy(s,1,pos('|',s));end;while (c1>=0)and(pos(s,dbg.Columns[c1].Title.Caption)>0) doc1:=c1-1;c1:=c1+1;while (c2<=(dbg.Columns.Count-1))and(pos(s,dbg.Columns[c2].Title.Caption)>0) do c2:=c2+1;c2:=c2-1;end;{-------------------------------------------------}// A.导出到Excel:参数1:第三方控件表格,参数2:标题,参数3:副标题function ExpToExcel(dbg:TDBGridEh;title:string;subTitle:string=''):Boolean;vari,j,k,col,maxLevel:integer;app,bok,sht:variant;s,s1:string;beginResult:=False;trytryapp:=getActiveOleObject('excel.application');exceptapp:=createOleObject('excel.application');end;exceptexit;end;trybok:=app.workbooks.add;sht:=app.worksheets[1];col:=dbg.Columns.Count;sht.cells(1,1):=title;if subTitle='' thensubTitle:=FormatDateTime('yyyy-mm-dd',date);sht.cells(2,1):=subTitle;// 可转换成数字或日期时间的字符串,Excel会自动转换成对应数据类型,可牵制设置成文本方式显示for i:=0 to dbg.Columns.Count-1 doif dbg.Columns[i].Field.DataType in [ftString,ftMemo,ftFixedChar,ftWideString,ftFmtMemo] then beginsht.range[format('%s:%s',[getXlsCol(i+1),getXlsCol(i+1)])].NumberFormatLocal:='@';end;//在Excel获得和分层表格相同的单元格合并// 例: ID AB|A AB|B CDE|CD|C CDE|CD|D CDE|E FmaxLevel:=getmaxLevel(dbg);for i:=0 to dbg.Columns.Count-1 do begin//所有数字以字符表示,可以调整为某部分列数字以文本表示s:=dbg.Columns[i].Title.Caption;//单层垂直合并if pos('|',s)=0 then beginsht.cells(3,i+1):=s;if maxLevel>1 thensht.range[format('%s3:%s%d',[getXlsCol(i+1),getXlsCol(i+1),maxLevel+2])].merge;continue;end elses1:=copy(s,1,pos('|',s)-1);s:=copy(s,pos('|',s)+1,maxInt);getBeginEndCol(dbg,i,1,j,k);//第三行合并if i=j then beginsht.cells(3,i+1):=s1;if j<>k thensht.range[format('%s3:%s3', [getXlsCol(j+1),getXlsCol(k+1)])].merge;end;//双层垂直合并if pos('|',s)=0 then beginsht.cells(4,i+1):=s;if maxLevel>2 thensht.range[format('%s4:%s%d',[getXlsCol(i+1),getXlsCol(i+1),maxLevel+2])].merge;continue;end;//三层getBeginEndCol(dbg,i,2,j,k);if i=j then beginsht.cells(4,i+1):=copy(s,1,pos('|',s)-1);if j<>k thensht.range[format('%s4:%s4', [getXlsCol(j+1),getXlsCol(k+1)])].merge;end;sht.cells(5,i+1):=copy(s,pos('|',s)+1,maxInt);end;//导出数据dbg.DataSource.DataSet.First;i:=2+MaxLevel;while not dbg.DataSource.DataSet.Eof do begini:=i+1;sht.cells(i,1):=i-3;for j:=0 to col-1 doif dbg.Columns[j].Field<>nil then //忽略不与数据库字段对应的列sht.cells(i,j+1):=dbg.Columns[j].Field.DisplayText;dbg.DataSource.DataSet.Next;end;dbg.DataSource.DataSet.First;//导出页脚统计数据数据if dbg.FooterRowCount>0 then begini:=i+1;for j:=0 to col-1 doif dbg.Columns[j].Footer.ValueType=fvtStaticText thensht.cells(i,j+1):=dbg.Columns[j].Footer.Valueelse if dbg.Columns[j].Footer.ValueType=fvtNon thencontinueelsesht.cells(i,j+1):=varToStr(dbg.Columns[j].Footer.SumValue);end;//设置Excel格式s:=format('A1:%s1', [getXlsCol(col)]); //主标题(第一行)sht.range[s].merge;sht.range[s].font.size:=14;s:=format('A2:%s2',[getXlsCol(col)]); //副标题(第二行)sht.range[s].merge;sht.range[s].font.size:=10;s:=format('A1:%s%d',[getXlsCol(col),2+MaxLevel]); //主标题，副标题，列名(前三行)水平垂直居中,粗体sht.range[s].HorizontalAlignment:=-4108;sht.range[s].VerticalAlignment:=-4108;sht.range[s].font.bold:=true;if dbg.FooterRowCount>0 then //列名，数据区加边框(不包括页脚区)sht.range[format('A3:%s%d',[getXlsCol(col),i-1])].borders.lineStyle:=1 elsesht.range[format('A3:%s%d',[getXlsCol(col),i])].borders.lineStyle:=1;s:=format('A3:%s%d',[getXlsCol(col),i]); //列名，数据，统计行字体10,自动换行，加边框sht.range[s].font.size:=10;sht.columns[format('A:%s',[getXlsCol(col)])].EntireColumn.AutoFit; //自动适应列宽bok.saved:=true;app.visible:=true;excepton e:Exception do beginApplication.MessageBox(PChar(e.Message+#13+' 数据导出异常，操作取消！'),'提示');bok.saved:=true;app.quit;end;end;sht:=unAssigned;bok:=unAssigned;app:=unAssigned;Result:=True;end;end.。

winform应用使用DataGridView数据导出到Excel

Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Valu
用
if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
t ry
{
f ile.Delet e();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失败 ", M
头
int displayColumnsCount = 1;
for (int i = 0; i <= dgv.ColumnCount - 1; i++)
径 dlg.InitialDirectory = Directory.GetCurrentDirectory(); //打开保存对话框 if (dlg.ShowDialog() == DialogResult.Cancel) return; //返回文件路径 string fileNameString = dlg.FileName; //验证strF
示 ", MessageBoxButtons.OK, rmation);
return; }
//行数不可以大于65536 if (rowscount > 65536) {
MessageBo x.Sho w("数据记录数太多(最多不能超过65536条)，
它 FileInfo file = new FileInfo(fileNameString); if (file.Exists) {

GridView导出Excel的方法以及导出后文件怎样变成文本的形式

GridView导出Excel的方法以及导出后文件怎样变成文本的形式GridView导出Excel的方法一、带有复选框的导出excel文件protected void dc_Click(object sender, EventArgs e){Export("application/ms-excel", "预订已通过人员.xls");}//"预订已通过人员.xls"为excel文件的表头private void Export(string FileType, string FileName){Response.ContentEncoding = System.Text.Encoding.UTF7;Response.Clear();Response.Buffer = false;Response.Charset = "GB2312";Response.AppendHeader("Content-Disposition","attachment;filename=exl.xls");Response.ContentEncoding =System.Text.Encoding.GetEncoding("GB2312");Response.ContentType = "application/ms-excel";Response.Write("content=\\"text/html; charset=GB2312\\">");this.EnableViewState = false;//转换为table 表格HtmlTable ht = new HtmlTable();ht.Border = 1;HtmlTableRow htrt = new HtmlTableRow();HtmlTableCell htct = new HtmlTableCell();htct.ColSpan = 15;htct.Align = "center";htct.InnerHtml = "预订已通过人员";htrt.Cells.Add(htct);ht.Rows.Add(htrt);HtmlTableRow htrh = new HtmlTableRow();string[] strArray = { "ID", "领取ID", "工号", "姓名", "性别", "部门", "工种", "状态", "领取类型", "代领人", "物品", "预订时间","审核时间", "数量", "物品型号","接收人签字" };for (int i = 0; i < 16; i++){HtmlTableCell htc = new HtmlTableCell();htc.InnerHtml = "" + strArray[i] + "";htrh.Cells.Add(htc);}ht.Rows.Add(htrh);for (int i = 0; i < GV_ypck.Rows.Count; i++)//取行{HtmlTableRow htr = null;for (int j = 0; j < 17; j++)//取列{if (j == 0){CheckBox cb4this =(CheckBox)GV_ypck.Rows[i].Cells[0].FindControl("CheckBox1 ");if (!cb4this.Enabled || !cb4this.Checked){break;//取消取列elsehtr = new HtmlTableRow();}else if (j >= 1 && j <= 16){HtmlTableCell htc = new HtmlTableCell();htc.InnerT ext =(GV_ypck.Rows[i].Cells[j].Text.Trim() == " " ? "" :GV_ypck.Rows[i].Cells[j].Text.Trim());htr.Cells.Add(htc);}}if (htr != null && htr.Cells.Count > 0){ht.Rows.Add(htr);}}System.IO.StringWriter oStringWriter = newSystem.IO.StringWriter();HtmlTextWriter oHtmlTextWriter = newHtmlTextWriter(oStringWriter);//ht.Attributes.Add("style", "vnd.ms-excel.numberformat:@"); ht.RenderControl(oHtmlTextWriter);Response.Write(oStringWriter.ToString());Response.End();}//未将GridView放到runat="server"里public override void VerifyRenderingInServerForm(Control control) {二、不带复选框的导出excel文件//在gridview绑定时进行设置导出的excel文件为文本形式//ydwp.DataSource = dt;//ydwp.Attributes.Add("style", "vnd.ms-excel.numberformat:@");//ydwp.DataBind();// 导出列表信息到Excelprotected void dc_Click(object sender, EventArgs e){Export("application/ms-excel", "已领取人员表.xls");}private void Export(string FileType, string FileName){ydwp.AllowPaging = false;Response.Charset = "GB2312";Response.ContentEncoding = System.Text.Encoding.UTF7;Response.Clear();Response.Buffer = false;Response.Charset = "GB2312";Response.AppendHeader("Content-Disposition","attachment;filename=exl.xls");Response.ContentEncoding =System.Text.Encoding.GetEncoding("GB2312");Response.ContentType = "application/ms-excel";Response.Write("");//this.EnableViewState = false;System.IO.StringWriter oStringWriter = newSystem.IO.StringWriter();HtmlTextWriter oHtmlTextWriter = newHtmlTextWriter(oStringWriter);ydwp.RenderControl(oHtmlTextWriter);Response.Write(oStringWriter.ToString());Response.End();}//注：三、在导出的excel文件中往往需要将excel文件设置为文本的形式需要在方法或者绑定gridview中加.Attributes.Add("style", "vnd.ms-excel.numberformat:@");。

C++DBGrid中据出到Word和Excel

一、将DBGrid中的内容导出到Word文档//---------------------------------------------------------------------------// 将DBGrid中的数据导出到Word文档//---------------------------------------------------------------------------void __fastcall DBGrid2Word(TDBGrid *dbg, String strDocFile){if(!dbg->DataSource->DataSet->Active) // 数据集没有打开就返回return;Variant vWordApp, vTable, VCell;try{vWordApp = Variant::CreateObject("Word.Application");}catch(...){MessageBox(0, "启动 Word 出错, 可能是没有安装Word.","DBGrid2Word", MB_OK | MB_ICONERROR);vWordApp = Unassigned;return;}// 隐藏Word界面vWordApp.OlePropertySet("Visible", false);// 新建一个文档vWordApp.OlePropertyGet("Documents").OleFunction("Add");Variant vSelect = vWordApp.OlePropertyGet("Selection");// 设置一下字体，大小vSelect.OlePropertyGet("Font").OlePropertySet("Size", dbg->Font->Size);vSelect.OlePropertyGet("Font").OlePropertySet("Name", dbg->Font->Name.c_str());// 要插入表格的行数int nRowCount(dbg->DataSource->DataSet->RecordCount + 1);nRowCount = nRowCount < 2? 2: nRowCount;// 要插入表格的列数int nColCount(dbg->Columns->Count);nColCount = nColCount < 1? 1: nColCount;// 在Word文档中插入与DBGrid行数列数基本相同的一个表格vWordApp.OlePropertyGet("ActiveDocument").OlePropertyGet("Tables").OleProcedure("Add", vSelect.OlePropertyGet("Range"),nRowCount, // 行数nColCount, // 列数1, // DefaultTableBehavior:=wdWord9TableBehavior0); // AutoFitBehavior:=wdAutoFitFixed// 操作这个表格vTable = vWordApp.OlePropertyGet("ActiveDocument").OleFunction("Range").OlePropertyGet("Tables").OleFunction("Item", 1);// 设置单元格的宽度for(int i=0; i<nColCount; i++){int nColWidth = dbg->Columns->Items[i]->Width;vTable.OlePropertyGet("Columns").OleFunction("Item", i + 1).OlePropertySet("PreferredWidthType", 3); // wdPreferredWidthPoints vTable.OlePropertyGet("Columns").OleFunction("Item", i + 1).OlePropertySet("PreferredWidth", nColWidth);}// 先将列名写入Word表格for(int j=0; j<dbg->Columns->Count; j++){vCell = vTable.OleFunction("Cell", 1, j + 1);vCell.OlePropertySet("Range", dbg->Columns->Items[j]->FieldName.c_str());// 列名单元格背景颜色 // wdColorGray125vCell.OlePropertyGet("Shading").OlePropertySet("BackgroundPatternColor", 14737632); }// 将DBGrid中的数据写入Word表格dbg->DataSource->DataSet->First();for(int i=0; i<nRowCount; i++){// 63 63 72 75 6E 2E 63 6F 6Dfor(int j=0; j<dbg->Columns->Count; j++){vCell = vTable.OleFunction("Cell", i + 2, j + 1);vCell.OlePropertySet("Range",dbg->DataSource->DataSet->FieldByName(dbg->Columns->Items[j]->FieldName)->AsString.c_str());}dbg->DataSource->DataSet->Next();}// 保存Word文档并退出vWordApp.OlePropertyGet("ActiveDocument").OleProcedure("SaveAs", strDocFile.c_str());vWordApp.OlePropertyGet("ActiveDocument").OleProcedure("Close");Application->ProcessMessages();vWordApp.OleProcedure("Quit");Application->ProcessMessages();vWordApp = Unassigned;// 工作结束MessageBox(0, "DBGrid2Word 转换结束!","DBGrid2Word", MB_OK | MB_ICONINFORMATION);}二、将DBGrid中的内容导出到Excel文档//---------------------------------------------------------------------------// 将DBGrid中的数据导出到Excel文档//---------------------------------------------------------------------------void __fastcall DBGrid2Excel(TDBGrid *dbg, String strXlsFile){if(!dbg->DataSource->DataSet->Active) // 数据集没有打开就返回return;Variant vExcelApp, vSheet;try{vExcelApp = Variant::CreateObject("Excel.Application");}catch(...){MessageBox(0, "启动 Excel 出错, 可能是没有安装Excel.","DBGrid2Excel", MB_OK |MB_ICONERROR);return;}// 隐藏Excel界面vExcelApp.OlePropertySet("Visible", false);// 新建一个工作表vExcelApp.OlePropertyGet("Workbooks").OleFunction("Add", 1); // 工作表// 操作这个工作表vSheet = vExcelApp.OlePropertyGet("ActiveWorkbook").OlePropertyGet("Sheets", 1);// 设置Excel文档的字体vSheet.OleProcedure("Select");vSheet.OlePropertyGet("Cells").OleProcedure("Select");vExcelApp.OlePropertyGet("Selection").OlePropertyGet("Font").OlePropertySet("Size",dbg->Font->Size);vExcelApp.OlePropertyGet("Selection").OlePropertyGet("Font").OlePropertySet("Name",dbg->Font ->Name.c_str());vExcelApp.OlePropertyGet("Selection").OlePropertyGet("Font").OlePropertySet("FontStyle", "常规");vSheet.OlePropertyGet("Cells", 1, 1).OleProcedure("Select");// 表格的行数int nRowCount(dbg->DataSource->DataSet->RecordCount + 1);nRowCount = nRowCount < 2? 2: nRowCount;// 表格的列数int nColCount(dbg->Columns->Count);nColCount = nColCount < 1? 1: nColCount;// 设置单元格的宽度for(int i=0; i<nColCount; i++){int nColWidth = dbg->Columns->Items[i]->Width;vExcelApp.OlePropertyGet("Columns", i + 1).OlePropertySet("ColumnWidth", nColWidth / 7);}// 先将列名写入Excel表格for(int j=0; j<dbg->Columns->Count; j++){// 标题行的行高vExcelApp.OlePropertyGet("Rows", 1).OlePropertySet("RowHeight", 20);vSheet.OlePropertyGet("Cells", 1, j + 1).OlePropertySet("Value", dbg->Columns->Items[j]->FieldName.c_str());// 设置列名单元格的背景色Variant vInter = vSheet.OlePropertyGet( "Cells", 1, j + 1).OlePropertyGet("Interior");vInter.OlePropertySet("ColorIndex", 15); // 灰色vInter.OlePropertySet("Pattern", 1); // xlSolidvInter.OlePropertySet("PatternColorIndex", -4105); // xlAutomatic}// 将DBGrid中的数据写入Excel表格dbg->DataSource->DataSet->First();for(int i=0; i<nRowCount; i++){// 普通数据行的行高16vExcelApp.OlePropertyGet("Rows", i + 2).OlePropertySet("RowHeight", 16);// 63 63 72 75 6E 2E 63 6F 6Dfor(int j=0; j<dbg->Columns->Count; j++){vSheet.OlePropertyGet("Cells", i + 2, j + 1).OlePropertySet("Value",dbg->DataSource->DataSet->FieldByName(dbg->Columns->Items[j]->FieldName)->AsString.c_str());}dbg->DataSource->DataSet->Next();}// 保存Excel文档并退出vExcelApp.OlePropertyGet("ActiveWorkbook").OleFunction("SaveAs", strXlsFile.c_str());vExcelApp.OleFunction("Quit");vSheet = Unassigned;vExcelApp = Unassigned;// 工作结束MessageBox(0, "DBGrid2Excel 转换结束!","DBGrid2Excel", MB_OK | MB_ICONINFORMATION); }。

unigui unidbgrid导出excel实例

unigui unidbgrid导出excel实例在UniGUI中，UniDBGrid是一个非常常用的组件，用于展示数据库中的数据。

有时候我们需要将UniDBGrid中的数据导出到Excel文件中，以便进行数据分析或者与其他人分享数据。

本文将介绍如何使用UniGUI中的UniDBGrid组件来实现数据导出到Excel的功能。

准备工作在开始之前，确保你已经安装了Delphi和UniGUI，并且已经创建了一个UniGUI项目并连接了数据库。

确保你的UniDBGrid已经绑定了数据源，并且数据源已经连接到数据库中的表。

导出Excel步骤步骤一：添加导出按钮首先，在UniGUI的Form中添加一个按钮，命名为“ExportExcelButton”，用于触发导出Excel的操作。

步骤二：编写导出Excel的代码在“ExportExcelButton”的OnClick事件中编写导出Excel的代码。

以下是一个示例代码：示例代码star：编程语言：delphiusesVcl.OleAuto, System.Variants;procedure TForm1.ExportExcelButtonClick(Sender: TObject);varExcelApp: Variant;i, j: Integer;beginExcelApp := CreateOleObject('Excel.Application');ExcelApp.Workbooks.Add;ExcelApp.Visible := True;// 导出表头for i := 0 to UniDBGrid1.Columns.Count - 1 dobeginExcelApp.Cells[1, i + 1] :=UniDBGrid1.Columns[i].Title.Caption;end;// 导出数据UniDBGrid1.DataSource.DataSet.First;i := 2;while not UniDBGrid1.DataSource.DataSet.Eof dobeginfor j := 0 to UniDBGrid1.Columns.Count - 1 dobeginExcelApp.Cells[i, j + 1] :=UniDBGrid1.DataSource.DataSet.FieldByName(UniDBGrid1.Columns[ j].FieldName).AsString;end;Inc(i);UniDBGrid1.DataSource.DataSet.Next;end;end;示例代码end步骤三：测试导出功能运行你的UniGUI应用程序，点击“ExportExcelButton”按钮，即可将UniDBGrid中的数据导出到Excel文件中。

从DBGridEh控件导出Excel

从DBGridEh控件导出Excel从DBGridEh控件导出Excel（收藏）1、调用（通过点击按钮直接导出Excel）：procedure TForm3.Button2Click(Sender: TObject);varGridtoExcel: TDBGridEhToExcel;begintryGridtoExcel := TDBGridEhT oExcel.Create(nil);GridtoExcel.DBGridEh := DBGridEh1;GridtoExcel.TitleName := '收费明细表';GridtoExcel.ShowProgress := true;GridtoExcel.ShowOpenExcel := true;GridtoExcel.ExportToExcel;finallyGridtoExcel.Free;end;end;2、具体的导出函数unit Unit_DBGridEhToExcel;interfaceusesSysUtils, Variants, Classes, Graphics, Controls, Forms, Excel2000, ComObj,Dialogs, DB, DBGridEh, windows,ComCtrls,ExtCtrls;typeTDBGridEhToExcel = class(TComponent)privateFProgressForm: TForm; {进度窗体}FtempGauge: TProgressBar; {进度条}FShowProgress: Boolean; {是否显示进度窗体}FShowOpenExcel:Boolean; {是否导出后打开Excel文件}FDBGridEh: TDBGridEh;FTitleName: TCaption; {Excel文件标题} FUserName: TCaption; {制表人}procedure SetShowProgress(const Value: Boolean); {是否显示进度条}procedure SetShowOpenExcel(const Value: Boolean); {是否打开生成的Excel文件}procedure SetDBGridEh(const Value: TDBGridEh);procedure SetTitleName(const Value: TCaption); {标题名称}procedure SetUserName(const Value: TCaption); {使用人名称}procedure CreateProcessForm(AOwner: TComponent); {生成进度窗体}publicconstructor Create(AOwner: TComponent); override;destructor Destroy; override;procedure ExportT oExcel; {输出Excel文件}publishedproperty DBGridEh: TDBGridEh read FDBGridEh writeSetDBGridEh;property ShowProgress: Boolean read FShowProgress write SetShowProgress; //是否显示进度条property ShowOpenExcel: Boolean read FShowOpenExcel write SetShowOpenExcel; //是否打开Excelproperty TitleName: TCaption read FTitleName write SetTitleName;property UserName: TCaption read FUserName write SetUserName;end;implementationconstructor TDBGridEhToExcel.Create(AOwner: TComponent);begininherited Create(AOwner);FShowProgress := True;FShowOpenExcel:= True;end;procedure TDBGridEhToExcel.SetShowProgress(const Value: Boolean);beginFShowProgress := Value;end;procedure TDBGridEhT oExcel.SetDBGridEh(const Value: TDBGridEh);beginFDBGridEh := Value;end;procedure TDBGridEhT oExcel.SetTitleName(const Value: TCaption);beginFTitleName := Value;end;procedure TDBGridEhT oExcel.SetUserName(const Value: TCaption);beginFUserName := Value;end;function IsFileInUse(fName: string ): boolean;varHFileRes: HFILE;beginResult :=false;if not FileExists(fName) then exit;HFileRes :=CreateFile(pchar(fName), GENERIC_READor GENERIC_WRITE,0, nil,OPEN_EXISTING,FILE_ATTRIBUTE_NORMAL, 0);Result :=(HFileRes=INVALID_HANDLE_VALUE);if not Result thenCloseHandle(HFileRes);end;procedure TDBGridEhToExcel.ExportToExcel;XLApp: Variant;Sheet: Variant;s1, s2: string;Caption,Msg: String;Row, Col: integer;iCount, jCount: Integer;FBookMark: TBookmark;FileName: String;SaveDialog1: TSaveDialog;begin//如果数据集为空或没有打开则退出if not DBGridEh.DataSource.DataSet.Active then Exit;SaveDialog1 := TSaveDialog.Create(Nil);SaveDialog1.FileName := TitleName + '_' + FormatDateTime('YYMMDDHHmmSS', now);SaveDialog1.Filter := 'Excel文件|*.xls';if SaveDialog1.Execute thenFileName := SaveDialog1.FileName;SaveDialog1.Free;if FileName = '' then Exit;while IsFileInUse(FileName) dobeginif Application.MessageBox('目标文件使用中，请退出目标文件后点击确定继续！','注意', MB_OKCANCEL + MB_ICONWARNING) = IDOK then beginelsebeginExit;end;end;if FileExists(FileName) thenbeginMsg := '已存在文件（' + FileName + '），是否覆盖？';if Application.MessageBox(PChar(Msg), '提示', MB_YESNO + MB_ICONQUESTION + MB_DEFBUTTON2) = IDYES then begin//删除文件DeleteFile(PChar(FileName))endelseexit;end;Application.ProcessMessages;Screen.Cursor := crHourGlass;//显示进度窗体if ShowProgress thenCreateProcessForm(nil);if not VarIsEmpty(XLApp) thenbeginXLApp.DisplayAlerts := False;XLApp.Quit;VarClear(XLApp);end;//通过ole创建Excel对象tryXLApp := CreateOleObject('Excel.Application');exceptMessageDlg('创建Excel对象失败，请检查你的系统是否正确安装了Excel软件！', mtError, [mbOk], 0);Screen.Cursor := crDefault;Exit;end;//生成工作页XLApp.WorkBooks.Add[XLWBatWorksheet];XLApp.WorkBooks[1].WorkSheets[1].Name := TitleName;Sheet := XLApp.Workbooks[1].WorkSheets[TitleName];//写标题sheet.cells[1, 1] := TitleName;sheet.range[sheet.cells[1, 1], sheet.cells[1, DBGridEh.Columns.Count]].Select; //选择该列XLApp.selection.HorizontalAlignment := $FFFFEFF4; //居中XLApp.selection.MergeCells := True; //合并//写表头Row := 1;jCount := 3;for iCount := 0 to DBGridEh.Columns.Count - 1 dobeginCol := 2;Row := iCount+1;Caption := DBGridEh.Columns[iCount].Title.Caption;while POS('|', Caption) > 0 dobeginjCount := 4;s1 := Copy(Caption, 1, Pos('|',Caption)-1);if s2 = s1 thenbeginsheet.range[sheet.cells[Col, Row-1],sheet.cells[Col, Row]].Select;XLApp.selection.HorizontalAlignment := $FFFFEFF4;XLApp.selection.MergeCells := True;endelseSheet.cells[Col,Row] := Copy(Caption, 1, Pos('|',Caption)-1);Caption := Copy(Caption,Pos('|', Caption)+1, Length(Caption));Inc(Col);s2 := s1;end;Sheet.cells[Col, Row] := Caption;Inc(Row);end;//合并表头并居中if jCount = 4 thenfor iCount := 1 to DBGridEh.Columns.Count doif Sheet.cells[3, iCount].Value = '' thenbeginsheet.range[sheet.cells[2, iCount],sheet.cells[3, iCount]].Select;XLApp.selection.HorizontalAlignment := $FFFFEFF4;XLApp.selection.MergeCells := True;endelse beginsheet.cells[3, iCount].Select;XLApp.selection.HorizontalAlignment := $FFFFEFF4;end;//读取数据DBGridEh.DataSource.DataSet.DisableControls;FBookMark := DBGridEh.DataSource.DataSet.GetBookmark;DBGridEh.DataSource.DataSet.First;while not DBGridEh.DataSource.DataSet.Eof dobeginfor iCount := 1 to DBGridEh.Columns.Count dobegin//Sheet.cells[jCount,iCount] :=DBGridEh.Columns.Items[iCount-1].Field.AsString;caseDBGridEh.DataSource.DataSet.FieldByName(DBGridEh.Columns. Items[iCount-1].FieldName).DataType offtSmallint, ftInteger, ftWord, ftAutoInc, ftBytes:Sheet.cells[jCount,iCount] :=DBGridEh.Columns.Items[iCount-1].Field.asinteger;ftFloat, ftCurrency, ftBCD:Sheet.cells[jCount,iCount] :=DBGridEh.Columns.Items[iCount-1].Field.AsFloat;elseifDBGridEh.DataSource.DataSet.FieldByName(DBGridEh.Columns. Items[iCount-1].FieldName) is TBlobfield then // 此类型的字段(图像等)暂无法读取显示Sheet.cells[jCount,iCount] :=DBGridEh.Columns.Items[iCount-1].Field.AsString elseSheet.cells[jCount,iCount] :=''''+DBGridEh.Columns.Items[iCount-1].Field.AsString;end;end;Inc(jCount);//显示进度条进度过程if ShowProgress thenbeginFtempGauge.Position := DBGridEh.DataSource.DataSet.RecNo;FtempGauge.Refresh;end;DBGridEh.DataSource.DataSet.Next;end;if DBGridEh.DataSource.DataSet.BookmarkValid(FBookMark)thenDBGridEh.DataSource.DataSet.GotoBookmark(FBookMark);DBGridEh.DataSource.DataSet.EnableControls;//读取表脚if DBGridEh.FooterRowCount > 0 thenbeginfor Row := 0 to DBGridEh.FooterRowCount-1 dobeginfor Col := 0 to DBGridEh.Columns.Count-1 doSheet.cells[jCount, Col+1] := DBGridEh.GetFooterValue(Row,DBGridEh.Columns[Col]);Inc(jCount);end;end;//调整列宽// for iCount := 1 to DBGridEh.Columns.Count do// Sheet.Columns[iCount].EntireColumn.AutoFit;sheet.cells[1, 1].Select;XlApp.Workbooks[1].SaveAs(FileName);XlApp.Visible := True;XlApp := Unassigned;if ShowProgress thenFreeAndNil(FProgressForm);Screen.Cursor := crDefault;end;destructor TDBGridEhToExcel.Destroy;begininherited Destroy;end;procedure TDBGridEhT oExcel.CreateProcessForm(AOwner: TComponent);varPanel: TPanel;beginif Assigned(FProgressForm) thenexit;FProgressForm := TForm.Create(AOwner);with FProgressForm dobegintry := '宋体'; {设置字体}Font.Size := 10;BorderStyle := bsNone;Width := 300;Height := 30;BorderWidth := 1;Color := clBlack;Position := poScreenCenter;Panel := TPanel.Create(FProgressForm);with Panel dobeginParent := FProgressForm;Align := alClient;Caption := '正在导出Excel，请稍候......';Color:=$00E9E5E0;end;FtempGauge:=TProgressBar.Create(Panel);with FtempGauge dobeginParent := Panel;Align:=alClient;Min := 0;Max:= DBGridEh.DataSource.DataSet.RecordCount;Position := 0;end;exceptend;end;FProgressForm.Show;FProgressForm.Update;end;procedure TDBGridEhT oExcel.SetShowOpenExcel(const Value: Boolean);beginFShowOpenExcel:=Value;end;end.用法：先新建一个单元文件比如Unit_DBGridEhToExcel.pas，然后再在工程里面引用这个文件，再直接通过一个按钮事件调用就可以了。

DBGridEh导出txt xls等功能详解

{$R *.dfm}uses DBGridEhImpExp;//SaveDBGridEhToExportFile可以支持以下五种类型//TDBGridEhExportAsText//TDBGridEhExportAsCSV//TDBGridEhExportAsHTML//TDBGridEhExportAsRTF//TDBGridEhExportAsXLSprocedure TForm1.Button1Click(Sender: TObject);begin//导出//SaveDBGridEhToExportFile(TDBGridEhExportAsXls,DBGridEh1,'f:\test.xls',True); //True:导出整个表;False:导出选中的局部数据区域//SaveDBGridEhToExportFile(TDBGridEhExportAsText,DBGridEh1,'f:\test.txt',True);SaveDBGridEhToExportFile(TDBGridEhExportAsHtml,DBGridEh1,'f:\test.htm',True);end;procedure TForm1.Button3Click(Sender: TObject);beginOpenDialog1.Filter:='Text(*.txt)|*.txt';if Opendialog1.Execute thenbeginADOQuery1.SQL.Text:='Delete from dzb';ADOQuery1.ExecSQL;ADOQuery1.SQL.Text:='Select 自编号,合同号,币种,金额,银行from dzb';ADOQuery1.Open;//当前表的列数和字段类型一定要跟导入表的列数和字段类型相对应好,否则会出错LoadDBGridEhFromImportFile(TDBGridEhImportAsText,DBGridEh1,Opendialog1.FileName,False);end;end;procedure TForm1.FormCreate(Sender: TObject);beginDBGridEh1.DataSource:=DataSource1;DataSource1.DataSet:=ADOQuery1;ADOQuery1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;'+'Data Source=f:\mcmygs.mdb;'+'Persist Security Info=False;'+'Jet OLEDB:Database Password=123456';ADOQuery1.SQL.Text:='Select 自编号,合同号,币种,金额,银行from dzb';ADOQuery1.Open;end;导出的三种格式：导入文本文件的时候需要对文本文件进行一些处理：DBGridEh导出功能详解(SaveDBGridEhToExportFile说明)作者：sunjun0427 来源：博客园发布时间：2010-12-16 13:38 阅读：77 次原文链接[收藏]DBGridEh导出功能详解(SaveDBGridEhToExportFile说明)---经常有用户要求数据导出，对此，我们一直用最原始的办法，打开查询，读取每个字段的数据，逐条的的写入文件。

Delphi表格控件DBGridEh应用实例6-输入与输出

Delphi表格控件DBGridEh应用实例6-输入与输出6. 输入/输出1) 导入导出数据导入/导出数据在实际处理过程中是比较烦琐的。

但是Enlib3.0 提供了一系列函数让你轻松实现此功能，而且支持的文件格式很多：Text, Csv, HTML, RTF, XLS 和内部数据格式。

除此之外，还可对任意选择的数据区域进行操作。

函数如下：Pascal:SaveDBGridEhToExportFile(TDBGridEhExportAsText,DBGridE h1,'c:\temp\file1.txt',F alse);C++:SaveDBGridEhToExportFile(__classid(TDBGridEhExportAsText ),DBGridEh1,"c:\\temp\\file1.txt",false);说明：其中false参数表示导出的是选中的局部数据区域数据，true则为整个表格数据。

例：将当前表格中数据导出为EXCEL等格式文件。

在窗体中添加一个SaveDialog组件和“导出”按钮B_exp，在“导出”按钮的click 事件中添加如下代码：procedure TForm1.B_expClick(Sender: TObject);varExpClass:TDBGridEhExportClass;Ext:String;BeginSaveDialog1.FileName := 'file1';if (ActiveControl is TDBGridEh) thenif SaveDialog1.Execute thenbegincase SaveDialog1.FilterIndex of1: begin ExpClass := TDBGridEhExportAsText; Ext := 'txt'; end;2: begin ExpClass := TDBGridEhExportAsCSV; Ext := 'csv'; end;3: begin ExpClass := TDBGridEhExportAsHTML; Ext := 'htm'; end;4: begin ExpClass := TDBGridEhExportAsRTF; Ext := 'rtf'; end;5: begin ExpClass := TDBGridEhExportAsXLS; Ext := 'xls'; end;elseExpClass := nil; Ext := '';end;if ExpClass nil thenbeginifUpperCase(Copy(SaveDialog1.FileName,Length(SaveDialog1.File Name)-2,3))UpperCase(Ext) thenSaveDialog1.FileName := SaveDialog1.FileName + '.' + Ext;SaveDBGridEhToExportFile(ExpClass,DBGridEh1,SaveDialog1 .FileName,False);//其中false为局部数据end;end;end;2) 从多种格式导入/导出数据到TDBGridEhEhLib 的函数集可以从DBGridEh 导出数据到Text, Csv, HTML, RTF, XLS 以及其内部格式。

DBGridEh基本操作

DBGridEh基本操作•导出到excel等文件类型es DBGridEhImpExp2.//导出到文本文件3.TDBGridEhExportAsText4.//导出到Unicode文本5.TDBGridEhExportAsUnicodeText6.//导出到CSV7.TDBGridEhExportAsCSV8.//导出到HTML9.TDBGridEhExportAsHTML10.//导出到RTF11.TDBGridEhExportAsRTF12.//导出到XLS13.TDBGridEhExportAsXLS14.//导出到XLSX15.TDBGridEhExportAsXlsx1.//示例2.procedure TForm1.Button1Click(Sender: TObject);3.var4.Path:string;5.begin6.GetDir(0,Path);7.ExportDBGridEhT oXlsx(DBGridEh,Path+'\File1.xlsx',[]);8.ShowMessage('Export is finished');9.end;•多表头1.需要打开多表头支持TitleParams->MultiTitle选择TRUE2.给表头赋值时输入格式为:3.DBGridEh.Columns[].FieldName:='Name|FirstName';4.DBGridEh.Columns[].FieldName:='Name|LastName';5.相同合并的表头需要用竖线'|'分割•页脚合计等功能1.FooterRowCount需要输入页脚汇总的行数,比如12.单击要汇总列的Footers属性,在结构视图点击右键选择Add Item出现此列页脚的属性,在ValueType选择需要进行的计算,常用的有计数,合计,最大值,最小值,3.其中fvtFieldValue是根据数据集中字段进行计算,在FieldName属性中,可以选择要汇总合计的字段4.,在DisplayFormat中可以输入要显示值得格式比如0.,5.ValueType是fvtStaticText中是显示静态文本6.需要将dbgrideh的属性中的sumlist中的Active 设置为 True 才能根据字段进行计算•复选框列1.选中要显示复选框的列,设置CheckBoxes为True2.在FieldName中选中数据集中要显示的列,数据集中这个字段必须设置为Bit类型•显示表中表1.主表连接方式：DBGridEh1->DataSource1->MemTableEh1->DataSetDriverEh1->ADOQuery1->ADOConnection12.ADOQuery1.sql.text:='select * from WareOut'3.DBGridEH1.RowDetailPanel.Active:=True;打开显示子表的Panel4.把DBGridEH2放入DBGridEH1打开的Panel中，并设置DBGridEH2的Algin为alClient5.子表连接方式：DBGridEh2->DataSource2->MemTableEh2->DataSetDriverEh2->ADOQuery2->ADOConnection16.ADOQuery2.sql.text:='select * from WareOutDetail'7.设置MemTableEh2的MasterFields和DetailFields•获取单元格值1.//获取当前点击单元格值2.procedure TForm1.DBGridEh1CellClick(Column: TColumnEh);3.begin4.ShowMessage(Column.DisplayText);5.end;6.7.//获取当前选择行某列单元格值8.procedure TForm1.DBGridEh1CellClick(Column: TColumnEh);9.begin10.ShowMessage(DBGridEh1.Columns[].DisplayText);11.end;•表格指示器显示数字行号1.DBGridEh1.IndicatorOptions := [gioShowRowIndicatorEh, gioShowRecNoEh];2.DBGridEh1.IndicatorParams.RecNoShowStep := ; //定义多少行显示行号,默认为●鼠标点击后显示当前行号•隔行变色1.EvenRowColor 偶数行颜色2.OddRowColor 奇数行颜色•冻结列1.FrozenCols 冻结列,赋值2即冻结左边两列•纵向相同单元格值不重复显示•。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

问题：由DBGridEh导出EXCEL改写的! 很简陋, 大家有兴趣的可以再改写一下! 希望做地更漂亮( 积分：300, 回复：0, 阅读：2 ) 分类：控件- 开发( 版主：cAkk, amo )来自：kk2000, 时间：2008-11-23 21:37:00, ID：3931729 [显示：小字体| 大字体] //好久没有写东西了今天发情. 就乱写呵呵! 别见笑啊^^unit DataSetToExcel;interfaceusesWindows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,Dialogs, DB, ComCtrls, ExtCtrls, StdCtrls, Gauges, ShellApi;typeTDataSetToExcel = class(TComponent)privateFCol: integer; {行}FRow: integer; {列}Stream: TStream; {输出文件流}FBookMark: TBookmark;FDataSet: TDataSet;FBeginDate: TCaption; {开始日期}FTitleName: TCaption; {Excel文件标题}FEndDate: TCaption; {结束日期}FUserName: TCaption; {制表人}FFileName: String; {保存文件名}procedure SetDataSet(const Value: TDataSet);procedure SetBeginDate(const Value: TCaption);procedure SetEndDate(const Value: TCaption);procedure SetTitleName(const Value: TCaption);procedure SetUserName(const Value: TCaption);procedure SetFileName(const Value: String);procedure IncColRow;procedure WriteBlankCell; {写空单元格}{写数字单元格}procedure WriteFloatCell(const AValue: Double; const IncStatus: Boolean=True); {写整型单元格}procedure WriteIntegerCell(const AValue: Integer; const IncStatus: Boolean=True); {写字符单元格}procedure WriteStringCell(const AValue: string; const IncStatus: Boolean=True); procedure WritePrefix;procedure WriteSuffix;procedure WriteHeader; {输出Excel标题} procedure WriteTitle; {输出Excel列标题} procedure WriteDataCell; {输出数据集内容} procedure WriteFooter; {输出Excel表脚} procedure SaveStream(aStream: TStream);protected{ Protected declarations }publicconstructor Create(AOwner: TComponent); override;destructor Destroy; override;procedure ExportToExcel; {输出Excel文件}publishedproperty DataSet: TDataSet read FDataSet write SetDataSet;property TitleName: TCaption read FTitleName write SetTitleName;property BeginDate: TCaption read FBeginDate write SetBeginDate;property EndDate: TCaption read FEndDate write SetEndDate;property UserName: TCaption read FUserName write SetUserName;property FileName: String read FFileName write SetFileName;end;CXlsBof: array[0..5] of Word = ($809, 8, 0, $10, 0, 0); CXlsEof: array[0..1] of Word = ($0A, 00);CXlsLabel: array[0..5] of Word = ($204, 0, 0, 0, 0, 0); CXlsNumber: array[0..4] of Word = ($203, 14, 0, 0, 0); CXlsRk: array[0..4] of Word = ($27E, 10, 0, 0, 0); CXlsBlank: array[0..4] of Word = ($201, 6, 0, 0, $17);procedure Register;implementationprocedure Register;beginRegisterComponents('ExportToExcel', [TDataSetToExcel]); end;{ TDataSetToExcel }constructor TDataSetToExcel.Create(AOwner: TComponent); begininherited Create(AOwner);end;destructor TDataSetToExcel.Destroy;begininherited;end;FileStream: TFileStream;Msg: String;begin//如果数据集为空或没有打开则退出if (DataSet.IsEmpty) or (not DataSet.Active) then exit;//如果保存的文件名为空则退出if Trim(FileName) = '' then exit;Screen.Cursor := crHourGlass;trytryif FileExists(FileName) thenbeginMsg := '已存在文件（' + FileName + '），是否覆盖？';if Application.MessageBox(PChar(Msg),'提示',MB_YESNO+MB_ICONQUESTION+MB_DEFBUTTON2) = IDYES then begin//删除文件DeleteFile(FileName)end elseexit;end;//显示进度窗体FileStream := TFileStream.Create(FileName, fmCreate);try//输出文件SaveStream(FileStream);finallyFileStream.Free;end;//打开Excel文件exceptend;finallyScreen.Cursor := crDefault;end;end;procedure TDataSetToExcel.IncColRow;beginif FCol = DataSet.FieldCount - 1 thenbeginInc(FRow);FCol := 0;end elseInc(FCol);end;procedure TDataSetToExcel.SaveStream(aStream: TStream); beginFCol := 0;FRow := 0;Stream := aStream;//输出前缀WritePrefix;//输出表格标题WriteHeader;//输出列标题WriteTitle;WriteDataCell;//输出表脚WriteFooter;//输出后缀WriteSuffix;end;procedure TDataSetToExcel.SetBeginDate(const Value: TCaption); beginFBeginDate := Value;end;procedure TDataSetToExcel.SetDataSet(const Value: TDataSet); beginFDataSet := Value;end;procedure TDataSetToExcel.SetEndDate(const Value: TCaption); beginFEndDate := Value;end;procedure TDataSetToExcel.SetFileName(const Value: String); beginFFileName := Value;end;procedure TDataSetToExcel.SetTitleName(const Value: TCaption); beginend;procedure TDataSetToExcel.SetUserName(const Value: TCaption); beginFUserName := Value;end;procedure TDataSetToExcel.WriteBlankCell;beginCXlsBlank[2] := FRow;CXlsBlank[3] := FCol;Stream.WriteBuffer(CXlsBlank, SizeOf(CXlsBlank)); IncColRow;end;procedure TDataSetToExcel.WriteDataCell;vari: integer;beginFBookMark := DataSet.GetBookmark;tryDataSet.First;while not DataSet.Eof dobeginfor i := 0 to DataSet.FieldCount - 1 dobeginif DataSet.Fields[i].IsNull or (not DataSet.Fields[i].Visible) thenWriteBlankCellelsebegincase DataSet.Fields[i].DataType ofWriteIntegerCell(DataSet.Fields[i].AsInteger);ftFloat, ftCurrency, ftBCD:WriteFloatCell(DataSet.Fields[i].AsFloat);elseif DataSet.Fields[i] Is TBlobfield then // 此类型的字段(图像等)暂无法读取显示WriteStringCell('')elseWriteStringCell(DataSet.Fields[i].AsString);end;end;end;//显示进度条进度过程DataSet.Next;end;finallyif DataSet.BookmarkValid(FBookMark) thenDataSet.GotoBookmark(FBookMark);end;end;procedure TDataSetToExcel.WriteFloatCell(const AValue: Double;const IncStatus: Boolean);beginCXlsNumber[2] := FRow;CXlsNumber[3] := FCol;Stream.WriteBuffer(CXlsNumber, SizeOf(CXlsNumber));Stream.WriteBuffer(AValue, 8);if IncStatus then IncColRow;end;procedure TDataSetToExcel.WriteFooter;end;procedure TDataSetToExcel.WriteHeader;varOpName, OpDate: String;begin//标题FCol := 3;WriteStringCell(TitleName,False);FCol := 0;Inc(FRow);{if Trim(BeginDate) <> '' thenbegin//开始日期FCol := 0;WriteStringCell(BeginDate,False);FCol := 0end;if Trim(EndDate) <> '' thenbegin//结束日期FCol := 5;WriteStringCell(EndDate,False);FCol := 0;end;if (Trim(BeginDate) <> '') or (Trim(EndDate) <> '') then//制表人OpName := '制表人：' + UserName;FCol := 0;WriteStringCell(OpName,False);FCol := 0;//制表时间OpDate := '制表时间：' + DateTimeToStr(Now);FCol := 5;WriteStringCell(OpDate,False);FCol := 0;Inc(FRow); }end;procedure TDataSetToExcel.WriteIntegerCell(const AValue: Integer; const IncStatus: Boolean);varV: Integer;beginCXlsRk[2] := FRow;CXlsRk[3] := FCol;Stream.WriteBuffer(CXlsRk, SizeOf(CXlsRk));V := (AValue Shl 2) Or 2;Stream.WriteBuffer(V, 4);if IncStatus then IncColRow;end;procedure TDataSetToExcel.WriteStringCell(const AValue: string; const IncStatus: Boolean);varbeginL := Length(AValue);CXlsLabel[1] := 8 + L;CXlsLabel[2] := FRow;CXlsLabel[3] := FCol;CXlsLabel[5] := L;Stream.WriteBuffer(CXlsLabel, SizeOf(CXlsLabel)); Stream.WriteBuffer(Pointer(AValue)^, L);if IncStatus then IncColRow;end;procedure TDataSetToExcel.WritePrefix;beginStream.WriteBuffer(CXlsBof, SizeOf(CXlsBof)); end;procedure TDataSetToExcel.WriteSuffix;beginStream.WriteBuffer(CXlsEof, SizeOf(CXlsEof)); end;procedure TDataSetToExcel.WriteTitle;varstrList: TStrings;j: integer;begintrystrList := TStringList.Create;DataSet.GetFieldNames(strList);for j := 0 to strList.Count - 1 dobeginFCol := j;。