ImportExport Azure Excel 文件来自 ASP.NET 中的 Azure SQL Server

此示例演示如何将工作表 Azure Excel 文件 blob 导入 Azure SQL Server 上的数据库以及如何将其从数据库导出到 Azure Excel blob。

先决条件：

• Microsoft Visual Studio 2015 版本
• 打开适用于 Microsoft Office 的 XML SDK 2.5
• Azure 存储帐户
• Azure SQL Server

将引用 DocumentFormat.OpenXml 添加到项目中。

1. 将数据从 DB 导出到 Azure Excel blob
   将 excel 保存到服务器存储，然后将其上载到 Azure。

public static string DBExportToExcel() 
{ 
    string result = string.Empty; 
    try 
    { 
        //Get datatable from db 
        DataSet ds = new DataSet(); 
        SqlConnection connection = new SqlConnection(connectionStr);                 
        SqlCommand cmd = new SqlCommand($"SELECT {string.Join(",", columns)} FROM {tableName}", connection); 
        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd)) 
        { 
            adapter.Fill(ds); 
        } 
        //Check directory 
        if (!Directory.Exists(directoryPath)) 
        { 
            Directory.CreateDirectory(directoryPath); 
        } 
        // Delete the file if it exists 
        string filePath = $"{directoryPath}//{excelName}"; 
        if (File.Exists(filePath)) 
        { 
            File.Delete(filePath); 
        } 
 
        if (ds.Tables.Count > 0 && ds.Tables[0] != null || ds.Tables[0].Columns.Count > 0) 
        { 
            DataTable table = ds.Tables[0]; 
 
            using (var spreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook)) 
            { 
                // Create SpreadsheetDocument 
                WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart(); 
                workbookPart.Workbook = new Workbook(); 
                var sheetPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorksheetPart>(); 
                var sheetData = new SheetData(); 
                sheetPart.Worksheet = new Worksheet(sheetData); 
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets()); 
                string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(sheetPart); 
                Sheet sheet = new Sheet() { Id = relationshipId, SheetId = 1, Name = table.TableName }; 
                sheets.Append(sheet); 
 
                //Add header to sheetData 
                Row headerRow = new Row(); 
                List<String> columns = new List<string>(); 
                foreach (DataColumn column in table.Columns) 
                { 
                    columns.Add(column.ColumnName); 
 
                    Cell cell = new Cell(); 
                    cell.DataType = CellValues.String; 
                    cell.CellValue = new CellValue(column.ColumnName); 
                    headerRow.AppendChild(cell); 
                } 
                sheetData.AppendChild(headerRow); 
 
                //Add cells to sheetData 
                foreach (DataRow row in table.Rows) 
                { 
                    Row newRow = new Row(); 
                    columns.ForEach(col => 
                    { 
                        Cell cell = new Cell(); 
                        //If value is DBNull, do not set value to cell 
                        if (row[col] != System.DBNull.Value) 
                        { 
                            cell.DataType = CellValues.String; 
                            cell.CellValue = new CellValue(row[col].ToString()); 
                        } 
                        newRow.AppendChild(cell); 
                    }); 
                    sheetData.AppendChild(newRow); 
                } 
                result = $"Export {table.Rows.Count} rows of data to excel successfully."; 
            } 
        } 
 
        // Write the excel to Azure storage container 
        using (FileStream fileStream = File.Open(filePath, FileMode.Open)) 
        { 
            bool exists = container.CreateIfNotExists(); 
            var blob = container.GetBlockBlobReference(excelName); 
            blob.DeleteIfExists(); 
            blob.UploadFromStream(fileStream); 
        } 
    } 
    catch (Exception ex) 
    { 
        result =$"Export action failed. Error Message: {ex.Message}"; 
    } 
    return result; 
}

2. 将 Azure Excel 文件导入到数据库
   我们无法直接读取 excel blob 数据，因此我们必须将其保存到服务器存储中，然后进行处理。
   我们使用 SqlBulkCopy 批量插入数据到 db。

public static string ExcelImportToDB() 
{ 
    string result = string.Empty; 
    try 
    { 
        //Check directory 
        if (!Directory.Exists(directoryPath)) 
        { 
            Directory.CreateDirectory(directoryPath); 
        } 
        // Delete the file if it exists 
        string filePath = $"{directoryPath}//{excelName}"; 
        if (File.Exists(filePath)) 
        { 
            File.Delete(filePath); 
        } 
        // Download blob to server disk. 
        container.CreateIfNotExists(); 
        CloudBlockBlob blob = container.GetBlockBlobReference(excelName); 
        blob.DownloadToFile(filePath, FileMode.Create); 
 
        DataTable dt = new DataTable(); 
        using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filePath, false)) 
        { 
            //Get sheet data 
            WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart; 
            IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>(); 
            string relationshipId = sheets.First().Id.Value; 
            WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId); 
            Worksheet workSheet = worksheetPart.Worksheet; 
            SheetData sheetData = workSheet.GetFirstChild<SheetData>(); 
            IEnumerable<Row> rows = sheetData.Descendants<Row>(); 
 
            // Set columns 
            foreach (Cell cell in rows.ElementAt(0)) 
            { 
                dt.Columns.Add(cell.CellValue.InnerXml); 
            } 
 
            //Write data to datatable 
            foreach (Row row in rows.Skip(1)) 
            { 
                DataRow newRow = dt.NewRow(); 
                for (int i = 0; i < row.Descendants<Cell>().Count(); i++) 
                { 
                    if (row.Descendants<Cell>().ElementAt(i).CellValue != null) 
                    { 
                        newRow[i] = row.Descendants<Cell>().ElementAt(i).CellValue.InnerXml; 
                    } 
                    else 
                    { 
                        newRow[i] = DBNull.Value; 
                    } 
                } 
                dt.Rows.Add(newRow); 
            } 
        } 
 
        //Bulk copy datatable to DB 
        SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionStr); 
        try 
        { 
            columns.ForEach(col => { bulkCopy.ColumnMappings.Add(col, col); }); 
            bulkCopy.DestinationTableName = tableName; 
            bulkCopy.WriteToServer(dt); 
        } 
        catch (Exception ex) 
        { 
            throw ex; 
        } 
        finally 
        { 
            bulkCopy.Close(); 
        } 
        result = $"Import {dt.Rows.Count} rows of data to DB successfully."; 
    } 
    catch (Exception ex) 
    { 
        result = $"Import action failed. Error Message: {ex.Message}"; 
    } 
    return result; 
} 

有关详细信息，请参阅 https://code.msdn.microsoft.com/How-to-ImportExport-Azure-0c858df9 。