如何通过 Excel 互操作对象自动调整列的大小？

下面是我用来将数据加载到 Excel 工作表中的代码，但我希望在加载数据后自动调整列的大小。有人知道自动调整列大小的最佳方法吗？

using Microsoft.Office.Interop;


public class ExportReport
{
public void Export()
{
Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook wb;
Excel.Worksheet ws;
Excel.Range aRange;
object m = Type.Missing;
string[,] data;
string errorMessage = string.Empty;
try
{
if (excelApp == null)
throw new Exception("EXCEL could not be started.");


// Create the workbook and worksheet.
wb = excelApp.Workbooks.Add(Office.Excel.XlWBATemplate.xlWBATWorksheet);
ws = (Office.Excel.Worksheet)wb.Worksheets[1];


if (ws == null)
throw new Exception("Could not create worksheet.");


// Set the range to fill.
aRange = ws.get_Range("A1", "E100");


if (aRange == null)
throw new Exception("Could not get a range.");


// Load the column headers.
data = new string[100, 5];
data[0, 0] = "Column 1";
data[0, 1] = "Column 2";
data[0, 2] = "Column 3";
data[0, 3] = "Column 4";
data[0, 4] = "Column 5";


// Load the data.
for (int row = 1; row < 100; row++)
{
for (int col = 0; col < 5; col++)
{
data[row, col] = "STUFF";
}
}


// Save all data to the worksheet.
aRange.set_Value(m, data);
// Atuo size columns
// TODO: Add Code to auto size columns.


// Save the file.
wb.SaveAs("C:\Test.xls", Office.Excel.XlFileFormat.xlExcel8, m, m, m, m, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m, m, m, m, m);
// Close the file.
wb.Close(false, false, m);
}
catch (Exception) { }
finally
{
// Close the connection.
cmd.Close();
// Close Excel.
excelApp.Quit();
}
}
}
136697 次浏览
小开

看看这篇文章，它并不完全符合你的问题，但很适合你:

小开
最佳答案

在你的 TODO 点添加以下内容:

aRange.Columns.AutoFit();

小开

还有

aRange.EntireColumn.AutoFit();

参见 Range. Columns 和 Range. EntireColumn 之间的区别是什么。

小开

这可能为时已晚，但如果你添加

 worksheet.Columns.AutoFit();

或者

 worksheet.Rows.AutoFit();

它也有用。

小开

此方法打开已创建的 Excel 文件，基于 第三排自动适应所有工作表的所有列。正如您可以看到的，范围是在 Excel 中从 “ A3至 K3”中选择的。

 public static void AutoFitExcelSheets()
{
Microsoft.Office.Interop.Excel.Application _excel = null;
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = null;
try
{
string ExcelPath = ApplicationData.PATH_EXCEL_FILE;
_excel = new Microsoft.Office.Interop.Excel.Application();
_excel.Visible = false;
object readOnly = false;
object isVisible = true;
object missing = System.Reflection.Missing.Value;


excelWorkbook = _excel.Workbooks.Open(ExcelPath,
0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
foreach (Microsoft.Office.Interop.Excel.Worksheet currentSheet in excelSheets)
{
string Name = currentSheet.Name;
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(Name);
Microsoft.Office.Interop.Excel.Range excelCells =
(Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("A3", "K3");
excelCells.Columns.AutoFit();
}
}
catch (Exception ex)
{
ProjectLog.AddError("EXCEL ERROR: Can not AutoFit: " + ex.Message);
}
finally
{
excelWorkbook.Close(true, Type.Missing, Type.Missing);
GC.Collect();
GC.WaitForPendingFinalizers();
releaseObject(excelWorkbook);
releaseObject(_excel);
}
}

提交答案