下面是我的代碼，我遇到了無法在excel中追加內容的問題，第一次加載程式時，excel可以正常輸出但在第二次加載程式時，excel會嚴重崩潰（無法打開）。我正在使用 FileMode.Append、FileAccess.Write，但仍然無法作業 有沒有我錯過的步驟？請幫我解決這個問題，非常感謝！
XSSFWorkbook XSSFworkbook = new XSSFWorkbook(); //建立活頁簿
ISheet sheet = XSSFworkbook.CreateSheet(tbx_Build.Text); //建立sheet
//設定樣式
ICellStyle headerStyle = XSSFworkbook.CreateCellStyle();
IFont headerfont = XSSFworkbook.CreateFont();
headerStyle.Alignment = HorizontalAlignment.Center; //水平置中
headerStyle.VerticalAlignment = VerticalAlignment.Center; //垂直置中
headerfont.FontName = "Segoe UI";
headerfont.FontHeightInPoints = 12;
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headerStyle.SetFont(headerfont);
XSSFCellStyle cs = (XSSFCellStyle)XSSFworkbook.CreateCellStyle();
cs.WrapText = true; // 設定換行
cs.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top;
//新增標題列
IRow headerrow = sheet.CreateRow(0);//建立行
headerrow.HeightInPoints = 20;
headerrow.CreateCell(0).SetCellValue("System_name");
headerrow.CreateCell(1).SetCellValue("Fixture_name");
headerrow.CreateCell(2).SetCellValue("build_ID");
headerrow.CreateCell(3).SetCellValue("start_time");
headerrow.CreateCell(4).SetCellValue("end_time");
headerrow.CreateCell(5).SetCellValue("serial_number");
headerrow.CreateCell(6).SetCellValue("Status");
headerrow.CreateCell(7).SetCellValue("Symptom_label");
headerrow.CreateCell(8).SetCellValue("Repair");
headerrow.CreateCell(9).SetCellValue("Measurement");
headerrow.CreateCell(10).SetCellValue("Board_slot");
headerrow.CreateCell(11).SetCellValue("Error");
headerrow.CreateCell(12).SetCellValue("Version");
for (int i = 0; i < 13; i )
{
headerrow.GetCell(i).CellStyle = headerStyle; //套用樣式
}
//填入資料
int rowIndex = 1;
for (int i = 0; i < dt.Rows.Count; i )
{
IRow row = sheet.CreateRow(rowIndex);//建立行
row.HeightInPoints = 18;
row.CreateCell(0).SetCellValue(Convert.ToString(dt.Rows[i]["System_name"]));
row.CreateCell(1).SetCellValue(Convert.ToString(dt.Rows[i]["Fixture_name"]));
row.CreateCell(2).SetCellValue(Convert.ToString(dt.Rows[i]["build_ID"]));
row.CreateCell(3).SetCellValue(Convert.ToString(dt.Rows[i]["start_time"]));
row.CreateCell(4).SetCellValue(Convert.ToString(dt.Rows[i]["end_time"]));
row.CreateCell(5).SetCellValue(Convert.ToString(dt.Rows[i]["serial_number"]));
row.CreateCell(6).SetCellValue(Convert.ToString(dt.Rows[i]["Status"]));
row.CreateCell(7).SetCellValue(Convert.ToString(dt.Rows[i]["Symptom_label"]));
row.CreateCell(8).SetCellValue(Convert.ToString(dt.Rows[i]["Repair"]));
row.CreateCell(9).SetCellValue(Convert.ToString(dt.Rows[i]["Measurement"]));
row.CreateCell(10).SetCellValue(Convert.ToString(dt.Rows[i]["Board_slot"]));
row.CreateCell(11).SetCellValue(Convert.ToString(dt.Rows[i]["Error"]));
row.CreateCell(12).SetCellValue(Convert.ToString(dt.Rows[i]["Version"]));
if (dt.Rows[i]["Error"].ToString().Contains("\n"))
{
row.GetCell(12).CellStyle = cs;
row.HeightInPoints = 45;
}
else if (dt.Rows[i]["Repair"].ToString().Contains("\n"))
{
row.GetCell(12).CellStyle = cs;
row.HeightInPoints = 45;
}
sheet.AutoSizeColumn(i); //欄位自動調整大小
rowIndex ;
}
string newName2 = "Yield.xlsx";
string exportpath2 = server_backup_failed "\\output";
if (!System.IO.Directory.Exists(exportpath2))
{
System.IO.Directory.CreateDirectory(exportpath2);//不存在就建立目錄
}
var file2 = new FileStream(exportpath2 "\\" newName2, FileMode.Append, FileAccess.Write);
XSSFworkbook.Write(file2, true);
file2.Close();
XSSFworkbook.Close();
}
uj5u.com熱心網友回復：
正如已經評論過的……我可以從一些測驗中確認……
FileMode.Append
不會按您的意圖作業。這似乎有些明顯，因為作業簿可以有多個作業表，而您所描述的是將資料附加到現有作業表......他們FileStream不知道如何做到這一點。
因此，如前所述，您需要打開檔案，進行更改（追加行），然后保存檔案并關閉它。在我的測驗中，這使用您的大部分代碼進行了一些小的更改。在您當前的代碼中，它始終創建一個“新”Excel 作業簿和作業表。因此，如果您想將某些內容“附加”到作業表，那么這“意味著”作業簿和作業表可能已經存在。所以，如果作業簿已經存在，那么我們想要打開它而不是創建一個新的。
因此，我建議使用一個小方法，它接受string檔案路徑和檔案名并回傳一個作業簿。如果作業簿存在，那么我們只需回傳作業簿。如果作業簿不存在，那么我們回傳一個“新”作業簿。如果路徑錯誤或任何其他故障……我們將回傳一個null值。就像是…
private XSSFWorkbook GetExcelWorkbook(string filePath) {
if (!File.Exists(filePath)) {
return new XSSFWorkbook();
}
try {
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {
return new XSSFWorkbook(fs);
}
}
catch (Exception ex) {
Debug.WriteLine("Excel Error getting workbook: " ex.Message);
return null;
}
}
這應該有助于獲取作業簿。接下來，代碼似乎正在尋找“特定”作業表。與上面的代碼類似，一個接受作業簿和作業表名稱并回傳具有給定名稱的作業表的簡單方法可能會派上用場。同樣，如果作業表已經存在，那么我們只需回傳該作業表。如果作業表不存在，那么我們將創建一個具有給定名稱的新作業表。此外，如果作業表是新的，我們將繼續添加標題行。這也是另一種方法，因此您還可以在每次附加新資料時添加標題行。但是，在此示例中，僅在創建新作業表時添??加標題，并假定標題行已存在于現有檔案中。
這種方法可能看起來像……
private ISheet GetWorksheet(XSSFWorkbook wb, string targetSheetName) {
try {
for (int i = 0; i < wb.NumberOfSheets; i ) {
if (wb.GetSheetAt(i).SheetName.Equals(targetSheetName)) {
return wb.GetSheetAt(i);
}
}
ISheet ws = wb.CreateSheet(targetSheetName);
AddHeaders(wb, ws);
return ws;
}
catch (Exception e) {
Debug.WriteLine("Excel Error getting worksheet: " e.Message);
return null;
}
}
A walkthrough of the code above is straight forward… Loop through the given workbooks worksheets to look for the target worksheet name. If the worksheet is found it is returned. If the worksheet is not found, then a new worksheet is created with the target name, add the headers to the new worksheet then return the new worksheet. If some error arises a null value is returned.
Next is the method that adds the headers to the worksheet and is taken almost directly from your code.
private void AddHeaders(XSSFWorkbook wb, ISheet sheet) {
IRow headerrow = sheet.CreateRow(0);
headerrow.HeightInPoints = 20;
headerrow.CreateCell(0).SetCellValue("System_name");
headerrow.CreateCell(1).SetCellValue("Fixture_name");
headerrow.CreateCell(2).SetCellValue("build_ID");
headerrow.CreateCell(3).SetCellValue("start_time");
headerrow.CreateCell(4).SetCellValue("end_time");
headerrow.CreateCell(5).SetCellValue("serial_number");
headerrow.CreateCell(6).SetCellValue("Status");
headerrow.CreateCell(7).SetCellValue("Symptom_label");
headerrow.CreateCell(8).SetCellValue("Repair");
headerrow.CreateCell(9).SetCellValue("Measurement");
headerrow.CreateCell(10).SetCellValue("Board_slot");
headerrow.CreateCell(11).SetCellValue("Error");
headerrow.CreateCell(12).SetCellValue("Version");
ICellStyle headerStyle = wb.CreateCellStyle();
IFont headerfont = wb.CreateFont();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerfont.FontName = "Segoe UI";
headerfont.FontHeightInPoints = 12;
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headerStyle.SetFont(headerfont);
for (int i = 0; i < 13; i ) {
headerrow.GetCell(i).CellStyle = headerStyle;
}
}
These methods should simplify “appending” data to an existing worksheet or creating a new worksheet. So, using the above methods, the code below works as described here… This code is added to a button clicks event. Initially, some test data is created for the DataTable dt that will be appended to the worksheet. Next, we check if the given folder path exists and if not, then create it.
Next we use our methods above to get the workbook and also the worksheet. The worksheet name is as you have in the current code and is coming from a TextBox ...tbx_Build on the form.
Next, instead of setting rowIndex to 1, we set it to the last row of existing data in the worksheet so we can “append” the data. Specifically the line…
int rowIndex = ws.LastRowNum 1;
Next a cell style is created and a loop through the DataTables dt rows where each cell is added to the worksheet. Note I removed the unnecessary “Convert” code and used the cells ToString() method. And finally a style is added to some cells for some reason.
And lastly, the FilesStream is created to save the file and possibly overwrite it when it’s not new.
You may note, that the workbook wb is closed in the finally portion of the try/catch/finally statement and the reason for this is that if the code fails sometime after the workbook is open, then it may not get closed. This ensures that the workbook is properly closed.
private void button1_Click(object sender, EventArgs e) {
string workbookName = "__NewBook_1.xlsx";
string saveFilePath = @"D:\Test\Excel_Test";
DataTable dt = GetTable();
FillTable(dt);
XSSFWorkbook wb = null;
try {
if (!Directory.Exists(saveFilePath)) {
Directory.CreateDirectory(saveFilePath);
}
wb = GetExcelWorkbook(saveFilePath @"\" workbookName);
if (wb != null) {
ISheet ws = GetWorksheet(wb, tbx_Build.Text);
if (ws != null) {
int rowIndex = ws.LastRowNum 1;
ICellStyle cs = wb.CreateCellStyle();
cs.WrapText = true;
cs.VerticalAlignment = VerticalAlignment.Top;
for (int i = 0; i < dt.Rows.Count; i ) {
IRow row = ws.CreateRow(rowIndex);
row.HeightInPoints = 18;
row.CreateCell(0).SetCellValue(dt.Rows[i]["System_name"].ToString());
row.CreateCell(1).SetCellValue(dt.Rows[i]["Fixture_name"].ToString());
row.CreateCell(2).SetCellValue(dt.Rows[i]["build_ID"].ToString());
row.CreateCell(3).SetCellValue(dt.Rows[i]["start_time"].ToString());
row.CreateCell(4).SetCellValue(dt.Rows[i]["end_time"].ToString());
row.CreateCell(5).SetCellValue(dt.Rows[i]["serial_number"].ToString());
row.CreateCell(6).SetCellValue(dt.Rows[i]["Status"].ToString());
row.CreateCell(7).SetCellValue(dt.Rows[i]["Symptom_label"].ToString());
row.CreateCell(8).SetCellValue(dt.Rows[i]["Repair"].ToString());
row.CreateCell(9).SetCellValue(dt.Rows[i]["Measurement"].ToString());
row.CreateCell(10).SetCellValue(dt.Rows[i]["Board_slot"].ToString());
row.CreateCell(11).SetCellValue(dt.Rows[i]["Error"].ToString());
row.CreateCell(12).SetCellValue(dt.Rows[i]["Version"].ToString());
if (dt.Rows[i]["Error"].ToString().Contains("\n")) {
row.GetCell(12).CellStyle = cs;
row.HeightInPoints = 45;
}
else if (dt.Rows[i]["Repair"].ToString().Contains("\n")) {
row.GetCell(12).CellStyle = cs;
row.HeightInPoints = 45;
}
ws.AutoSizeColumn(i);
rowIndex ;
}
using (FileStream fs = new FileStream(saveFilePath @"\" workbookName, FileMode.Create, FileAccess.Write)) {
wb.Write(fs, true);
}
}
}
}
catch (Exception ex) {
Debug.WriteLine("Excel Error: " ex.Message);
}
finally {
if (wb != null) {
wb.Close();
}
}
}
And finally the code that creates some test data to test the above methods and complete the example.
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment;
private DataTable GetTable() {
DataTable dt = new DataTable();
dt.Columns.Add("System_name");
dt.Columns.Add("Fixture_name");
dt.Columns.Add("build_ID");
dt.Columns.Add("start_time");
dt.Columns.Add("end_time");
dt.Columns.Add("serial_number");
dt.Columns.Add("Status");
dt.Columns.Add("Symptom_label");
dt.Columns.Add("Repair");
dt.Columns.Add("Measurement");
dt.Columns.Add("Board_slot");
dt.Columns.Add("Error");
dt.Columns.Add("Version");
return dt;
}
private void FillTable(DataTable dt) {
for (int i = 0; i < 20; i ) {
dt.Rows.Add("C0R" (i 1), "C1R" (i 1), "C2R" (i 1), "C3R" (i 1), "C4R" (i 1),
"C5R" (i 1), "C6R" (i 1), "C7R" (i 1), "C8R" (i 1), "C9R" (i 1),
"C10R" (i 1), "C11R" (i 1), "C12R" (i 1));
}
}
I hope this makes sense. I tested this numerous times and it appears to work as expected, however there may still be some necessary exception checking that was not added for brevity. If something is not correct please let me know and I will correct it if possible.
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/352402.html
上一篇：Excel中的單元格條件格式