c#数据到excel导出给出空文件

Question

我目前正在使用c#进行一些Excel导入/导出，

这是我的导出函数:

private void myButton11_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkbook;
Microsoft.Office.Interop.Excel.Worksheet excelsheet;
Microsoft.Office.Interop.Excel.Range excelCellFormat;
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
excelworkbook = excel.Workbooks.Add(Type.Missing);
excelsheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkbook.ActiveSheet;
excelsheet.Name = "dataToExcel";
// fill in data
excelsheet.Cells[1, 1] = "test";
// left it pretty much empty so you have a nice exemple
excelCellFormat = excelsheet.Range[excelsheet.Cells[1, 1], excelsheet.Cells[max+1, 13]];
excelCellFormat.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellFormat.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel (*.xlsx)|*.xlsx";
sfd.Title = "Sauvegarde";
sfd.ShowDialog();
if(sfd.FileName != "")
{
System.IO.FileStream fs = (System.IO.FileStream)sfd.OpenFile();

// save excel
excelworkbook.SaveAs(fs,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value);
fs.Close();
}

excel.Quit();
// for a pdf version
//excelworkbook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF,
//                                    Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "//dataAsPDF.pdf",
//                                    Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard,
//                                    true, true, 1, 10, false);
}

我的问题是，当保存使用外部窗口，它保存正确…但是作为一个空文件，比如完全空(用记事本打开它会显示一个空文件)

知道我的问题是什么吗?

谢谢!

Answer 1

下面是一个通用代码示例，它创建一个新文件，重命名一个工作表，添加一个工作表，然后将第一个工作表单元格A1的文本设置为Hello Excel。

Excel类

using System;
using System.Diagnostics;
using System.Linq;
using System.IO;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
namespace ExcelInteropApp.Classes
{
public class ExcelOperations
{
public delegate void OnAction(string sender);
public static event OnAction ActionHandler;
/// <summary>
/// create an excel file, rename sheet1 (default sheet),
/// create another worksheet, rename it and re-order to end.
/// </summary>
/// <param name="fileName">path and file name for excel file</param>
/// <param name="firstWorkSheetName">name for default sheet</param>
/// <param name="secondWorkSheetName">name for newly added sheet</param>
public static (bool success, Exception exception) CreateExcelFile(string fileName, string firstWorkSheetName, string secondWorkSheetName, bool open)
{
try
{
if (File.Exists(fileName))
{
File.Delete(fileName);
}
Excel.Application xlApp;
Excel.Workbooks xlWorkBooks;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Sheets xlWorkSheets;


xlApp = new Excel.Application { DisplayAlerts = false };
xlWorkBooks = xlApp.Workbooks;
xlWorkBook = xlWorkBooks.Add();
xlWorkSheets = xlWorkBook.Sheets;

xlWorkSheet = (Excel.Worksheet)xlWorkSheets[1];
xlWorkSheet.Name = firstWorkSheetName;
ActionHandler?.Invoke("renamed first sheet");

Excel.Worksheet xlNewSheet = (Excel.Worksheet)xlWorkSheets
.Add(xlWorkSheets[1], 
Type.Missing, 
Type.Missing, 
Type.Missing);
xlNewSheet.Move(System.Reflection.Missing.Value, xlWorkSheets[xlWorkSheets.Count]);
xlNewSheet.Name = secondWorkSheetName;
Excel.Range xlRange1 = null;
xlRange1 = xlWorkSheet.Range["A1"];
xlRange1.Value = "Hello Excel";
Marshal.FinalReleaseComObject(xlRange1);
xlRange1 = null;
ActionHandler?.Invoke("Done with add sheet");
Marshal.FinalReleaseComObject(xlNewSheet);
xlNewSheet = null;
xlWorkBook.SaveAs(fileName);
ActionHandler?.Invoke("Saved file");
xlWorkBook.Close();
xlApp.UserControl = true;
xlApp.Quit();
Marshal.FinalReleaseComObject(xlWorkSheets);
xlWorkSheets = null;
Marshal.FinalReleaseComObject(xlWorkSheet);
xlWorkSheet = null;
Marshal.FinalReleaseComObject(xlWorkBook);
xlWorkBook = null;
Marshal.FinalReleaseComObject(xlWorkBooks);
xlWorkBooks = null;
Marshal.FinalReleaseComObject(xlApp);
xlApp = null;
ActionHandler?.Invoke($"Clean-up: {(Process.GetProcesses().Any((p) => p.ProcessName.Contains("EXCEL")) ? "Released" : "Not released")}");
if (open)
{
ActionHandler?.Invoke("Opening");
Process.Start(fileName);
}
else
{
ActionHandler?.Invoke("Not opening");
}

return (true, null);
}
catch (Exception exception)
{
return (false, exception);
}
}

}
}

表单代码，一个按钮和一个列表框

using System;
using System.IO;
using System.Windows.Forms;
using ExcelInteropApp.Classes;
namespace ExcelInteropApp
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void CreateExcelButton1_Click(object sender, EventArgs e)
{
listBox1.Items.Clear();
ExcelOperations.ActionHandler += ExcelOperationsOnActionHandler;
string fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo.xlsx");
string firstSheet = "Karen";
string secondSheet = "Karen 1";

var (success, exception) = ExcelOperations.CreateExcelFile(fileName,firstSheet, secondSheet, openWhenDoneCheckBox.Checked);
if (success == false)
{
Console.WriteLine(exception.Message);
}
ExcelOperations.ActionHandler -= ExcelOperationsOnActionHandler;
}
private void ExcelOperationsOnActionHandler(string sender)
{
listBox1.Items.Add(sender);
}
}
}