public static string ExportDataTableToExcel(DataTable dt, string fn)
{
string str = "";
str = (string)dt.Rows[0][1];
MSExcel.Application excel = new MSExcel.Application();
excel.DisplayAlerts = false;
string submittedFilePath = @"C:\Users\510832\Documents\Files\" + fn;
MSExcel.Workbook excelbook = excel.Workbooks.Add();
MSExcel.Worksheet excelsheet = excelbook.Sheets.Add();
excelsheet.Name = fn;
int n = 0;
for (int i = 1; i <= dt.Columns.Count; i++)
{
excelsheet.Cells[1, i] = dt.Columns[i - 1].ColumnName.ToString();
}
n = dt.Columns.Count;
MSExcel.Range range1 = excel.ActiveCell.Worksheet.Cells[1, n] as MSExcel.Range;
range1.AutoFormat(MSExcel.XlRangeAutoFormat.xlRangeAutoFormatList3);
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
excelsheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
}
MSExcel.Range range2 = excel.ActiveCell.Worksheet.Cells[dt.Rows.Count, n] as MSExcel.Range;
range2.AutoFormat(MSExcel.XlRangeAutoFormat.xlRangeAutoFormatList3);
excelbook.SaveAs(submittedFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, MSExcel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
excelbook.Close();
excel.Quit();
return(submittedFilePath);
}
private void Button_Click(object sender, RoutedEventArgs e)
{
if (_xlApp != null)
{
Excel.Worksheet workSheet = _xlApp.ActiveWorkbook.Worksheets[1];
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
var accounts = Enumerable.Range(1, 100)
.Select((x, y) => new { Id = 100000 + y, Balance = new Random().Next(0, 300 * x) });
var index = 2;
foreach (var acct in accounts)
{
workSheet.Cells[index, "A"] = acct.Id;
workSheet.Cells[index, "B"] = acct.Balance;
index++;
}
// Call to AutoFormat in Visual C# 2010. This statement replaces the
// two calls to AutoFit.
workSheet.Range["A1", "B3"].AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
Excel.Worksheet ws = _xlApp.ActiveSheet;
Excel.Range range = ws.Range[ws.Cells[1, 1], ws.Cells[(2), (5)]]; // row 1 col 1 to row 2 col 5
ws.Names.Add("ChrisRange", range); //"Sheet1!ChrisRange"
//https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.aspx
//https://msdn.microsoft.com/en-us/library/7zte17ya.aspx
//https://msdn.microsoft.com/en-us/library/bb386091.aspx
// find the range "Sheet1!ChrisRange" and format it.
foreach (Excel.Name n in ws.Names)
{
Console.WriteLine(n.Name);
Excel.Range r = n.RefersToRange;
r.AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
}
}
}
private void Listing_2_14()
{
Excel.Range rng = Globals.Sheet1.Range["a1", "g12"] as Excel.Range;
rng.AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormat3DEffects2, true, false, true, false, true, true);
}
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
try
{
MovieCatalogBL context = new MovieCatalogBL();
List <Movie> movieList = context.GetMovies().ToList();
if (movieList.Count > 0)
{
string path = Server.MapPath("~/exportedfiles/");
if (!Directory.Exists(path)) // CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER.
{
Directory.CreateDirectory(path);
}
// "File.Delete" method does not throw an exception when a file doesn't exist.
File.Delete(path + "Movies.xls"); // DELETE THE FILE BEFORE CREATING A NEW ONE.
// ADD A WORKBOOK USING THE EXCEL APPLICATION.
Excel.Application xlAppToExport = new Excel.Application();
xlAppToExport.Workbooks.Add("");
// ADD A WORKSHEET.
Excel.Worksheet xlWorkSheetToExport = default(Excel.Worksheet);
xlWorkSheetToExport = (Excel.Worksheet)xlAppToExport.Sheets["Sheet1"];
// ROW ID FROM WHERE THE DATA STARTS SHOWING.
int iRowCnt = 4;
// SHOW THE HEADER.
xlWorkSheetToExport.Cells[1, 1] = "Movies list.";
Excel.Range range = xlWorkSheetToExport.Cells[1, 1] as Excel.Range;
range.EntireRow.Font.Name = "Arial";
range.EntireRow.Font.Bold = true;
range.EntireRow.Font.Size = 20;
// Depends how long the title is
xlWorkSheetToExport.Range["A1:F1"].MergeCells = true; // MERGE CELLS OF THE HEADER.
// SHOW COLUMNS ON THE TOP.
xlWorkSheetToExport.Cells[iRowCnt - 1, 1] = "Title";
xlWorkSheetToExport.Cells[iRowCnt - 1, 2] = "Country";
xlWorkSheetToExport.Cells[iRowCnt - 1, 3] = "Genre";
xlWorkSheetToExport.Cells[iRowCnt - 1, 4] = "Year Of Production";
xlWorkSheetToExport.Cells[iRowCnt - 1, 5] = "Content Provider";
xlWorkSheetToExport.Cells[iRowCnt - 1, 6] = "Duration";
xlWorkSheetToExport.Cells[iRowCnt - 1, 7] = "IPTV Rights";
xlWorkSheetToExport.Cells[iRowCnt - 1, 8] = "VOD Rights";
xlWorkSheetToExport.Cells[iRowCnt - 1, 9] = "SVOD Rights";
xlWorkSheetToExport.Cells[iRowCnt - 1, 10] = "Ancillary Rights";
xlWorkSheetToExport.Cells[iRowCnt - 1, 11] = "Start Date";
xlWorkSheetToExport.Cells[iRowCnt - 1, 12] = "Expiry Date";
xlWorkSheetToExport.Cells[iRowCnt - 1, 13] = "Comment";
foreach (var item in movieList)
{
xlWorkSheetToExport.Cells[iRowCnt, 1] = item.OriginalName;
xlWorkSheetToExport.Cells[iRowCnt, 2] = item.Country;
xlWorkSheetToExport.Cells[iRowCnt, 3] = item.Genre;
xlWorkSheetToExport.Cells[iRowCnt, 4] = item.Year;
xlWorkSheetToExport.Cells[iRowCnt, 5] = item.ContentProvider;
// Duration has to be converted to string (Duration.ToString()), otherwise data can not be exported
xlWorkSheetToExport.Cells[iRowCnt, 6] = item.Duration.ToString();
xlWorkSheetToExport.Cells[iRowCnt, 7] = item.RightsIPTV;
xlWorkSheetToExport.Cells[iRowCnt, 8] = item.RightsVOD;
xlWorkSheetToExport.Cells[iRowCnt, 9] = item.SVODRights;
xlWorkSheetToExport.Cells[iRowCnt, 10] = item.AncillaryRights;
xlWorkSheetToExport.Cells[iRowCnt, 11] = item.StartDate;
xlWorkSheetToExport.Cells[iRowCnt, 12] = item.ExpireDate;
xlWorkSheetToExport.Cells[iRowCnt, 13] = item.Comment;
iRowCnt = iRowCnt + 1;
}
// FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION.
Excel.Range range1 = xlAppToExport.ActiveCell.Worksheet.Cells[4, 1] as Excel.Range;
range1.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatList3);
// Set width of Column
xlWorkSheetToExport.Columns[2].ColumnWidth = 25;
xlWorkSheetToExport.Columns[7].ColumnWidth = 25;
xlWorkSheetToExport.Columns[8].ColumnWidth = 25;
// SAVE THE FILE IN A FOLDER. (XLS or XLSX format)
xlWorkSheetToExport.SaveAs(path + "Movies.xls");
// xlWorkSheetToExport.SaveAs(path + "Movies.xlsx");
// CLEAR.
xlAppToExport.Workbooks.Close();
xlAppToExport.Quit();
xlAppToExport = null;
xlWorkSheetToExport = null;
lblMessage.Text = "Data Exported.";
lblMessage.Attributes.Add("style", "color:green; font: bold 14px/16px Sans-Serif,Arial");
}
}
catch (IOException)
{
lblMessage.Text = "There was an error while exporting data. Check if file is in use by another application (MS Excel).";
lblMessage.Attributes.Add("style", "color:red; font: bold 14px/16px Sans-Serif,Arial");
}
catch (Exception)
{
lblMessage.Text = "There was an error while exporting data. Try again.";
lblMessage.Attributes.Add("style", "color:red; font: bold 14px/16px Sans-Serif,Arial");
}
}
protected void ExportToExcel(object sender, EventArgs e)
{
// SET THE CONNECTION STRING.
string sCon = "Data Source=DESKTOP-1TMBC4T;Persist Security Info=False;Integrated Security=SSPI;" +
"Initial Catalog=DEV;User Id=sa;Password=sa;Connect Timeout=30;";
using (SqlConnection con = new SqlConnection(sCon))
{
using (SqlCommand cmd = new SqlCommand("SELECT *FROM dbo.EmployeeDetails"))
{
SqlDataAdapter sda = new SqlDataAdapter();
try
{
cmd.Connection = con;
con.Open();
sda.SelectCommand = cmd;
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
string path = Server.MapPath("exportedfiles\\");
if (!Directory.Exists(path)) // CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER.
{
Directory.CreateDirectory(path);
}
File.Delete(path + "EmployeeDetails.xlsx"); // DELETE THE FILE BEFORE CREATING A NEW ONE.
// ADD A WORKBOOK USING THE EXCEL APPLICATION.
Excel.Application xlAppToExport = new Excel.Application();
xlAppToExport.Workbooks.Add("");
// ADD A WORKSHEET.
Excel.Worksheet xlWorkSheetToExport = default(Excel.Worksheet);
xlWorkSheetToExport = (Excel.Worksheet)xlAppToExport.Sheets["Sheet1"];
// ROW ID FROM WHERE THE DATA STARTS SHOWING.
int iRowCnt = 4;
// SHOW THE HEADER.
xlWorkSheetToExport.Cells[1, 1] = "Employee Details";
Excel.Range range = xlWorkSheetToExport.Cells[1, 1] as Excel.Range;
range.EntireRow.Font.Name = "Calibri";
range.EntireRow.Font.Bold = true;
range.EntireRow.Font.Size = 20;
xlWorkSheetToExport.Range["A1:D1"].MergeCells = true; // MERGE CELLS OF THE HEADER.
// SHOW COLUMNS ON THE TOP.
xlWorkSheetToExport.Cells[iRowCnt - 1, 1] = "Employee Name";
xlWorkSheetToExport.Cells[iRowCnt - 1, 2] = "Mobile No.";
xlWorkSheetToExport.Cells[iRowCnt - 1, 3] = "PresentAddress";
xlWorkSheetToExport.Cells[iRowCnt - 1, 4] = "Email Address";
int i;
for (i = 0; i <= dt.Rows.Count - 1; i++)
{
xlWorkSheetToExport.Cells[iRowCnt, 1] = dt.Rows[i].Field <string>("EmpName");
xlWorkSheetToExport.Cells[iRowCnt, 2] = dt.Rows[i].Field <string>("Mobile");
xlWorkSheetToExport.Cells[iRowCnt, 3] = dt.Rows[i].Field <string>("PresentAddress");
xlWorkSheetToExport.Cells[iRowCnt, 4] = dt.Rows[i].Field <string>("Email");
iRowCnt = iRowCnt + 1;
}
// FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION.
Excel.Range range1 = xlAppToExport.ActiveCell.Worksheet.Cells[4, 1] as Excel.Range;
range1.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatList3);
// SAVE THE FILE IN A FOLDER.
xlWorkSheetToExport.SaveAs(path + "EmployeeDetails.xlsx");
// CLEAR.
xlAppToExport.Workbooks.Close();
xlAppToExport.Quit();
xlAppToExport = null;
xlWorkSheetToExport = null;
lblConfirm.Text = "Data Exported Successfully";
lblConfirm.Attributes.Add("style", "color:green; font: normal 14px Verdana;");
btView.Attributes.Add("style", "display:block");
btDownLoadFile.Attributes.Add("style", "display:block");
}
}
catch (Exception ex)
{
lblConfirm.Text = ex.Message.ToString();
lblConfirm.Attributes.Add("style", "color:red; font: bold 14px/16px Sans-Serif,Arial");
}
finally
{
sda.Dispose();
sda = null;
}
}
}
}
