Excel对象的问题 [英] Problem with Excel object
问题描述
大家好,
我正在使用Excel对象将csv文件转换为excel格式化
当Excel对象打开时如果我打开任何excel文件应用程序
它会抛出错误。
也许在Excel外面试图打开,打开同样的工作簿我是什么意思
创建。
打开Excel和工作簿以打开专属的最佳方式是什么。
功能附在这里
帮助值得一提
''谢谢你''
Hi All,
I am using Excel object to convert csv file to excel with formatting
when Excel object is open if i open any excel file otside of application
it goes throws error.
perhaps outside excel i m trying to open , opens in same workbook what i
created.
what is the best way to open Excel and work book to open exclusively.
function is attached here
help is appreciable
''Thank You''
public string Convert(DataTable oDataTable, string directoryPath, string fileName)
{
object missing = Type.Missing;
string fullpath = "";
if (directoryPath.Substring(directoryPath.Length - 1, 1) == @"\" || directoryPath.Substring(directoryPath.Length - 1, 1) == "/")
{
fullpath = directoryPath + fileName;
}
else
{
fullpath = directoryPath + @"\" + fileName;
}
if (File.Exists(fullpath))
{
File.Delete(fullpath);
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(missing);
DataTable table = oDataTable;
int ColumnIndex = 0;
foreach (DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[1, ColumnIndex] = col.ColumnName;
}
int rowIndex = 0;
excel.Cells.NumberFormat = "@";
string colhecks = "";
foreach (DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex + 1, ColumnIndex] = row.ItemArray[ColumnIndex - 1];
}
//// c11 // code added to confirm numeric columns decimal precision 4 times
try
{
ColumnIndex = 0;
foreach (DataColumn col in table.Columns)
{
ColumnIndex++;
string colcode = GetChar(ColumnIndex);
if (IsNumeric(row.ItemArray[ColumnIndex - 1].ToString()))
{
if (row.ItemArray[ColumnIndex - 1].ToString().IndexOf(".") > 0)
{
if (colhecks.IndexOf(colcode) < 0)
{
excel.Cells.get_Range(colcode.ToString() + "1").EntireColumn.NumberFormat = "#.0000";
colhecks += colcode;
}
}
else
{
if (colhecks.IndexOf(colcode) < 0)
{
if (row.ItemArray[ColumnIndex - 1].ToString().Length > 13)
{
excel.Cells.get_Range(colcode.ToString() + "1").EntireColumn.NumberFormat = "@";
}
else
{
excel.Cells.get_Range(colcode.ToString() + "1").EntireColumn.NumberFormat = "0";
}
}
}
}
excel.Cells[rowIndex + 1, ColumnIndex] = row.ItemArray[ColumnIndex - 1];
/// code to increase width of columns
excel.Cells.get_Range(colcode.ToString() + "1").EntireColumn.AutoFit();
/// code to increase width of columns specific
if (excel.Cells.get_Range(colcode.ToString() + "1").Text.ToString().ToUpper().Contains("RATE") || excel.Cells.get_Range(colcode.ToString() + "2").Text.ToString().ToUpper().Contains("RATE"))
{
try
{
int width = System.Convert.ToInt32(excel.Cells.get_Range(colcode.ToString() + "1").EntireColumn.ColumnWidth) + 2;
if (width < 25)
excel.Cells.get_Range(colcode.ToString() + "1").EntireColumn.ColumnWidth = width.ToString();
}
catch
{
}
}
/// end code to increase width of columns specific
/// end code to increase width of columns
}
}
catch (Exception ex)
{
}
//// end c11 //
}
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
//worksheet.Activate();
//worksheet.Columns.AutoFit();
workbook.Windows[1].Zoom = 80;
char[] chrSplit = { '.' };
string[] strFile = fileName.Split(chrSplit);
worksheet.Name = strFile[0].ToString();
workbook.SaveAs(fullpath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(Type.Missing, Type.Missing, Type.Missing);
workbook = null;
excel.Quit();
// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
return fullpath;
}
推荐答案
首次打开excel应用程序时使用这些设置
When you first open your excel application use these settings
excel.IgnoreRemoteRequests = true;
excel.Visible = false;
作为旁白而不是在整个代码中使用 icrosoft.Office.Interop.Excel
,例如
As an aside instead of using icrosoft.Office.Interop.Excel
throughout your code e.g.
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
尝试使用xl = Microsoft.Office.Interop.Excel放置
try putting
using xl = Microsoft.Office.Interop.Excel;
然后你可以使用
and then you can use
xl.Application excel = new xl.Application();
使事情更清晰
这篇关于Excel对象的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!