忙裡偷閒的部落格

NPOI是在.NET上實現DataTable、Excel互轉的利器，工作上我習慣用該組件來實現所需的功能。具體做法可以參考MSDN CodePlex上的說明。我根據gipi大所提供的換行作法，修改了CodePlex上匯出excel部分的程式碼，如下所示 :

using System;

using System.Collections.Generic;

using System.Data;

using System.IO;

using System.Linq;

using System.Web;

using NPOI;

using NPOI.HPSF;

using NPOI.HSSF;

using NPOI.HSSF.UserModel;

using NPOI.POIFS;

using NPOI.Util;

public class DataTableRenderToExcel

{

    public static Stream RenderDataTableToExcel(DataTable SourceTable)

    {

        HSSFWorkbook workbook = new HSSFWorkbook();

        MemoryStream ms = new MemoryStream();

        HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();

        HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);

        // handling header.

        foreach (DataColumn column in SourceTable.Columns)

            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

        // handling value.

        int rowIndex = 1;

        foreach (DataRow row in SourceTable.Rows)

        {

            HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);

            foreach (DataColumn column in SourceTable.Columns)

            {

                HSSFCellStyle cs = (HSSFCellStyle)workbook.CreateCellStyle();

                cs.WrapText = true; // 設定換行

                cs.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.TOP; //文字對齊方式

                dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());

                char[] ch = new char[]{'\n'};

                string[] str = row[column].ToString().Split(ch); 

                if (row[column].ToString().Contains("\n"))

                {

                    dataRow.Cells[column.Ordinal].CellStyle = cs;

                    dataRow.HeightInPoints = str.Length * sheet.DefaultRowHeight / 20;//計算Cell的高度

                }

            }

            rowIndex++;

        }

        workbook.Write(ms);

        ms.Flush();

        ms.Position = 0;

        sheet = null;

        headerRow = null;

        workbook = null;

        return ms;

    }

    public static void RenderDataTableToExcel(DataTable SourceTable, string FileName)

    {

        MemoryStream ms = RenderDataTableToExcel(SourceTable) as MemoryStream;

        FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);

        byte[] data = ms.ToArray();

        fs.Write(data, 0, data.Length);

        fs.Flush();

        fs.Close();

        data = null;

        ms = null;

        fs = null;

    }

}