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 新增到專案中。
- 將資料從 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;
}
- 將 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 。