EXCEL与SQL数据库间导入导出之傻瓜闲扯淡

.xaml.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Aspose.Cells;
using Microsoft.Win32;

namespace EXCELexportAndLeadIn
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        private const string Connstr = "server=127.0.0.1;uid=sa;pwd=123456;database=Music";
        public MainWindow()
        {
            InitializeComponent();
        }

        public DataTable GetDataTable(string str)
        {
            SqlConnection conn = new SqlConnection(Connstr);
            SqlDataAdapter sda = new SqlDataAdapter(@"SELECT * FROM " + str + "", conn);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            dt.Dispose();
            return dt;
        }
        private void Export(object sender, RoutedEventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx";

            if (openFileDialog.ShowDialog() == true)
            {
                Workbook wbWorkbook = new Workbook(openFileDialog.FileName);
                Worksheet wsWorksheet = wbWorkbook.Worksheets[0];

                int maxColumn = wsWorksheet.Cells.MaxColumn + 1;
                int maxRow = wsWorksheet.Cells.MaxRow + 1;

                DataTable exportDataTable = wsWorksheet.Cells.ExportDataTable(0, 0, maxRow, maxColumn,true);

                MyDataGrid.ItemsSource = null;
                MyDataGrid.Columns.Clear();
                MyDataGrid.AutoGenerateColumns = true;
                MyDataGrid.ItemsSource = exportDataTable.DefaultView;

                //将数据导入数据库

                SqlConnection Con=new SqlConnection(Connstr);
                Con.Open();
                SqlBulkCopy sqlBulkCopy=new SqlBulkCopy(Con);
                sqlBulkCopy.DestinationTableName = "EXCELtest";
                sqlBulkCopy.WriteToServer(exportDataTable);
                Con.Close();
            }
        }

        private void LeadIn(object sender, RoutedEventArgs e)
        {
            DataTable dtDataTable = GetDataTable("EXCELtest");

            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx";

            if (openFileDialog.ShowDialog() == true)
            {

                var workbook = new Workbook();
                if (workbook.Worksheets.Count == 0)
                {
                    workbook.Worksheets.Add("Sheet1");
                }
                Worksheet worksheet = workbook.Worksheets[0];

                MyDataGrid.ItemsSource = null;
                MyDataGrid.Columns.Clear();
                MyDataGrid.AutoGenerateColumns = true;
                MyDataGrid.ItemsSource = dtDataTable.DefaultView;

                worksheet.AutoFitColumns();
                int importDataTable = worksheet.Cells.ImportDataTable(dtDataTable, false, 0, 0);
                workbook.Save(openFileDialog.FileName);
                Process.Start(openFileDialog.FileName);
            }
        }
    }
}

.xaml

<Window x:Class="EXCELexportAndLeadIn.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="600" Width="800">
    <Grid>
        <Grid.RowDefinitions>
            <RowDefinition Height="80*"/>
            <RowDefinition Height="500*"/>
        </Grid.RowDefinitions>
       
            <DataGrid></DataGrid>
        <Button Content="导入" Click="Export" Grid.Row="0" HorizontalAlignment="Left" Margin="100,26,0,0" VerticalAlignment="Top" Width="75"/>
        <Button Content="导出" Click="LeadIn" Grid.Row="0" HorizontalAlignment="Left" Margin="275,26,0,0" VerticalAlignment="Top" Width="75"/>
        <DataGrid Name="MyDataGrid" Grid.Row="1"></DataGrid>
    </Grid>
</Window>