迷思

一直有人在問DataGrid的資料如何匯出Excel. DataGrid並沒有存取每個Cell值的方法, 硬要作的話就是要寫一大堆代碼。

但試想, DataGrid裏的值, 是那裏來的? 就是使用 grid.DataContext = DataTable, 把DataTable裏放入DataGrid裏

所以主題要變換一下, 如何把DataTable匯出Excel

Excel套件

網路上大都教學使用Microsoft.Office.Interop.Excel套件，此套件是微軟自行開發的。在匯出資料時，會於背景開啟Excel, 然後與C#進行資料的傳輸。此方法除了效能極差之外，客戶端必需安裝Excel才可執行。所以根本就不建議使用此方法。

本章要介紹的，是另一個效能極高的NPOI套件，而且客戶端不需安裝Excel就能執行。請先由工具/NuGet套件管理員/管理方案的NuGet套件，然後於瀏覽搜尋NPOI套件，進行安裝即可

建立工作簿及工作表

NPOI.XSSF是建立Excel 2006及以上的版本，副檔名為 .xlsx。NPOI.HSSF則是建立2006以前的版本，副檔名為 .xls。

首先使用XSSFWorkbook()產生一個工作簿物件workbook, 再由workbook.CreateSheet(“Sheet”)工作表 sheet 物件。然後就可以開始由sheet進行資料填入。

using NPOI.XSSF.UserModel;
XSSFWorkbook workbook = new XSSFWorkbook(); //新建xlsx工作簿
workbook.CreateSheet("Sheet1");
XSSFSheet sheet = (XSSFSheet)workbook.GetSheet("Sheet1");

建立列

使用sheet.CreateRow(列)，參數的列，由0開始。建立後，即可由sheet.GetRow(列)取得列物件

var row=sheet.CreateRow(i);

建立欄

使用row.CreateCell(欄)建立欄物件，再使用SetCellValue(值)將資料填入儲存格

row.CreateCell(j).SetCellValue(f)

儲存檔案

先產生FileStream file檔案物件，然後由工作簿workbook.Write(file)。記得後續需close file及workbook.

FileStream file = new FileStream(excelFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook.Close();

圖片

1. 先使用new WebClient().DownloadData(url)下載圖片置於byte[]陣列中。
2. 使用workbook.AddPicture()將圖片加入工作簿，並記錄圖片索引 pictureIdx。
3. 使用sheet.CreateDrawingPatriarch在工作表建立Patriarch
4. 使用XSSFClientAnchor()建立描點。前四個參數為圖片位置，可全設為0。後四個分別為(啟始欄，啟始列，結束欄，結束列)
5. 使用patriach.CreatePicture將圖片加入

string url;
byte[] bytes;
IDrawing patriarch;
XSSFClientAnchor anchor;
XSSFPicture pict;
try//底下有可能圖片不見了找不到
{
    url = t.Rows[i][j].ToString();
    bytes = new WebClient().DownloadData(url);
    pictureIdx = workbook.AddPicture(bytes, XSSFWorkbook.PICTURE_TYPE_JPEG);
    patriarch = sheet.CreateDrawingPatriarch();
    anchor = new XSSFClientAnchor(0, 0, 0, 0, j, i + 1, j + 1, i + 2);//(0,0,0,0,啟始欄,啟始列,結束欄，結束列)
    pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
catch { }

完整代碼

private void ExportExcel(Object table)
{
	SaveFileDialog saveFileDialog = new SaveFileDialog();
	saveFileDialog.Filter = "Excel Files(*.xlsx)|*.xlsx";
	if (saveFileDialog.ShowDialog() == true)
	{
		psBar.Value = 0;
		psBar.IsIndeterminate = false;
		psBar.Visibility = Visibility.Visible;
		btn匯出Excel.IsEnabled = false;
		new Thread(() => {
			int h = 115, pictureIdx;
			string url, content;
			byte[] bytes;
			IDrawing patriarch;
			XSSFClientAnchor anchor;
			XSSFPicture pict;
			string fileName = saveFileDialog.FileName;
			DataTable t = (DataTable)table;
			XSSFWorkbook workbook = new XSSFWorkbook(); //新建xlsx工作簿
			workbook.CreateSheet("Sheet1");
			XSSFSheet sheet = (XSSFSheet)workbook.GetSheet("Sheet1");
			sheet.SetColumnWidth(0, 30 * 256);
			sheet.SetColumnWidth(1, 30 * 256);
			sheet.SetColumnWidth(2, 10 * 256);//來源
			sheet.SetColumnWidth(3, 17 * 256);//巡查案號
			sheet.SetColumnWidth(4, 10 * 256);//日期
			sheet.SetColumnWidth(5, 50 * 256);//地址
			sheet.SetColumnWidth(6, 10 * 256);//損壞類別
			sheet.SetColumnWidth(7, 10 * 256);//巡查員
			sheet.SetColumnWidth(8, 15 * 256);//道管編號
			sheet.SetColumnWidth(9, 15 * 256);//會勘編號
			sheet.SetColumnWidth(10, 15 * 256);//派工單號
			sheet.SetColumnWidth(11, 15 * 256);//回報單號
			IRow row=sheet.CreateRow(0);
			row = sheet.CreateRow(0);
			for (int i = 0; i < t.Columns.Count; i++)
			{
				row.CreateCell(i).SetCellValue(t.Columns[i].ToString());
			}
			for (int i = 0; i < t.Rows.Count; i++)
			{
				row = sheet.CreateRow(i+1);
				row.HeightInPoints = h;
				for (int j = 0; j < t.Columns.Count; j++)
				{
					if (t.Rows[i][j].ToString().StartsWith("http"))
					{
						try//底下有可能圖片不見了找不到
						{
							url = t.Rows[i][j].ToString();
							bytes = new WebClient().DownloadData(url);
							pictureIdx = workbook.AddPicture(bytes, XSSFWorkbook.PICTURE_TYPE_JPEG);
							patriarch = sheet.CreateDrawingPatriarch();
							anchor = new XSSFClientAnchor(0, 0, 0, 0, j, i + 1, j + 1, i + 2);//(0,0,0,0,啟始欄,啟始列,結束欄，結束列)
							pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
						}
						catch { }
					}
					else
					{
						content = t.Rows[i][j].ToString();
						if (!content.Equals("'"))row.CreateCell(j).SetCellValue(t.Rows[i][j].ToString());
					}
				}
				Dispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(() => {
					psBar.Value = (int)((float)i / t.Rows.Count * 100);
				}));
			}

			FileStream file = new FileStream(fileName, FileMode.Create);
			workbook.Write(file);
			file.Close();
			workbook.Close();
			Dispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(() => {
				psBar.Value = 100;
				psBar.IsIndeterminate = false;
				psBar.Visibility = Visibility.Collapsed;
				MessageBox.Show(string.Format("匯出Excel完成\n{0}", fileName), "派工單維護明細");
				btn匯出Excel.IsEnabled = true;
			}));

		}).Start();
	}
}