使用.net在excel中导出数据 [英] export data in excel using .net

问题描述

如何在c#.net中将数据从ms访问权限导出到ms excel

How to export data from ms access to ms excel in c#.net

推荐答案

请参阅以下链接
将数据导出到Excel，Word，PDF
导出讨论

See these links
Export Data to Excel, Word, PDF
Discussion for export

以下代码对我有用

TST数据库中的表

Below code works for me

Table in TST database

CREATE TABLE planets
(
name    varchar(50),
size    int
)

INSERT INTO planets
SELECT 'EARTH',1200

C#代码(控制台应用程序)

C# Code (Console Application)

using System;
using System.IO;
using System.Data.SqlClient;

namespace CP_316977
{
    class Program
    {
        static void Main(string[] args)
        {
            string path = @"D:\db.xls";
            if (!File.Exists(path))
            {
                // Create a file to write to.
                using (StreamWriter sw = File.CreateText(path))
                {
                    SqlConnection cn = new SqlConnection( "Data Source=MDT765;Initial Catalog=TST;User Id=sa;Password=sa@123;");

                    SqlCommand cmd = new SqlCommand("SELECT * FROM planets", cn);
                    try
                    {
                        cn.Open();
                        SqlDataReader dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            sw.WriteLine(dr["name"].ToString() + "\t" + dr["size"].ToString());
                        }

                        Console.WriteLine("Database has been exported.");
                    }
                    catch (Exception excpt)
                    {
                        Console.WriteLine(excpt.Message);
                    }
                }
   
            }
        }
    }
}

在您的项目中添加下面的参考

Hi,

Add reference below in your project

Microsoft office 12.0 controls library 

之后，使用下面的代码

After that use below code

add namespace
using Microsoft.Office.Interop.Excel;

ApplicationClass excel = new ApplicationClass();
                Workbook wBook;
                Worksheet wSheet;
                wBook = excel.Workbooks.Add(System.Reflection.Missing.Value);
                wSheet = (Worksheet)wBook.ActiveSheet;
                System.Data.DataTable dt = dset.Tables[0];
                System.Data.DataColumn dc = new DataColumn();
                int colIndex = 0;
                int rowIndex = 4;
                foreach (DataColumn dcol in dt.Columns)
                {
                    colIndex = colIndex + 1;
                    excel.Cells[5, colIndex] = dcol.ColumnName;
                }
                foreach (DataRow drow in dt.Rows)
                {
                    rowIndex = rowIndex + 1;
                    colIndex = 0;
                    foreach (DataColumn dcol in dt.Columns)
                    {
                        colIndex = colIndex + 1;
                        excel.Cells[rowIndex + 1, colIndex] = drow[dcol.ColumnName];
                    }
                }
                wSheet.Columns.AutoFit();
                String strFileName = Server.MapPath("~\\Images\\StockStatement.xls");


Boolean blnFileOpen = false;
                try
                {
                    System.IO.FileStream fileTemp = File.OpenWrite(strFileName);
                    fileTemp.Close();
                }
                catch
                {
                    blnFileOpen = false;
                }
                if (System.IO.File.Exists(strFileName))
                {
                    System.IO.File.Delete(strFileName);
                }
                Range oRng;
                wSheet.Cells[1, 2] = lblOffice1.Text;
                wSheet.Cells[3, 2] = lblCostCenter1.Text;
                wSheet.Cells[4, 1] = lblOfficeName1.Text;
                wSheet.get_Range("B1", "B1").Font.Bold = true;
                wSheet.get_Range("B1", "B1").Font.ColorIndex = 55;
                wSheet.get_Range("B3", "B3").Font.ColorIndex = 55;
                wSheet.get_Range("A4", "A4").Font.ColorIndex = 55;
                wSheet.get_Range("B1", "E1").Merge(Type.Missing);
                wSheet.get_Range("B3", "E3").Merge(Type.Missing);
                wSheet.get_Range("B1", "B1").HorizontalAlignment = Constants.xlCenter;
                wSheet.get_Range("B3", "B3").HorizontalAlignment = Constants.xlCenter;
                wSheet.get_Range("B3", "B3").Font.Bold = true;
                wSheet.get_Range("A4", "A4").Font.Bold = true;
                wSheet.get_Range("A4", "A4").HorizontalAlignment = Constants.xlLeft;
                wSheet.get_Range("A5", "P5").Font.Bold = true;
                wSheet.get_Range("A5", "P5").Interior.ColorIndex = 43;
                wSheet.Name = "Stock Statement";
                //AutoFit columns A:D. 
                oRng = wSheet.get_Range("A1", "P1");
                oRng.EntireColumn.AutoFit();
                wBook.SaveAs(strFileName, XlFileFormat.xlExcel12, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, XlSaveAsAccessMode.xlShared, XlSaveConflictResolution.xlLocalSessionChanges, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false);

这篇关于使用.net在excel中导出数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！