- android - RelativeLayout 背景可绘制重叠内容
- android - 如何链接 cpufeatures lib 以获取 native android 库?
- java - OnItemClickListener 不起作用,但 OnLongItemClickListener 在自定义 ListView 中起作用
- java - Android 文件转字符串
我尝试在我的 C# 4.0 Windows 应用程序中将 datagridview 数据导出到 excel 文件。
我们使用了 Microsoft.Office.Interop.Excel dll 版本 12.0.0.0。一切都很好
很好。但是当我尝试导出超过 1000 条 datagridview 记录时,它花费的时间太长了
时间。我怎样才能提高性能。
请参阅下面的 Excel 帮助程序代码。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
using System.Runtime.InteropServices; // For COMException
using System.Reflection; // For Missing.Value and BindingFlags
using System.Diagnostics; // to ensure EXCEL process is really killed
namespace Export.Excel
{
#region InstanceFields
//Instance Fields
//public delegate void ProgressHandler(object sender, ProgressEventArgs e);
//public event ProgressHandler prg;
private System.Data.DataView dv;
private Style styleRows;
private Style styleColumnHeadings;
private Microsoft.Office.Interop.Excel.Application EXL;
private Workbook workbook;
private Sheets sheets;
private Worksheet worksheet;
private string[,] myTemplateValues;
private int position;
private System.Globalization.CultureInfo cl;
private Type _ResourceType;
#endregion
#region Constructor
//Constructs a new export2Excel object. The user must
//call the createExcelDocument method once a valid export2Excel
//object has been instantiated
public ExportExcelFormat(string culture, Type type)
{
cl = new System.Globalization.CultureInfo(culture);
_ResourceType = type;
}
#endregion
#region EXCEL : ExportToExcel
//Exports a DataView to Excel. The following steps are carried out
//in order to export the DataView to Excel
//Create Excel Objects
//Create Column & Row Workbook Cell Rendering Styles
//Fill Worksheet With DataView
//Add Auto Shapes To Excel Worksheet
//Select All Used Cells
//Create Headers/Footers
//Set Status Finished
//Save workbook & Tidy up all objects
//@param dv : DataView to use
//@param path : The path to save/open the EXCEL file to/from
//@param sheetName : The target sheet within the EXCEL file
public void ExportToExcel(System.Data.DataView dv, string path, string sheetName, string[] UnWantedColumns)
{
try
{
//Assign Instance Fields
this.dv = dv;
#region NEW EXCEL DOCUMENT : Create Excel Objects
//create new EXCEL application
EXL = new Microsoft.Office.Interop.Excel.ApplicationClass();
//index to hold location of the requested sheetName in the workbook sheets
//collection
int indexOfsheetName;
#region FILE EXISTS
//Does the file exist for the given path
if (File.Exists(path))
{
//Yes file exists, so open the file
workbook = EXL.Workbooks.Open(path,
0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
//get the workbook sheets collection
sheets = workbook.Sheets;
//set the location of the requested sheetName to -1, need to find where
//it is. It may not actually exist
indexOfsheetName = -1;
//loop through the sheets collection
for (int i = 1; i <= sheets.Count; i++)
{
//get the current worksheet at index (i)
worksheet = (Worksheet)sheets.get_Item(i);
//is the current worksheet the sheetName that was requested
if (worksheet.Name.ToString().Equals(sheetName))
{
//yes it is, so store its index
indexOfsheetName = i;
//Select all cells, and clear the contents
Microsoft.Office.Interop.Excel.Range myAllRange = worksheet.Cells;
myAllRange.Select();
myAllRange.CurrentRegion.Select();
myAllRange.ClearContents();
}
}
//At this point it is known that the sheetName that was requested
//does not exist within the found file, so create a new sheet within the
//sheets collection
if (indexOfsheetName == -1)
{
//Create a new sheet for the requested sheet
Worksheet sh = (Worksheet)workbook.Sheets.Add(
Type.Missing, (Worksheet)sheets.get_Item(sheets.Count),
Type.Missing, Type.Missing);
//Change its name to that requested
sh.Name = sheetName;
}
}
#endregion
#region FILE DOESNT EXIST
//No the file DOES NOT exist, so create a new file
else
{
//Add a new workbook to the file
workbook = EXL.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//get the workbook sheets collection
sheets = workbook.Sheets;
//get the new sheet
worksheet = (Worksheet)sheets.get_Item(1);
//Change its name to that requested
worksheet.Name = sheetName;
}
#endregion
#region get correct worksheet index for requested sheetName
//get the workbook sheets collection
sheets = workbook.Sheets;
//set the location of the requested sheetName to -1, need to find where
//it is. It will definately exist now as it has just been added
indexOfsheetName = -1;
//loop through the sheets collection
for (int i = 1; i <= sheets.Count; i++)
{
//get the current worksheet at index (i)
worksheet = (Worksheet)sheets.get_Item(i);
//is the current worksheet the sheetName that was requested
if (worksheet.Name.ToString().Equals(sheetName))
{
//yes it is, so store its index
indexOfsheetName = i;
}
}
//set the worksheet that the DataView should write to, to the known index of the
//requested sheet
worksheet = (Worksheet)sheets.get_Item(indexOfsheetName);
#endregion
#endregion
// Set styles 1st
SetUpStyles();
//Fill EXCEL worksheet with DataView values
fillWorksheet_WithDataView(UnWantedColumns);
//Add the autoshapes to EXCEL
//AddAutoShapesToExcel();
//Select all used cells within current worksheet
SelectAllUsedCells();
try
{
workbook.Close(true, path, Type.Missing);
EXL.UserControl = false;
EXL.Quit();
EXL = null;
//kill the EXCEL process as a safety measure
killExcel();
}
catch (COMException cex)
{
}
catch (Exception ex)
{
}
}
catch (Exception ex)
{
}
}
#endregion
#region EXCEL : UseTemplate
//Exports a DataView to Excel. The following steps are carried out
//in order to export the DataView to Excel
//Create Excel Objects And Open Template File
//Select All Used Cells
//Create Headers/Footers
//Set Status Finished
//Save workbook & Tidy up all objects
//@param path : The path to save/open the EXCEL file to/from
public void UseTemplate(string path, string templatePath, string[,] myTemplateValues)
{
try
{
this.myTemplateValues = myTemplateValues;
//create new EXCEL application
EXL = new Microsoft.Office.Interop.Excel.ApplicationClass();
//Yes file exists, so open the file
workbook = EXL.Workbooks.Open(templatePath,
0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
//get the workbook sheets collection
sheets = workbook.Sheets;
//get the new sheet
worksheet = (Worksheet)sheets.get_Item(1);
//Change its name to that requested
worksheet.Name = "ATemplate";
//Fills the Excel Template File Selected With A 2D Test Array
fillTemplate_WithTestValues();
//Select all used cells within current worksheet
SelectAllUsedCells();
try
{
workbook.Close(true, path, Type.Missing);
EXL.UserControl = false;
EXL.Quit();
EXL = null;
//kill the EXCEL process as a safety measure
killExcel();
}
catch (COMException)
{
}
}
catch (Exception ex)
{
}
}
#endregion
#region STEP 1 : Create Column & Row Workbook Cell Rendering Styles
//Creates 2 Custom styles for the workbook These styles are
// styleColumnHeadings
// styleRows
//These 2 styles are used when filling the individual Excel cells with the
//DataView values. If the current cell relates to a DataView column heading
//then the style styleColumnHeadings will be used to render the current cell.
//If the current cell relates to a DataView row then the style styleRows will
//be used to render the current cell.
private void SetUpStyles()
{
// Style styleColumnHeadings
try
{
styleColumnHeadings = workbook.Styles["styleColumnHeadings"];
}
// Style doesn't exist yet.
catch
{
styleColumnHeadings = workbook.Styles.Add("styleColumnHeadings", Type.Missing);
styleColumnHeadings.Font.Name = "Arial";
styleColumnHeadings.Font.Size = 12;
styleColumnHeadings.Font.Bold = true;
}
// Style styleRows
try
{
styleRows = workbook.Styles["styleRows"];
}
// Style doesn't exist yet.
catch
{
styleRows = workbook.Styles.Add("styleRows", Type.Missing);
styleRows.Font.Name = "Verdana";
styleRows.Font.Size = 9;
}
}
#endregion
#region STEP 2 : Fill Worksheet With DataView
//Fills an Excel worksheet with the values contained in the DataView
//parameter
private void fillWorksheet_WithDataView(string[] UnWantedColumns)
{
position = 0;
//Add DataView Columns To Worksheet
int row = 1;
int col = 1;
// Remove unwanted columns in the loop
int total = dv.Table.Columns.Count - UnWantedColumns.Count();
// Loop thought the columns
for (int i = 0; i < total; i++)
{
fillExcelCell(worksheet, row, col++, dv.Table.Columns[i].ToString(), styleColumnHeadings.Name, UnWantedColumns);
}
//Add DataView Rows To Worksheet
row = 2;
col = 1;
for (int i = 0; i < dv.Table.Rows.Count; i++)
{
for (int j = 0; j < dv.Table.Columns.Count; j++)
{
fillExcelCell(worksheet, row, col++, dv[i][j].ToString(), styleRows.Name, UnWantedColumns);
}
col = 1;
row++;
position = (100 / dv.Table.Rows.Count) * row + 2;
}
}
#endregion
#region STEP 3 : Fill Individual Cell and Render Using Predefined Style
//Formats the current cell based on the Style setting parameter name
//provided here
//@param worksheet : The worksheet
//@param row : Current row
//@param col : Current Column
//@param Value : The value for the cell
//@param StyleName : The style name to use
private void fillExcelCell(Worksheet worksheet, int row, int col, Object Value, string StyleName, string[] UnWantedColumns)
{
if (!UnWantedColumns.Contains(Value.ToString()))
{
Range rng = (Range)worksheet.Cells[row, col];
rng.NumberFormat = "@";
rng.Select();
rng.Value2 = Value.ToString();
rng.Style = StyleName;
rng.Columns.EntireColumn.AutoFit();
}
}
#endregion
#region STEP 4 : Add Auto Shapes To Excel Worksheet
//Add some WordArt objecs to the Excel worksheet
private void AddAutoShapesToExcel()
{
//Method fields
float txtSize = 80;
float Left = 100.0F;
float Top = 100.0F;
//Have 2 objects
int[] numShapes = new int[2];
Microsoft.Office.Interop.Excel.Shape[] myShapes = new Microsoft.Office.Interop.Excel.Shape[numShapes.Length];
try
{
//loop through the object count
for (int i = 0; i < numShapes.Length; i++)
{
//Add the object to Excel
myShapes[i] = worksheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect1, "DRAFT", "Arial Black",
txtSize, MsoTriState.msoFalse, MsoTriState.msoFalse, (Left * (i * 3)), Top);
//Manipulate the object settings
myShapes[i].Rotation = 45F;
myShapes[i].Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse;
myShapes[i].Fill.Transparency = 0F;
myShapes[i].Line.Weight = 1.75F;
myShapes[i].Line.DashStyle = MsoLineDashStyle.msoLineSolid;
myShapes[i].Line.Transparency = 0F;
myShapes[i].Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
myShapes[i].Line.ForeColor.RGB = (0 << 16) | (0 << 8) | 0;
myShapes[i].Line.BackColor.RGB = (255 << 16) | (255 << 8) | 255;
}
}
catch (Exception ex)
{
}
}
#endregion
#region STEP 5 : Select All Used Cells
//Selects all used cells for the Excel worksheet
private void SelectAllUsedCells()
{
Microsoft.Office.Interop.Excel.Range myAllRange = worksheet.Cells;
myAllRange.Select();
myAllRange.CurrentRegion.Select();
}
#endregion
#region STEP 6 : Fill Template With Test Values
//Fills the Excel Template File Selected With A 2D Test Array parameter
private void fillTemplate_WithTestValues()
{
//Initilaise the correct Start Row/Column to match the Template
int StartRow = 3;
int StartCol = 2;
position = 0;
// Display the array elements within the Output window, make sure its correct before
for (int i = 0; i <= myTemplateValues.GetUpperBound(0); i++)
{
//loop through array and put into EXCEL template
for (int j = 0; j <= myTemplateValues.GetUpperBound(1); j++)
{
//update position in progress bar
position = (100 / myTemplateValues.Length) * i;
//put into EXCEL template
Range rng = (Range)worksheet.Cells[StartRow, StartCol++];
rng.Select();
rng.Value2 = myTemplateValues[i, j].ToString();
rng.Rows.EntireRow.AutoFit();
}
//New row, so column needs to be reset
StartCol = 2;
StartRow++;
}
}
#endregion
#region Kill EXCEL
//As a safety check go through all processes and make
//doubly sure excel is shutdown. Working with COM
//have sometimes noticed that the EXL.Quit() call
//does always do the job
private void killExcel()
{
try
{
Process[] ps = Process.GetProcesses();
foreach (Process p in ps)
{
if (p.ProcessName.ToLower().Equals("excel"))
{
p.Kill();
}
}
}
catch (Exception ex)
{
}
}
#endregion
}
最佳答案
我有一些改进性能的建议。单独使用它们可能不会产生太大影响,但它们一起应该可以提高整体性能。
EXL.Visible = false;
。关掉计算
(Application.Calculation = xlCalculationManual
,如果它不需要)和 ScreenUpdating
。Excel.Workbooks.Worksheets
而不是 Sheets
集合。与其遍历所有工作表,不如尝试引用所需的工作表,使用错误处理来确定工作表是否存在:
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets["SheetName"];
避免Select
,它很少需要——而且很慢。替换,
//Select all cells, and clear the contents
Microsoft.Office.Interop.Excel.Range myAllRange = worksheet.Cells;
myAllRange.Select();
myAllRange.CurrentRegion.Select();
myAllRange.ClearContents();
与
worksheet.UsedRange.ClearContents();
您应该能够完全删除函数 SelectAllUsedCells()
。如果您仍然需要选择它们,则:
worksheet.UsedRange.Select(); // but shouldn't be necessary
否则,如果您坚持循环遍历工作表,请在找到工作表后使用 break;
退出循环。
从 fillExcelCell() 函数中删除 rng.Select();
。但是,您似乎在为每个单元格调用此函数;这个对吗?之后我会一次性完成所有格式设置。特别是,将 AutoFit
应用于整个范围。
我会创建一次形状并复制/粘贴它。 (不确定是否可以克隆?)
完成后将计算模式恢复到原来的设置。
关于c# - Microsoft Office 互操作性能问题,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17082635/
#include using namespace std; class C{ private: int value; public: C(){ value = 0;
这个问题已经有答案了: What is the difference between char a[] = ?string?; and char *p = ?string?;? (8 个回答) 已关闭
关闭。此题需要details or clarity 。目前不接受答案。 想要改进这个问题吗?通过 editing this post 添加详细信息并澄清问题. 已关闭 7 年前。 此帖子已于 8 个月
除了调试之外,是否有任何针对 c、c++ 或 c# 的测试工具,其工作原理类似于将独立函数复制粘贴到某个文本框,然后在其他文本框中输入参数? 最佳答案 也许您会考虑单元测试。我推荐你谷歌测试和谷歌模拟
我想在第二台显示器中移动一个窗口 (HWND)。问题是我尝试了很多方法,例如将分辨率加倍或输入负值,但它永远无法将窗口放在我的第二台显示器上。 关于如何在 C/C++/c# 中执行此操作的任何线索 最
我正在寻找 C/C++/C## 中不同类型 DES 的现有实现。我的运行平台是Windows XP/Vista/7。 我正在尝试编写一个 C# 程序,它将使用 DES 算法进行加密和解密。我需要一些实
很难说出这里要问什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或夸夸其谈,无法以目前的形式得到合理的回答。如需帮助澄清此问题以便重新打开,visit the help center . 关闭 1
有没有办法强制将另一个 窗口置于顶部? 不是应用程序的窗口,而是另一个已经在系统上运行的窗口。 (Windows, C/C++/C#) 最佳答案 SetWindowPos(that_window_ha
假设您可以在 C/C++ 或 Csharp 之间做出选择,并且您打算在 Windows 和 Linux 服务器上运行同一服务器的多个实例,那么构建套接字服务器应用程序的最明智选择是什么? 最佳答案 如
你们能告诉我它们之间的区别吗? 顺便问一下,有什么叫C++库或C库的吗? 最佳答案 C++ 标准库 和 C 标准库 是 C++ 和 C 标准定义的库,提供给 C++ 和 C 程序使用。那是那些词的共同
下面的测试代码,我将输出信息放在注释中。我使用的是 gcc 4.8.5 和 Centos 7.2。 #include #include class C { public:
很难说出这里问的是什么。这个问题是含糊的、模糊的、不完整的、过于宽泛的或修辞性的,无法以目前的形式得到合理的回答。如需帮助澄清此问题以便重新打开它,visit the help center 。 已关
我的客户将使用名为 annoucement 的结构/类与客户通信。我想我会用 C++ 编写服务器。会有很多不同的类继承annoucement。我的问题是通过网络将这些类发送给客户端 我想也许我应该使用
我在 C# 中有以下函数: public Matrix ConcatDescriptors(IList> descriptors) { int cols = descriptors[0].Co
我有一个项目要编写一个函数来对某些数据执行某些操作。我可以用 C/C++ 编写代码,但我不想与雇主共享该函数的代码。相反,我只想让他有权在他自己的代码中调用该函数。是否可以?我想到了这两种方法 - 在
我使用的是编写糟糕的第 3 方 (C/C++) Api。我从托管代码(C++/CLI)中使用它。有时会出现“访问冲突错误”。这使整个应用程序崩溃。我知道我无法处理这些错误[如果指针访问非法内存位置等,
关闭。这个问题不符合Stack Overflow guidelines .它目前不接受答案。 我们不允许提问寻求书籍、工具、软件库等的推荐。您可以编辑问题,以便用事实和引用来回答。 关闭 7 年前。
已关闭。此问题不符合Stack Overflow guidelines 。目前不接受答案。 要求我们推荐或查找工具、库或最喜欢的场外资源的问题对于 Stack Overflow 来说是偏离主题的,因为
我有一些 C 代码,将使用 P/Invoke 从 C# 调用。我正在尝试为这个 C 函数定义一个 C# 等效项。 SomeData* DoSomething(); struct SomeData {
这个问题已经有答案了: Why are these constructs using pre and post-increment undefined behavior? (14 个回答) 已关闭 6
我是一名优秀的程序员,十分优秀!