ASP.NET DataTable轉成Excel實體檔案

ASP.NET DataTable轉成Excel實體檔案


C#:

    protected void Button1_Click(object sender, EventArgs e)
    {
        #region 建立虛擬表格
        DataTable dt = new DataTable();
        DataColumn column;
        column = new DataColumn();
        column.DataType = Type.GetType("System.String");
        column.ColumnName = "company_name";
        dt.Columns.Add(column);
        column = new DataColumn();
        column.DataType = Type.GetType("System.String");
        column.ColumnName = "tel";
        dt.Columns.Add(column);
        column = new DataColumn();
        column.DataType = Type.GetType("System.String");
        column.ColumnName = "fax";
        dt.Columns.Add(column);
        column = new DataColumn();
        column.DataType = Type.GetType("System.String");
        column.ColumnName = "email";
        dt.Columns.Add(column);
        column = new DataColumn();
        column.DataType = Type.GetType("System.String");
        column.ColumnName = "contact";
        dt.Columns.Add(column);
        column = new DataColumn();
        column.DataType = Type.GetType("System.String");
        column.ColumnName = "company_address";
        dt.Columns.Add(column);
        #endregion

        //輸出
        ExcelFileStream(dt, @"D:\", "abc.xls");
    }

    private void ExcelFileStream(DataTable dt, string savepath, string filename)
    {
        try
        {
            Stream fs = RenderDataTableToExcel(dt);
            SaveStreamToFile(savepath + filename, fs);
        }
        catch (Exception)
        {
            throw;
        }
    }

    public static Stream RenderDataTableToExcel(DataTable srcTable)
    {
        try
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
            HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
            foreach (DataColumn column in srcTable.Columns)
            {
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
            }
            int rowIndex = 1;
            foreach (DataRow row in srcTable.Rows)
            {
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                foreach (DataColumn column in srcTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }
                rowIndex++;
            }

            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            stream.Flush();
            stream.Position = 0;
            sheet = null;
            headerRow = null;
            workbook = null;
            return stream;
        }
        catch (Exception)
        {

            throw;
        }

    }

    public void SaveStreamToFile(string fileFullPath, Stream stream)
    {
        try
        {
            if (stream.Length == 0) return;

            // Create a FileStream object to write a stream to a file
            using (FileStream fileStream = System.IO.File.Create(fileFullPath, (int)stream.Length))
            {
                // Fill the bytes[] array with the stream data
                byte[] bytesInStream = new byte[stream.Length];
                stream.Read(bytesInStream, 0, (int)bytesInStream.Length);

                // Use FileStream object to write to the specified file
                fileStream.Write(bytesInStream, 0, bytesInStream.Length);
            }
        }
        catch (Exception)
        {
            throw;
        }

    }