private void AllBorders(Microsoft.Office.Interop.Excel.Borders _borders)
{
_borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
_borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
_borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
_borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
_borders.Color = Color.Black;
}
public static void createExcel(DataTable dataTable)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook workBook;
Microsoft.Office.Interop.Excel.Worksheet workSheet;
Microsoft.Office.Interop.Excel.Range cellRange;
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
workBook = excel.Workbooks.Add(Type.Missing);
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
workSheet.Name = "UserDetails";
int rowcount = 1;
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
if (rowcount == 2)
{
workSheet.Cells[1, i] = dataTable.Columns[i - 1].ColumnName;
workSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
workSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
}
}
// use this for styling
cellRange = workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[rowcount, dataTable.Columns.Count]];
cellRange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = cellRange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
workBook.SaveAs(ConfigurationManager.AppSettings["ExcelSaveLocation"]);
workBook.Close();
excel.Quit();
Console.WriteLine("Successfully Create Excel File");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
workSheet = null;
cellRange = null;
workBook = null;
}
}
/// <summary>
/// The CreateSpredsheetWorkBook
/// </summary>
/// <param name="filePath">The filePath<see cref="string"/></param>
public void CreateSpredsheetWorkBook()
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook workBook;
Microsoft.Office.Interop.Excel.Worksheet workSheet;
Microsoft.Office.Interop.Excel.Range cellRange;
excel = new Microsoft.Office.Interop.Excel.Application();
workBook = excel.Workbooks.Add(Type.Missing);
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
workSheet.Name = "Selected table | FTP Assistant";
int rowCount = 2;
foreach (DataRow datarow in ExportToExcel().Rows)
{
rowCount += 1;
for (int i = 1; i <= ExportToExcel().Columns.Count; i++)
{
if (rowCount == 3)
{
workSheet.Cells[2, i] = ExportToExcel().Columns[i - 1].ColumnName;
workSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
workSheet.Cells[rowCount, i] = datarow[i - 1].ToString();
if (rowCount > 3)
{
if (i == ExportToExcel().Columns.Count)
{
if (rowCount % 2 == 0)
{
cellRange = workSheet.Range[workSheet.Cells[rowCount, 1], workSheet.Cells[rowCount, ExportToExcel().Columns.Count]];
}
}
}
}
}
cellRange = workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[rowCount, ExportToExcel().Columns.Count]];
cellRange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = cellRange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
cellRange = workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[2, ExportToExcel().Columns.Count]];
//Directory.CreateDirectory(@"C:\Desktop\");
workBook.SaveAs("Exported users from FTP", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
workBook.Close();
excel.Quit();
}
private void BorderAround(Microsoft.Office.Interop.Excel.Range range, int colour)
{
Microsoft.Office.Interop.Excel.Borders borders = range.Borders;
borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
borders.Color = colour;
borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
borders = null;
}
public void GenerateReport()
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
object misValue = System.Reflection.Missing.Value;
DataTable empTable = new DataTable();
using (SqlConnection con = new SqlConnection(connectStr))
{
con.Open();
string qry = "SELECT * FROM EMPLOYEE";
SqlCommand cmd = new SqlCommand(qry, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(empTable);
}
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(misValue);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = "Test work sheet";
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[empTable.Rows.Count, empTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
for (int i = 0; i <= empTable.Rows.Count - 1; i++)
{
for (int j = 0; j <= empTable.Columns.Count - 1; j++)
{
string data = empTable.Rows[i].ItemArray[j].ToString();
excelSheet.Cells[i + 1, j + 1] = data;
}
}
excelSheet.SaveAs("e:\\informations.csv", Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue);
excelworkBook.Close(true, misValue, misValue);
excel.Quit();
releaseObject(excelSheet);
releaseObject(excelworkBook);
releaseObject(excel);
}
private void bt_baocaoNC_Click(object sender, EventArgs e)
{
string conString = @"Data Source=DESKTOP-RGHAECC;Initial Catalog=SucKhoeNhanVien_PNT;Integrated Security=True";
SqlConnection sqlCon = new SqlConnection(conString);
sqlCon.Open();
SqlDataAdapter da = new SqlDataAdapter("SELECT NghienCuu.Id_NghienCuu, NghienCuu.MaSo,NghienCuu.TenNghienCuu,CapNghienCuu.CapNghienCuu,TrangThaiNghienCuu.TenTrangThai,NghienCuu.ChuNhiemDeTai,NghienCuu.NhaTaiTro,NghienCuu.Follow,NghienCuu.NguoiPhuTrach,NghienCuu.Tg_ThucHien,NghienCuu.CoMauChungVN,NghienCuu.CoMauChungPNT,NghienCuu.NgayKhoiDongSite,NghienCuu.ThoiGianKTTDung,NghienCuu.TienDoThuDung,NghienCuu.KinhPhi,NghienCuu.NguonKP,NghienCuu.NgayDuyet,NghienCuu.SoQD from NghienCuu,CapNghienCuu,TrangThaiNghienCuu where NghienCuu.Id_CapNghienCuu = CapNghienCuu.Id_CapNghienCuu And NghienCuu.Id_TrangThaiNghienCuu = TrangThaiNghienCuu.Id_TrangThai", sqlCon);
System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
da.Fill(dtMainSQLData);
DataColumnCollection dcCollection = dtMainSQLData.Columns;
// Export Data into EXCEL Sheet
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Application.Workbooks.Add(Type.Missing);
ExcelApp.Columns.ColumnWidth = 20;
// ExcelApp.Cells.CopyFromRecordset(objRS);
for (int i = 1; i < dtMainSQLData.Rows.Count + 2; i++)
{
for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
{
if (i == 1)
{
ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
ExcelApp.Cells[i, j].Font.Bold = true;
ExcelApp.Cells[i, j].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
Microsoft.Office.Interop.Excel.Range formatRange;
Microsoft.Office.Interop.Excel.Borders border = ExcelApp.Cells[i, j].Borders;
formatRange = ExcelApp.Cells[i, j];
formatRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.CornflowerBlue);
}
else
{
ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString();
}
ExcelApp.Cells[i, j].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
ExcelApp.Cells[i, j].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
ExcelApp.Cells[i, j].Style.Wraptext = true;
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs("C:\\Users\\Oanh\\Desktop\\BaoCaoNghienCuu.xls");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
MessageBox.Show("Done");
}
public FileClass WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string ReporType)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
FileClass file = new FileClass();
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
excelSheet.Cells[1, 1] = ReporType;
excelSheet.Cells[1, 2] = "Print Date : " + DateTime.Now.ToShortDateString();
// loop through each row and add values to our sheet
int rowcount = 2;
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 3)
{
excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (rowcount > 3)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#00005e", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
//MemoryStream ms = new MemoryStream();
//excelworkBook.SaveAs(ms, excelworkBook.FileFormat);
//file.FileFormat = excelworkBook.FileFormat.ToString();
//byte[] data = null;
//data = ms.ToArray();
//file.FileContent = data;
//excelworkBook.Close();
/////////////////////
//string tempPath = AppDomain.CurrentDomain.BaseDirectory + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + DateTime.Now.Millisecond + "_temp";//date time added to be sure there are no name conflicts
string tempPath = AppDomain.CurrentDomain.BaseDirectory + "_temp"; //date time added to be sure there are no name conflicts
excelworkBook.SaveAs(tempPath, excelworkBook.FileFormat); //create temporary file from the workbook
tempPath = excelworkBook.FullName; //name of the file with path and extension
file.FileFormat = excelworkBook.FileFormat.ToString();
excelworkBook.Close();
excel.Quit();
DirectoryInfo dInfo = new DirectoryInfo(tempPath);
DirectorySecurity dSecurity = dInfo.GetAccessControl();
dSecurity.AddAccessRule(new FileSystemAccessRule(new SecurityIdentifier(WellKnownSidType.WorldSid, null), FileSystemRights.FullControl, InheritanceFlags.ObjectInherit | InheritanceFlags.ContainerInherit, PropagationFlags.NoPropagateInherit, AccessControlType.Allow));
dInfo.SetAccessControl(dSecurity);
byte[] result = File.ReadAllBytes(tempPath);//change to byte[]
file.FileContent = result;
File.Delete(tempPath);//delete temporary file
}
catch (Exception ex)
{
string mess = ex.Message.ToString();
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
return(file);
}
/// <summary>
/// FUNCTION FOR EXPORT TO EXCEL
/// </summary>2
/// <param name="dataTable"></param>
/// <param name="worksheetName"></param>
/// <param name="saveAsLocation"></param>
/// <returns></returns>
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
//excelSheet.Cells[1, 1] = ReporType;
//excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();
// loop through each row and add values to our sheet
int rowcount = 1;
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 2)
{
excelSheet.Cells[1, i] = dataTable.Columns[i - 1].ColumnName;
// excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (rowcount > 2)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
// FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black,false);
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
//FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
excelworkBook.SaveAs(saveAsLocation);;
excelworkBook.Close();
excel.Quit();
//System.Windows.Forms.DialogResult DGres = System.Windows.Forms.MessageBox.Show("Excel generated succesfully." + Environment.NewLine + "Do you want us to open the application? ", "Excel Report", System.Windows.Forms.MessageBoxButtons.YesNo);
//if (DGres == System.Windows.Forms.DialogResult.Yes)
//{
// Process.Start(@saveAsLocation);
//}
return(true);
}
catch (Exception ex)
{
MessageBox.Show("Excel Report generation failed : " + ex.Message.ToString());
return(false);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
/// <summary>
/// FUNCTION FOR EXPORT TO EXCEL
/// </summary>
/// <param name="dataTable"></param>
/// <param name="worksheetName"></param>
/// <param name="saveAsLocation"></param>
/// <returns></returns>
public bool WriteDataTableToExcel(DataTable dataTable1, DataTable dataTable2, string worksheetName, string saveAsLocation, string ReporType, string progtype, string user)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = true;
excel.DisplayAlerts = true;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
excelSheet.Cells[1, 1] = "Report Name - " + ReporType;
excelSheet.Cells[2, 1] = "Date of Report Generation : " + General_methods.get_current_date();
excelSheet.Cells[3, 1] = "Time of Report Generation :" + General_methods.get_current_time();
excelSheet.Cells[4, 1] = "Report Created By :" + user;
excelSheet.Cells[5, 1] = "Program Type :" + progtype;
excelSheet.Cells[6, 1] = "Company Participants";
// loop through each row and add values to our sheet
int rowcount = 7;
foreach (DataRow datarow in dataTable1.Rows)
{
//adding one to rowcount
rowcount += 1;
for (int i = 1; i <= dataTable1.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 8)
{
excelSheet.Cells[7, i] = dataTable1.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
excelSheet.Cells[7, i].Font.Bold = true;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (rowcount > 8)
{
if (i == dataTable1.Columns.Count)
{
if (rowcount % 4 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable1.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable1.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
int newrowcount = rowcount + 5;
int y = newrowcount;
excelSheet.Cells[newrowcount - 1, 1] = "Individual Participants";
int x = newrowcount;
//newrowcount =2;
foreach (DataRow datarow in dataTable2.Rows)
{
//adding one to rowcount
newrowcount += 1;
for (int i = 1; i <= dataTable2.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (newrowcount == x + 1)
{
excelSheet.Cells[x, i] = dataTable2.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
excelSheet.Cells[x, i].Font.Bold = true;
}
excelSheet.Cells[newrowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (newrowcount > x + 1)
{
if (i == dataTable2.Columns.Count)
{
if (newrowcount % 4 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[newrowcount, 1], excelSheet.Cells[newrowcount, dataTable2.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
}
excelCellrange = excelSheet.Range[excelSheet.Cells[x, 1], excelSheet.Cells[newrowcount, dataTable2.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border2 = excelCellrange.Borders;
border2.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border2.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[5, dataTable1.Columns.Count]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
excelSheet.Cells[1, 1].Font.Size = 20;
excelSheet.Cells[6, 1].Font.Color = System.Drawing.Color.Red;
excelSheet.Cells[6, 1].Font.Bold = true;
excelSheet.Cells[y - 1, 1].Font.Color = System.Drawing.Color.Red;
excelSheet.Cells[y - 1, 1].Font.Bold = true;
Microsoft.Office.Interop.Excel.Worksheet newWorksheet;
newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet();
return(true);
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
return(false);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
private void btnExcel_Click(object sender, EventArgs e)
{
string resultFilePath = AppDomain.CurrentDomain.BaseDirectory + "RESULT";
var connectionString = @"Server=(Local)\SQLExpress;Database=Vinod;Integrated Security=true";
List <string> lstRollNumberRecords = new List <string>();
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
string query = "select Roll from Sheet1$ group by Roll order by Roll";
SqlCommand cmd = new SqlCommand(query, connection);
SqlDataAdapter sqlDataReader = new SqlDataAdapter(cmd);
DataTable tblGetData = new DataTable();
sqlDataReader.Fill(tblGetData);
connection.Close();
foreach (DataRow row in tblGetData.Rows)
{
lstRollNumberRecords.Add(row["Roll"].ToString());
}
//Create dictionay
Dictionary <string, List <string> > dicRowData = new Dictionary <string, List <string> >();
foreach (var item in lstRollNumberRecords)
{
string qq = "select distinct CLASS from Sheet1$ where ROLL = '" + item.ToString() + "'";
SqlConnection con = new SqlConnection(connectionString);
con.Open();
SqlCommand cmdd = new SqlCommand(qq, con);
SqlDataAdapter sqlDataReaderr = new SqlDataAdapter(cmdd);
DataTable tblGetDataaa = new DataTable();
sqlDataReaderr.Fill(tblGetDataaa);
connection.Close();
List <string> classRecod = new List <string>();
foreach (DataRow itemRow in tblGetDataaa.Rows)
{
classRecod.Add(itemRow["CLASS"].ToString());
}
dicRowData.Add(item.ToString(), classRecod);
}
DataSet ds = new DataSet();
foreach (var item in dicRowData)
{
string folderName = item.Key.ToString();
List <string> folderValue = item.Value.ToList();
string NewFolder = resultFilePath + "\\" + folderName;
//string fileNAme = "";
if (!Directory.Exists(NewFolder))
{
Directory.CreateDirectory(NewFolder);
}
foreach (var folderValues in folderValue)
{
if (folderName.Equals("DL01"))
{
continue;
}
else
{
if (folderValues.Equals("#N/A"))
{
continue;
}
else
{
using (FileStream fs = File.Create(NewFolder + "\\" + folderValues + ".xlsx"))
{
}
string qry = "select [ROLL NUMBER],Name,SCORE,GRADE from Sheet1$ where ROLL = '" + folderName + "' and CLASS='" + folderValues + "' order by SCORE desc";
SqlConnection con = new SqlConnection(connectionString);
con.Open();
SqlCommand cmmd = new SqlCommand(qry, con);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmmd);
DataTable tblData = new DataTable();
tblData.TableName = item.ToString();
sqlDataAdapter.Fill(tblData);
con.Close();
//Create Excel File
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
worKbooK = excel.Workbooks.Add(Type.Missing);
worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet;
worKsheeT.Name = folderValues.ToString();
//worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 8]].Merge();
//worKsheeT.Cells[1, 1] = "Student Report Card";
// worKsheeT.Cells.Font.Size = 12;
//Bold the First Row
celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 4]];
celLrangE.EntireRow.Font.Bold = true;//.AutoFit();
int rowcount = 1;
foreach (DataRow datarow in tblData.Rows)
{
rowcount += 1;
for (int i = 1; i <= tblData.Columns.Count; i++)
{
if (rowcount == 2)
{
worKsheeT.Cells[1, i] = tblData.Columns[i - 1].ColumnName;
// worKsheeT.Cells.Font.Color = System.Drawing.Color.Red;
//worKsheeT.get_Range(1, i).Font.Bold = true;
}
worKsheeT.Cells[rowcount, i] = datarow[i - 1].ToString();
if (rowcount > 3)
{
if (i == tblData.Columns.Count)
{
if (rowcount % 2 == 0)
{
celLrangE = worKsheeT.Range[worKsheeT.Cells[rowcount, 1], worKsheeT.Cells[rowcount, tblData.Columns.Count]];
}
}
}
}
}
celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[rowcount, tblData.Columns.Count]];
celLrangE.EntireColumn.AutoFit();
celLrangE.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
worKbooK.SaveAs(NewFolder + "\\" + folderValues + ".xlsx");;
worKbooK.Close();
excel.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
worKsheeT = null;
celLrangE = null;
worKbooK = null;
}
//End Excel File
// tblData.WriteXml(NewFolder + "\\" + folderValues + ".xls");
}
}
}
}
}
/// <summary>
/// FUNCTION FOR EXPORT TO EXCEL
/// </summary>
/// <param name="dataTable"></param>
/// <param name="worksheetName"></param>
/// <param name="saveAsLocation"></param>
/// <returns></returns>
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType, string progtype, string user, int No_of_individual_participants, string type = "program", string coursno = "null", string type2 = null)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = true;
excel.DisplayAlerts = true;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
excelSheet.Cells[1, 1] = "Report Name - " + ReporType;
excelSheet.Cells[2, 1] = "Date of Report Generation : " + General_methods.get_current_date();
excelSheet.Cells[3, 1] = "Time of Report Generation :" + General_methods.get_current_time();
excelSheet.Cells[4, 1] = "Report Created By :" + user;
if (type == "program")
{
excelSheet.Cells[5, 1] = "Program Type :" + progtype;
}
else if (type == "diploma")
{
excelSheet.Cells[5, 1] = "Course name :" + General_methods.get_course_name_from_course_no(coursno);
}
else if (type == "diploma-all batches")
{
excelSheet.Cells[5, 1] = "All Batches";
}
// loop through each row and add values to our sheet
int rowcount = 6;
foreach (DataRow datarow in dataTable.Rows)
{
//adding one to rowcount
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 7)
{
excelSheet.Cells[6, i] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
excelSheet.Cells[6, i].Font.Bold = true;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (rowcount > 7)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 4 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
}
if (type2 == null)
{
excelSheet.Cells[rowcount + 1, 1] = "Number of Individual Participants - ";
excelSheet.Cells[rowcount + 1, 2] = No_of_individual_participants;
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount + 1, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[5, dataTable.Columns.Count + 12]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
excelSheet.Cells[1, 1].Font.Size = 20;
Microsoft.Office.Interop.Excel.Range chartRange;
Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;
chartRange = excelSheet.Range[excelSheet.Cells[7, 1], excelSheet.Cells[rowcount + 1, dataTable.Columns.Count]];
chartPage.SetSourceData(chartRange);
chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
public bool WriteDataTableToExcel(DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
excelSheet.Cells[1, 1] = ReporType;
excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();
// loop through each row and add values to our sheet
int rowcount = 2;
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 3)
{
excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (rowcount > 3)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
int count = 1;
string fileNameOnly = Path.GetFileNameWithoutExtension(saveAsLocation);
string extension = Path.GetExtension(saveAsLocation);
string path = Path.GetDirectoryName(saveAsLocation);
string newFullPath = saveAsLocation;
while (File.Exists(newFullPath))
{
string tempFileName = string.Format("{0}({1})", fileNameOnly, count++);
newFullPath = Path.Combine(path, tempFileName + extension);
}
excelworkBook.SaveAs(newFullPath);;
excelworkBook.Close();
excel.Quit();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
/// <summary>
/// FUNCTION FOR EXPORTING TO EXCEL
/// </summary>
/// <param name="minutesworked"></param>
/// <param name="worksheetName"></param>
/// <param name="saveAsLocation"></param>
/// <returns></returns>
public bool WriteDataTableToExcel(List <int> minutesworked, string worksheetName, string saveAsLocation, int eid, string ename, int Week, DateTime firstdate, int smins)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Start Excel and get the Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// make the Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Work sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
excelSheet.Cells[1, 1] = "Shop ID: 429";
excelSheet.Cells[1, 2] = "Employee ID: " + eid.ToString();
excelSheet.Cells[1, 3] = ename;
excelSheet.Cells[2, 1] = "Week: " + Week.ToString();
excelSheet.Cells[2, 2] = "Year: " + firstdate.Date.ToString("yyyy");
// loop through each row and add values to our sheet
int rowcount = 3, daycount = 0;
foreach (int mins in minutesworked)
{
excelSheet.Cells[rowcount, 1] = firstdate.AddDays(daycount).DayOfWeek.ToString();
excelSheet.Cells[rowcount, 2] = firstdate.AddDays(daycount).ToString("yyyy-MM-dd");
excelSheet.Cells[rowcount, 3] = (mins / 60).ToString() + ":" + (mins % 60).ToString();
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, 3]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
daycount++;
rowcount++;
}
excelSheet.Cells[rowcount, 1] = "Sick Leave Hours:";
excelSheet.Cells[rowcount, 2] = "All week";
excelSheet.Cells[rowcount, 3] = (smins / 60).ToString() + ":" + (smins % 60).ToString();
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, 3]];
FormattingExcelCells(excelCellrange, "#FF3399", System.Drawing.Color.White, false);
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, 3]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, 3]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
excelworkBook.SaveAs(saveAsLocation + "-" + eid.ToString() + "-" + firstdate.Date.ToString("yyyy") + "-" + Week.ToString() + ".xlsx");
excelworkBook.Close();
excel.Quit();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelWorkbook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellRange;
try
{
//Start Excel and get the Application Object
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel Visible
excel.Visible = true;
excel.DisplayAlerts = false;
excelWorkbook = excel.Workbooks.Add(Type.Missing);
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.ActiveSheet;
excelSheet.Name = worksheetName;
excelSheet.Cells[1, 1] = ReporType;
excelSheet.Cells[1, 2] = "Date: " + DateTime.Now.ToShortDateString();
// loop through each row and add values to our sheet
int rowCount = 2;
foreach (DataRow datarow in dataTable.Rows)
{
rowCount++;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
//on the first iteration we add the colum headers
if (rowCount == 3)
{
excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelSheet.Cells[rowCount, i] = datarow[i - 1].ToString();
//for all other rows
if (rowCount > 3)
{
if (i == dataTable.Columns.Count)
{
excelCellRange = excelSheet.Range[excelSheet.Cells[rowCount, 1], excelSheet.Cells[rowCount, dataTable.Columns.Count]];
FormattingExcelCells(excelCellRange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
// resizing the columns
excelCellRange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowCount, dataTable.Columns.Count]];
excelCellRange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellRange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excelCellRange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
FormattingExcelCells(excelCellRange, "#000099", System.Drawing.Color.White, true);
//save the workbook and exit Excel
return(true);
}
catch (Exception ex)
{
MessageBox.Show("Error: Could not convert dataTable to an Excel File. Please make sure that there is something in the DataTable. \nOriginal error " + ex.Message);
return(false);
}
finally
{
excelSheet = null;
excelCellRange = null;
excelWorkbook = null;
}
}
//Exporting the data from Datatable to Excel
public static void ExportDataToExcel(DataTable dtWorklist)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook worKbooK;
Microsoft.Office.Interop.Excel.Worksheet worKsheeT;
Microsoft.Office.Interop.Excel.Range celLrangE;
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
worKbooK = excel.Workbooks.Add(Type.Missing);
worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet;
worKsheeT.Name = "Result";
worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 8]].Merge();
worKsheeT.Cells[1, 1] = "Cosolidated compared data between Azure Devops and TimeSheet Data";
worKsheeT.Cells.Font.Size = 10;
int rowcount = 2;
foreach (DataRow datarow in dtWorklist.Rows)
{
rowcount += 1;
for (int i = 1; i <= dtWorklist.Columns.Count; i++)
{
if (rowcount == 3)
{
worKsheeT.Cells[2, i] = dtWorklist.Columns[i - 1].ColumnName;
worKsheeT.Cells.Font.Color = System.Drawing.Color.Black;
}
worKsheeT.Cells[rowcount, i] = datarow[i - 1].ToString();
if (rowcount > 3)
{
if (i == dtWorklist.Columns.Count)
{
if (rowcount % 2 == 0)
{
celLrangE = worKsheeT.Range[worKsheeT.Cells[rowcount, 1], worKsheeT.Cells[rowcount, dtWorklist.Columns.Count]];
}
}
}
}
}
celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[rowcount, dtWorklist.Columns.Count]];
celLrangE.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[2, dtWorklist.Columns.Count]];
if (!Directory.Exists(path + "\\Reports"))
{
Directory.CreateDirectory(path + "\\Reports");
}
string reportPath = path + "\\Reports\\UnisysReport-" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
Console.WriteLine($"Writing result to {reportPath}");
worKbooK.SaveAs(reportPath);
worKbooK.Close();
excel.Quit();
}
catch (Exception ex)
{
Console.Write(ex.Message);
WriteFileToDisk("ExportDataToExcel", ex.Message + Environment.NewLine + ex.StackTrace);
Console.ReadLine();
}
}
public void exportToExcel(DataGridView dataGridView)
{
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
excel.Visible = false;
worksheet = workbook.ActiveSheet;
worksheet.Name = "ExportedFromDatGrid";
Microsoft.Office.Interop.Excel.Range formatRange = worksheet.UsedRange;
for (int i = 0; i < dataGridView.Columns.Count; i++)
{
if (dataGridView.Columns[i].Visible)
{
worksheet.Cells[1, i + 1] = dataGridView.Columns[i].HeaderText;
if (dataGridView.Columns[i].HeaderText.Contains("Note"))
{
Microsoft.Office.Interop.Excel.Range noteHeader = worksheet.get_Range("D1", "D1");
noteHeader.ColumnWidth = 20.0;
}
else if (dataGridView.Columns[i].HeaderText.Equals("Paid"))
{
Microsoft.Office.Interop.Excel.Range paidHeader = worksheet.get_Range("H1", "H1");
paidHeader.ColumnWidth = 7.0;
}
else
{
worksheet.Columns.AutoFit();
}
worksheet.Cells[1, i + 1].Font.Bold = true;
worksheet.Cells[1, i + 1].Font.Size = 12;
Microsoft.Office.Interop.Excel.Range cell = formatRange.Cells[1, i + 1];
Microsoft.Office.Interop.Excel.Borders border = cell.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
}
}
for (int i = 0; i < dataGridView.Rows.Count; i++)
{
for (int j = 0; j < dataGridView.Columns.Count; j++)
{
if (dataGridView.Columns[i].Visible)
{
if (dataGridView.Rows[i].Cells[j].Displayed)
{
if (dataGridView.Rows[i].Cells[j].Value != null)
{
worksheet.Cells[i + 2, j + 1] = dataGridView.Rows[i].Cells[j].Value.ToString();
formatRange.Font.Size = 10;
}
else
{
worksheet.Cells[i + 2, j + 1] = "";
}
}
}
}
}
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveFileDialog.FilterIndex = 2;
if (saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveFileDialog.FileName);
MessageBox.Show("Export Successful", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
} catch (Exception e)
{
MessageBox.Show(e.Message);
} finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
public bool WriteDataTableToExcelTwo(System.Data.DataTable dataTable)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = true;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = "Risk Management";
excelSheet.Cells[1, 2] = "Report";
excelSheet.Cells[1, 3] = "Date : " + DateTime.Now.ToShortDateString();
excelSheet.Cells[2, 2] = "Activity";
excelSheet.Cells[2, 3] = "Cost Risk Factor";
excelSheet.Cells[2, 4] = "Time Risk Factor";
excelSheet.Cells[2, 5] = "Proposed Mitigation";
// loop through each row and add values to our sheet
int rowcount = 2;
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 3)
{
excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (rowcount > 3)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
var excelCellrangee = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, 1]];
excelCellrangee.EntireColumn.Delete();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
private void btnReporte_Click(object sender, EventArgs e)
{
try
{
BD metodos = new BD();
BD.ObtenerConexion();
tableProductosFiltro = metodos.consultarProductoDetalleReporte();
BD.CerrarConexion();
/*
* Productos.id_producto AS ID, Productos.modelo AS MODELO, Tamanos.tamano AS TAMAÑO,
* Material.nombre AS MATERIAL, Categoria.nombre AS CATEGORIA, Productos.cantidad AS CANTIDAD,
* Tipo.nombre AS TIPO, Productos.precio_publico AS PRECIO_PUBLICO, Productos.precio_frecuente AS PRECIO_FRECUENTE,
* Productos.precio_mayorista AS PRECIO_MAYORISTA, Tamanos.descripcion AS DESCRIPCION, Productos.peso AS PESO
*/
for (int i = 0; i < tableProductosFiltro.Rows.Count; i++)
{
for (int j = 0; j < tableProductosFiltro.Columns.Count; j++)
{
Console.Write(tableProductosFiltro.Rows[i][j]);
}
Console.WriteLine("");
}
}
catch
{
txtGenerando.Text = "Error de conexión ...";
}
if (tableProductosFiltro.Rows.Count != 0)
{
try
{
txtGenerando.Text = "GENERANDO DOCUMENTO ...";
Cursor.Current = Cursors.WaitCursor;
SaveFileDialog fichero = new SaveFileDialog();
fichero.FileName = "reporte_inventario_" + Inicio.fecha;
fichero.Filter = "Excel (*.xls)|*.xls";
if (fichero.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application aplicacion;
Microsoft.Office.Interop.Excel.Workbook libros_trabajo;
Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo;
Microsoft.Office.Interop.Excel.Range inicio;
Microsoft.Office.Interop.Excel.Range ultimo;
aplicacion = new Microsoft.Office.Interop.Excel.Application();
libros_trabajo = aplicacion.Workbooks.Add();
hoja_trabajo = (Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);
hoja_trabajo.Range["B5:M5"].Font.Bold = true;
//hoja_trabajo.Cells[2,8] = "Reporteador";
hoja_trabajo.Cells[6, 3] = "Fecha del reporte: " + Inicio.fecha;
aplicacion.get_Range("H2:M4").Merge(true);
Microsoft.Office.Interop.Excel.Range titulo = hoja_trabajo.get_Range("H2:M4");
titulo.Merge();
titulo.Value = "INVENTARIO";
//titulo.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
//titulo.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
titulo.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
titulo.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
titulo.Font.Size = 36;
titulo.BorderAround2(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium);
titulo.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbGreen;
titulo.Font.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbWhite;
///////////////////////////////
//
aplicacion.get_Range("H7:M10").Merge(true);
Microsoft.Office.Interop.Excel.Range nombre = hoja_trabajo.get_Range("H7:M10");
nombre.Merge();
nombre.Value = "BASES Y MOLDURAS";
nombre.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
nombre.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
nombre.Font.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbGreen;
nombre.Font.Size = 30;
////////////////////////////
Microsoft.Office.Interop.Excel.Range encabezado = hoja_trabajo.get_Range("B5:T12");
encabezado.BorderAround2(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium);
encabezado.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbWhiteSmoke;
//CONTENIDO
hoja_trabajo.Cells[13, 2] = "Id";
hoja_trabajo.Cells[13, 3] = "Modelo";
hoja_trabajo.Cells[13, 6] = "Tamaño";
hoja_trabajo.Cells[13, 8] = "Material";
hoja_trabajo.Cells[13, 9] = "Categoria";
hoja_trabajo.Cells[13, 11] = "Cantidad";
hoja_trabajo.Cells[13, 12] = "Tipo";
hoja_trabajo.Cells[13, 14] = "P. P";
hoja_trabajo.Cells[13, 15] = "P. F";
hoja_trabajo.Cells[13, 16] = "P. M";
hoja_trabajo.Cells[13, 17] = "Descripción";
hoja_trabajo.Cells[13, 20] = "Peso";
int hi = 14;
int hj = 2;
for (int i = 0; i < tableProductosFiltro.Rows.Count; i++)
{
for (int j = 0; j < tableProductosFiltro.Columns.Count; j++)
{
if (j == 0)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
hj++;
}
else if (j == 1)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
hj = hj + 3;
aplicacion.get_Range("C" + hi.ToString(), "E" + hi.ToString()).Merge(true);
}
else if (j == 2)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
hj = hj + 2;
aplicacion.get_Range("F" + hi.ToString(), "G" + hi.ToString()).Merge(true);
}
else if (j == 3)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
//hoja_trabajo.Range[hi, hj].Style.Color = Color.LightBlue;
hj++;
}
else if (j == 4)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
//hoja_trabajo.Range[hi, hj].Style.Color = Color.LightBlue;
hj = hj + 2;
aplicacion.get_Range("I" + hi.ToString(), "J" + hi.ToString()).Merge(true);
}
else if (j == 5)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
//hoja_trabajo.Cells[hi, hj].Style.Color = Color.LightBlue;
hj++;
}
else if (j == 6)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
//hoja_trabajo.Range[hi, hj].Style.Color = Color.LightBlue;
hj = hj + 2;
aplicacion.get_Range("L" + hi.ToString(), "M" + hi.ToString()).Merge(true);
}
else if (j == 7 || j == 8 || j == 9)
{
float num = (float)Convert.ToDouble(tableProductosFiltro.Rows[i][j].ToString());
String numero = string.Format("{0:c2}", num);
hoja_trabajo.Cells[hi, hj] = numero;
hj++;
}
else if (j == 10)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
//hoja_trabajo.Range[hi, hj].Style.Color = Color.LightBlue;
hj = hj + 3;
aplicacion.get_Range("Q" + hi.ToString(), "S" + hi.ToString()).Merge(true);
}
else if (j == 11)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
hj++;
}
}
hj = 2;
hi++;
}
//bordes
inicio = hoja_trabajo.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
ultimo = hoja_trabajo.get_Range("B13", inicio);
Microsoft.Office.Interop.Excel.Borders bordeTotal = ultimo.Borders;
bordeTotal.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
bordeTotal.Weight = 3d;
//
//bold
hoja_trabajo.Range["B13:T13"].Font.Bold = true;
hoja_trabajo.Range["B13:T13"].Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightGreen;
hoja_trabajo.Range["C6:E6"].Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightSkyBlue;
aplicacion.get_Range("C13", "E13").Merge(true);
aplicacion.get_Range("F13", "G13").Merge(true);
aplicacion.get_Range("I13", "J13").Merge(true);
aplicacion.get_Range("Q13", "S13").Merge(true);
aplicacion.get_Range("L13", "M13").Merge(true);
libros_trabajo.SaveAs(fichero.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
libros_trabajo.Close(true);
aplicacion.Quit();
txtGenerando.Text = "";
try
{
System.Diagnostics.Process.Start(fichero.FileName);
}
catch
{
DialogResult pregunta2;
pregunta2 = MetroFramework.MetroMessageBox.Show(this, "No se puede abrir el documento", "AVISO", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
DialogResult pregunta;
pregunta = MetroFramework.MetroMessageBox.Show(this, "\nDocumento generado con exito\n Guardado en: " + fichero.FileName + " ", "Documento", MessageBoxButtons.OK, MessageBoxIcon.Question);
Cursor.Current = Cursors.Default;
}
else
{
Cursor.Current = Cursors.Default;
txtGenerando.Text = "";
}
}
catch (Exception ex)
{
DialogResult pregunta;
pregunta = MetroFramework.MetroMessageBox.Show(this, "Ya se ha generado este documento", "AVISO" + ex, MessageBoxButtons.OK, MessageBoxIcon.Warning);
Console.WriteLine("Error" + ex);
Cursor.Current = Cursors.Default;
txtGenerando.Text = "";
}
}
else
{
DialogResult pregunta;
pregunta = MetroFramework.MetroMessageBox.Show(this, "No existen datos para generar el documento", "AVISO", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void ExportExcel_Click(object sender, RoutedEventArgs e)
{
try
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook worKbooK;
Microsoft.Office.Interop.Excel.Worksheet worksheet;
Microsoft.Office.Interop.Excel.Range celLrangE;
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
worKbooK = excel.Workbooks.Add(Type.Missing);
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet;
worksheet.Name = "تراکنش ها";
worksheet.Cells.Font.Size = 14;
var row = 0;
worksheet.Cells.Font.Color = System.Drawing.Color.Black;
foreach (var cost in CostsList)
{
for (int i = 0; i < 9; i++)
{
if (row == 0)
{
switch (i)
{
case 0:
worksheet.Cells[1, i] = "تاریخ";
break;
case 1:
worksheet.Cells[1, i] = "مبلغ";
break;
case 2:
worksheet.Cells[1, i] = "بابت";
break;
case 3:
worksheet.Cells[1, i] = "جزئیات";
break;
case 4:
worksheet.Cells[1, i] = "اپراتور";
break;
case 5:
worksheet.Cells[1, i] = "روش پرداخت";
break;
case 6:
worksheet.Cells[1, i] = "نام عضو";
break;
case 7:
worksheet.Cells[1, i] = "بدهی عضو";
break;
case 8:
worksheet.Cells[1, i] = "کد عضو";
break;
default:
break;
}
}
else
{
switch (i)
{
case 0:
worksheet.Cells[row, i] = cost.Date; // "تاریخ";
break;
case 1:
worksheet.Cells[row, i] = cost.Amount; // "مبلغ";
break;
case 2:
worksheet.Cells[row, i] = cost.Type; // "بابت";
break;
case 3:
worksheet.Cells[row, i] = cost.Info; // "جزئیات";
break;
case 4:
worksheet.Cells[row, i] = cost.User; // "اپراتور";
break;
case 5:
worksheet.Cells[row, i] = cost.Method; // "روش پرداخت";
break;
case 6:
worksheet.Cells[row, i] = cost.Member; //"نام عضو";
break;
case 7:
worksheet.Cells[row, i] = cost.Debtor; //"بدهی عضو";
break;
case 8:
worksheet.Cells[row, i] = cost.MemberId; //"کد عضو";
break;
default:
break;
}
}
row++;
}
}
celLrangE = worksheet.Range[worksheet.Cells[0, 0], worksheet.Cells[row, 9]];
celLrangE.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
//celLrangE = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[2, 9]];
worKbooK.SaveAs(Guid.NewGuid().ToString());
worKbooK.Close();
excel.Quit();
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// FUNCTION FOR EXPORT TO EXCEL
/// </summary>
/// <param name="dataTable"></param>
/// <param name="worksheetName"></param>
/// <param name="saveAsLocation"></param>
/// <returns></returns>
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string fileTitle, string fileSubtitle)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Work sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
//Work sheet title
excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[1, 35]].Merge();
excelSheet.Cells[1, 1] = fileTitle;
excelSheet.Cells[1, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelSheet.Cells[1, 1].EntireRow.Font.Bold = true;
excelSheet.Range[excelSheet.Cells[2, 1], excelSheet.Cells[2, 35]].Merge();
excelSheet.Cells[2, 1] = fileSubtitle;
excelSheet.Cells[2, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelSheet.Range[excelSheet.Cells[3, 1], excelSheet.Cells[3, 35]].Merge();
// loop through each row and add values to our sheet
int rowcount = 5;
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 6)
{
excelSheet.Cells[5, i] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (rowcount > 5)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
}
//change column name
excelSheet.Cells[4, 1] = "STT";
excelSheet.Cells[4, 1].EntireRow.Font.Bold = true;
excelSheet.Range[excelSheet.Cells[4, 1], excelSheet.Cells[5, 1]].Merge();
excelSheet.Cells[4, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelSheet.Cells[4, 1].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
excelSheet.Cells[4, 2] = "Số thẻ";
excelSheet.Cells[4, 2].EntireRow.Font.Bold = true;
excelSheet.Range[excelSheet.Cells[4, 2], excelSheet.Cells[5, 2]].Merge();
excelSheet.Cells[4, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelSheet.Cells[4, 2].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
excelSheet.Cells[4, 3] = "Họ và tên";
excelSheet.Cells[4, 3].EntireRow.Font.Bold = true;
excelSheet.Range[excelSheet.Cells[4, 3], excelSheet.Cells[5, 3]].Merge();
excelSheet.Cells[4, 3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelSheet.Cells[4, 3].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
excelSheet.Cells[4, 35] = "Tổng cộng";
excelSheet.Cells[4, 35].EntireRow.Font.Bold = true;
excelSheet.Range[excelSheet.Cells[4, 35], excelSheet.Cells[5, 35]].Merge();
excelSheet.Cells[4, 35].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelSheet.Cells[4, 35].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
excelSheet.Range[excelSheet.Cells[4, 4], excelSheet.Cells[4, 34]].Merge();
excelSheet.Cells[4, 4] = "Ngày trong tháng";
excelSheet.Cells[4, 4].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelSheet.Cells[4, 4].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
excelSheet.Cells[4, 4].EntireRow.Font.Bold = true;
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount - 3, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[1, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
excelworkBook.SaveAs(saveAsLocation);;
excelworkBook.Close();
excel.Quit();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReportType)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
excel = new Microsoft.Office.Interop.Excel.Application(); // Start Excel and get Application object.
excel.Visible = false; // for making Excel visible
excel.DisplayAlerts = false;
excelworkBook = excel.Workbooks.Add(Type.Missing); // Creation a new Workbook
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet; // Workk sheet
excelSheet.Name = worksheetName;
excelSheet.Cells[1, 1] = ReportType;
System.Drawing.ColorConverter cc = new System.Drawing.ColorConverter();
if (dataTable.Columns.Count == 14)
{
excelSheet.get_Range("A1", "N1").Merge(Type.Missing);
excelSheet.get_Range("A1", "N1").Font.Size = 20;
excelSheet.get_Range("A1", "N1").Font.Bold = true;
excelSheet.get_Range("A1", "N1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelSheet.get_Range("A1", "N1").VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelCellrange = excelSheet.get_Range("A1", "N1");
excelCellrange.RowHeight = 25;
int rowcount = 2; // loop through each row and add values to our sheet
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
if (rowcount == 3) // on the first iteration we add the column headers
{
excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName;
excelCellrange.RowHeight = 25;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelCellrange = excelSheet.Range[excelSheet.Cells[2, i], excelSheet.Cells[2, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
excelSheet.Cells[1, i].Interior.Color = System.Drawing.ColorTranslator.ToOle((System.Drawing.Color)cc.ConvertFromString("#F0F8FF")); // First Cell
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
excelCellrange.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
if (rowcount > 3) //for alternate rows
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
}
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; // now we resize the columns
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
//Namrata: 09/12/2017
excelSheet.EnableAutoFilter = true; //Enable Auto-filter.
Microsoft.Office.Interop.Excel.Range range = excelSheet.get_Range("A1", "N1"); //Create the range.
range.AutoFilter("1", "<>", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlOr, "", true); //Auto-filter the range.
excelSheet.get_Range("A1", "N1").EntireColumn.AutoFit(); //Auto-fit the second column.
}
if (dataTable.Columns.Count == 21)
{
excelSheet.get_Range("A1", "U1").Merge(Type.Missing);
excelSheet.get_Range("A1", "U1").Font.Size = 20;
excelSheet.get_Range("A1", "U1").Font.Bold = true;
excelSheet.get_Range("A1", "U1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelSheet.get_Range("A1", "U1").VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelCellrange = excelSheet.get_Range("A1", "U1");
excelCellrange.RowHeight = 25;
int rowcount = 2; // loop through each row and add values to our sheet
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
if (rowcount == 3) // on the first iteration we add the column headers
{
excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName;
excelCellrange.RowHeight = 25;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelCellrange = excelSheet.Range[excelSheet.Cells[2, i], excelSheet.Cells[2, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
excelSheet.Cells[1, i].Interior.Color = System.Drawing.ColorTranslator.ToOle((System.Drawing.Color)cc.ConvertFromString("#F0F8FF")); // First Cell
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
if (rowcount > 3) //for alternate rows
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
}
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; // now we resize the columns
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
//Namrata: 09/12/2017
excelSheet.EnableAutoFilter = true; //Enable Auto-filter.
Microsoft.Office.Interop.Excel.Range range = excelSheet.get_Range("A1", "U1"); //Create the range.
range.AutoFilter("1", "<>", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlOr, "", true); //Auto-filter the range.
excelSheet.get_Range("A1", "U1").EntireColumn.AutoFit(); //Auto-fit the second column.
}
if (dataTable.Columns.Count == 13)
{
excelSheet.get_Range("A1", "M1").Merge(Type.Missing);
excelSheet.get_Range("A1", "M1").Font.Size = 20;
excelSheet.get_Range("A1", "M1").Font.Bold = true;
excelSheet.get_Range("A1", "M1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelSheet.get_Range("A1", "M1").VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelCellrange = excelSheet.get_Range("A1", "M1");
excelCellrange.RowHeight = 25;
int rowcount = 2; // loop through each row and add values to our sheet
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
if (rowcount == 3) // on the first iteration we add the column headers
{
excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName;
excelCellrange.RowHeight = 25;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelCellrange = excelSheet.Range[excelSheet.Cells[2, i], excelSheet.Cells[2, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
excelSheet.Cells[1, i].Interior.Color = System.Drawing.ColorTranslator.ToOle((System.Drawing.Color)cc.ConvertFromString("#F0F8FF")); // First Cell
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
if (rowcount > 3) //for alternate rows
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
}
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; // now we resize the columns
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
//Namrata: 09/12/2017
excelSheet.EnableAutoFilter = true; //Enable Auto-filter.
Microsoft.Office.Interop.Excel.Range range = excelSheet.get_Range("A1", "M1"); //Create the range.
range.AutoFilter("1", "<>", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlOr, "", true); //Auto-filter the range.
excelSheet.get_Range("A1", "M1").EntireColumn.AutoFit(); //Auto-fit the second column.
}
excelworkBook.SaveAs(saveAsLocation); //now save the workbook and exit Excel
MessageBox.Show("File Exported Successfully", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
excelworkBook.Close();
excel.Quit();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
//Ví dụ gọi hàm viết excel
//WriteDataTableToExcel(table,"Sheet1",@"C:\Windows\abc.xlsx","Details");
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
excelSheet.Cells[1, 1] = ReporType;
excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToString("dd/MM/yyyy");
// loop through each row and add values to our sheet
int rowcount = 2;
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 3)
{
excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (rowcount > 3)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
//FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
//FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
excelworkBook.SaveAs(saveAsLocation);
excelworkBook.Close();
excel.Quit();
Marshal.ReleaseComObject(excelSheet);
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
/// <summary>
/// FUNCTION FOR EXPORT TO EXCEL
/// </summary>
/// <param name="dataTable"></param>
/// <param name="worksheetName"></param>
/// <param name="saveAsLocation"></param>
/// <returns></returns>
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType,
string totalbalance, string totaladvance, string DateTo, string Datefrom)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
excelSheet.Cells[1, 1] = ReporType;
if (totalbalance == "Green")
{
excelSheet.Cells[1, 4] = "Available Stock : ";
excelSheet.Cells[1, 5] = totaladvance;
}
else
{
excelSheet.Cells[1, 4] = "Total Balance : " + totalbalance;
excelSheet.Cells[1, 5] = "Total Advance : " + totaladvance;
}
excelSheet.Cells[2, 1] = "Data of Date from " + Datefrom + " To " + DateTo;
// loop through each row and add values to our sheet
int rowcount = 4;
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 6)
{
excelSheet.Cells[4, i] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
excelSheet.Cells.Font.Bold = true;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (rowcount > 6)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[4, 6]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
excelworkBook.SaveAs(saveAsLocation);;
excelworkBook.Close();
excel.Quit();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
public BuildDueContractFiles(DataTable table, string currentPath)
{
Console.WriteLine("Building Excel files, please wait...");
BranchData branchData = new BranchData();
Dictionary <string, string> branchManagers = branchData.getBranchManagers();
fileEmailCollection = new Dictionary <string, string>();
Utils utils = new Utils();
for (int count = 0; count < branchManagers.Count; count++)
{
//get branch manager details for branch manager collection
var element = branchManagers.ElementAt(count);
var branchCodeKey = (element.Key).ToUpper().ToString();
var branchManagerEmail = element.Value;
var fileName = "bm_" + utils.createFilename();
//create temp branch data collection
tempBranchData = new DataTable();
tempBranchData.Columns.Add("Branch", typeof(string));
tempBranchData.Columns.Add("Product", typeof(string));
tempBranchData.Columns.Add("Contract No", typeof(string));
tempBranchData.Columns.Add("Rental Amount", typeof(string));
tempBranchData.Columns.Add("Rental No", typeof(string));
tempBranchData.Columns.Add("Contract peroid", typeof(string));
tempBranchData.Columns.Add("Debtor balance", typeof(string));
tempBranchData.Columns.Add("Default Interest", typeof(string));
tempBranchData.Columns.Add("No of days Over in arrears", typeof(string));
tempBranchData.Columns.Add("Customer name", typeof(string));
tempBranchData.Columns.Add("Address", typeof(string));
tempBranchData.Columns.Add("Marketing officer", typeof(string));
tempBranchData.Columns.Add("Collector name", typeof(string));
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
workBook = excel.Workbooks.Add(Type.Missing);
//collect excel object processes and add to a list
int pid = -1;
HandleRef hwnd = new HandleRef(excel, (IntPtr)excel.Hwnd);
GetWindowThreadProcessId(hwnd, out pid);
GlobalObjects.addPidsToList(pid);
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
workSheet.Name = "Contracts";
workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[1, table.Columns.Count]].Merge();
workSheet.Cells[1, 1] = "Due Contracts";
workSheet.Cells.Font.Size = 15;
foreach (DataRow datarow in table.Rows)
{
string currentBranchCode = (datarow.Field <string>("Branch")).ToUpper().ToString();
if (branchCodeKey.Equals(currentBranchCode))
{
tempBranchData.ImportRow(datarow);
}
}
//create the branch specific excel file if data exists
if (tempBranchData.Rows.Count > 0)
{
try
{
int rowcount = 2;
//sort the data by product name
DataView dv = tempBranchData.DefaultView;
dv.Sort = "Product ASC";
DataTable sortedDT = dv.ToTable();
foreach (DataRow dr in sortedDT.Rows)
{
rowcount += 1;
for (int i = 1; i <= table.Columns.Count; i++)
{
if (rowcount == 3)
{
workSheet.Cells[2, i] = table.Columns[i - 1].ColumnName;
workSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
workSheet.Cells[rowcount, i] = dr[i - 1].ToString();
workSheet.Cells[rowcount, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
workSheet.Cells.Font.Size = 12;
if (rowcount > 3)
{
if (i == table.Columns.Count)
{
if (rowcount % 2 == 0)
{
celLrangE = workSheet.Range[workSheet.Cells[rowcount, 1], workSheet.Cells[rowcount, table.Columns.Count]];
}
}
}
}
}
celLrangE = workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[rowcount, table.Columns.Count]];
celLrangE.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
celLrangE = workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[2, table.Columns.Count]];
//bold and center title
celLrangE = workSheet.Range["A1", "M1"];
celLrangE.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
fontObj = celLrangE.Font;
fontObj.Color = ConsoleColor.Red;
//bold header titles
celLrangE = workSheet.Range["A2", "M2"];
fontObj = celLrangE.Font;
fontObj.Bold = true;
//cell number format - Rentals
celLrangE = workSheet.Range["D3", "D" + rowcount];
celLrangE.NumberFormat = "0#.00";
//cell number format - Deter balance
celLrangE = workSheet.Range["G3", "G" + rowcount];
celLrangE.NumberFormat = "0#.00";
//cell number format - default interest
celLrangE = workSheet.Range["H3", "H" + rowcount];
celLrangE.NumberFormat = "0#.00";
//cell number format - No. of days over arrears
celLrangE = workSheet.Range["I3", "I" + rowcount];
celLrangE.NumberFormat = "0#.00";
workBook.SaveAs(currentPath + fileName, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
workBook.Close(false, m_objOpt, m_objOpt);
excel.Quit();
fileEmailCollection.Add(fileName, branchManagerEmail);
tempBranchData.Dispose();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
utils.releaseObject(workBook);
utils.releaseObject(workSheet);
utils.releaseObject(excel);
utils.releaseObject(celLrangE);
utils.releaseObject(fontObj);
}
}
}
Console.WriteLine("Creating excel files completed");
//clear excel objects in a new thread using the pids in the pid list
/*
* new Thread(() =>
* {
* Thread.CurrentThread.IsBackground = true;
* Console.WriteLine("Clear excel files BM thread");
* utils.killProcess(pidList, "EXCEL");
* }).Start();
*/
//sending the emails
if (fileEmailCollection.Count > 0)
{
SendEmail sendEmail = new SendEmail(fileEmailCollection, currentPath);
}
}
/// <summary>
/// FUNCTION FOR EXPORT TO EXCEL
/// </summary>
/// <param name="dataTable"></param>
/// <param name="worksheetName"></param>
/// <param name="saveAsLocation"></param>
/// <returns></returns>
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType, string progtype, string user)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = true;
excel.DisplayAlerts = true;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
excelSheet.Cells[1, 1] = "Report Name - " + ReporType;
excelSheet.Cells[2, 1] = "Date of Report Generation : " + General_methods.get_current_date();
excelSheet.Cells[3, 1] = "Time of Report Generation :" + General_methods.get_current_time();
excelSheet.Cells[4, 1] = "Report Created By :" + user;
excelSheet.Cells[5, 1] = "Program Type :" + progtype;
// loop through each row and add values to our sheet
int rowcount = 6;
foreach (DataRow datarow in dataTable.Rows)
{
//adding one to rowcount
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 7)
{
excelSheet.Cells[6, i] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
excelSheet.Cells[6, i].Font.Bold = true;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (rowcount > 7)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 4 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[5, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
excelSheet.Cells[1, 1].Font.Size = 20;
//now save the workbook and exit Excel
/*var worksheet = excelworkBook.Worksheets[1] as
* Microsoft.Office.Interop.Excel.Worksheet;
*
* Microsoft.Office.Interop.Excel.Range chartRange;
*
* Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
* Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
* Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;
*
* myChart.Name = "Program Budget Report - Expenses";
* chartRange = worksheet.get_Range("M4", "S6");
* chartPage.SetSourceData(chartRange);
* chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;*/
//excelworkBook.SaveAs(saveAsLocation);
//excelworkBook.Close();
//excel.Quit();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
public void Print()
{
Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application();
appExcel.Application.Workbooks.Add(Type.Missing);
appExcel.ActiveWindow.DisplayGridlines = false;
appExcel.Columns.ColumnWidth = 15;
appExcel.Rows.RowHeight = 30;
appExcel.Range[appExcel.Cells[1, 1], appExcel.Cells[1, 1]].RowHeight = 150;
appExcel.Cells[2, 1].EntireRow.Font.Bold = true;
appExcel.Cells[3, 1].EntireRow.Font.Bold = true;
appExcel.Cells[4, 1].EntireRow.Font.Bold = true;
appExcel.ActiveSheet.Shapes.AddPicture(@"C:\Users\NAM\Desktop\BDElogo.jpg", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, (float)((double)appExcel.Cells[1, 6].Left), 30, 100, 100);
for (int i = 4; i < DonHangGiaoList.Count + 5; i++)
{
for (int j = 1; j <= 11; j++)
{
var appExcelBorderRange = appExcel.Range[appExcel.Cells[i, j], appExcel.Cells[i, j]];
Microsoft.Office.Interop.Excel.Borders border = appExcelBorderRange.Borders;
border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
}
}
appExcel.Range[appExcel.Cells[4, 1], appExcel.Cells[DonHangGiaoList.Count + 5, 11]].Wraptext = true;
appExcel.Range[appExcel.Cells[1, 1], appExcel.Cells[1, 11]].Merge();
appExcel.Range[appExcel.Cells[2, 1], appExcel.Cells[2, 11]].Merge();
appExcel.Range[appExcel.Cells[2, 1], appExcel.Cells[2, 11]].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
appExcel.Range[appExcel.Cells[3, 1], appExcel.Cells[3, 11]].Merge();
appExcel.Range[appExcel.Cells[3, 1], appExcel.Cells[3, 11]].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
appExcel.Range[appExcel.Cells[4, 1], appExcel.Cells[4, 11]].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
appExcel.Range[appExcel.Cells[4, 1], appExcel.Cells[4, 11]].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
appExcel.Cells[2, 1] = "BinhDinhExpress Thuộc Công Ty TNHH Thương Mại Và Dịch Vụ Vận Chuyển ĐẠT KHANG";
appExcel.Cells[3, 1] = "DANH SÁCH GIAO HÀNG";
appExcel.Cells[4, 1] = "Mã nhân viên giao";
appExcel.Cells[4, 2] = "Khu Vực";
appExcel.Cells[4, 3] = "Loại Hàng";
appExcel.Cells[4, 4] = "Tên người nhận";
appExcel.Cells[4, 5] = "Địa chỉ người nhận";
appExcel.Cells[4, 6] = "Số điện thoại người nhận";
appExcel.Cells[4, 7] = "Tiền thu hộ";
appExcel.Cells[4, 8] = "Tổng tiền";
appExcel.Cells[4, 9] = "Ghi chú";
appExcel.Cells[4, 10] = "Chữ kí người nhận";
appExcel.Cells[4, 11] = "Chữ kí nhân viên";
for (int i = 0; i < DonHangGiaoList.Count; i++)
{
appExcel.Cells[i + 5, 1] = DonHangGiaoList[i].MaNhanVienGiao;
appExcel.Cells[i + 5, 2] = DonHangGiaoList[i].TenKhuVuc;
appExcel.Cells[i + 5, 3] = DonHangGiaoList[i].TenLoaiDonHang;
appExcel.Cells[i + 5, 4] = DonHangGiaoList[i].TenNguoiMua;
appExcel.Cells[i + 5, 5] = DonHangGiaoList[i].DiaDiemGiaoHang;
appExcel.Cells[i + 5, 6] = DonHangGiaoList[i].SdtNguoiMua;
appExcel.Cells[i + 5, 7] = DonHangGiaoList[i].TienThuHo;
appExcel.Cells[i + 5, 8] = DonHangGiaoList[i].TongThanhTien;
appExcel.Cells[i + 5, 9] = DonHangGiaoList[i].GhiChu;
appExcel.Cells[i + 5, 10] = "";
appExcel.Cells[i + 5, 11] = "";
}
appExcel.Visible = true;
}
public void Excel_Generation(string fio, string kod, string s, string po, string coldPr, string coldTek, string hotPr, string hotTek, decimal cena)
{
Abonent_Otdel AO = new Abonent_Otdel();
Microsoft.Office.Interop.Excel.Application TNSE_App = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook WB_TNS = TNSE_App.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet WS_TNS = (Microsoft.Office.Interop.Excel.Worksheet)WB_TNS.ActiveSheet;
WS_TNS.Cells[1, 1] = "ЕПД" + "\n" + "Единый платежный документ";
WS_TNS.Name = "ЕПД";
WS_TNS.Cells[2, 1] = "Плательщик";
WS_TNS.Cells[2, 2] = fio;
WS_TNS.Cells[3, 1] = "Код плательщика";
WS_TNS.Cells[3, 2] = kod;
WS_TNS.Cells[4, 1] = "Период с-по";
WS_TNS.Cells[4, 2] = s;
WS_TNS.Cells[4, 3] = po;
WS_TNS.Cells[5, 1] = "Показания";
WS_TNS.Cells[5, 2] = "Предыдущие";
WS_TNS.Cells[5, 3] = "Текущие";
WS_TNS.Cells[6, 1] = "Холодное";
WS_TNS.Cells[6, 2] = coldPr;
WS_TNS.Cells[6, 3] = coldTek;
WS_TNS.Cells[7, 1] = "Горячее";
WS_TNS.Cells[7, 2] = hotPr;
WS_TNS.Cells[7, 3] = hotTek;
WS_TNS.Cells[8, 1] = "Сумма оплаты";
WS_TNS.Cells[8, 2] = cena;
WS_TNS.Range[WS_TNS.Cells[3, 2], WS_TNS.Cells[3, 3]].Merge();
WS_TNS.Range[WS_TNS.Cells[2, 2], WS_TNS.Cells[2, 3]].Merge();
Microsoft.Office.Interop.Excel.Range Doc_Range = WS_TNS.UsedRange;
Microsoft.Office.Interop.Excel.Range c1_Range = Doc_Range.Cells[1, 3];
Microsoft.Office.Interop.Excel.Range c2_Range = Doc_Range.Cells[2, 1];
Microsoft.Office.Interop.Excel.Range c3_Range = Doc_Range.Cells[1, 2];
Microsoft.Office.Interop.Excel.Range c3_1_Range = Doc_Range.Cells[1, 1];
Microsoft.Office.Interop.Excel.Range c4_Range = Doc_Range.Cells[3, 1];
Microsoft.Office.Interop.Excel.Range c5_Range = Doc_Range.Cells[4, 1];
Microsoft.Office.Interop.Excel.Range c6_Range = Doc_Range.Cells[5, 1];
Microsoft.Office.Interop.Excel.Range c7_Range = Doc_Range.Cells[5, 2];
Microsoft.Office.Interop.Excel.Range c8_Range = Doc_Range.Cells[5, 3];
Microsoft.Office.Interop.Excel.Range c9_Range = Doc_Range.Cells[6, 1];
Microsoft.Office.Interop.Excel.Range c10_Range = Doc_Range.Cells[7, 1];
Microsoft.Office.Interop.Excel.Range c11_Range = Doc_Range.Cells[8, 1];
c1_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
c1_Range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
c2_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
c2_Range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
c3_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
c3_Range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
c3_1_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
c3_1_Range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
c4_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
c4_Range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
c5_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
c5_Range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
Microsoft.Office.Interop.Excel.Borders C_1_Border = c1_Range.Borders;
C_1_Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
C_1_Border.Weight = 3d;
Microsoft.Office.Interop.Excel.Borders C_2_Border = c2_Range.Borders;
C_2_Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
C_2_Border.Weight = 3d;
Microsoft.Office.Interop.Excel.Borders C_3_Border = c3_Range.Borders;
C_3_Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
C_3_Border.Weight = 3d;
Microsoft.Office.Interop.Excel.Borders C_3_1_Border = c3_1_Range.Borders;
C_3_1_Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
C_3_1_Border.Weight = 3d;
Microsoft.Office.Interop.Excel.Borders C_4_Border = c4_Range.Borders;
C_4_Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
C_4_Border.Weight = 3d;
Microsoft.Office.Interop.Excel.Borders C_5_Border = c5_Range.Borders;
C_5_Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
C_5_Border.Weight = 3d;
Microsoft.Office.Interop.Excel.Borders C_6_Border = c6_Range.Borders;
C_6_Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
C_6_Border.Weight = 3d;
Microsoft.Office.Interop.Excel.Borders C_7_Border = c7_Range.Borders;
C_7_Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
C_7_Border.Weight = 3d;
Microsoft.Office.Interop.Excel.Borders C_8_Border = c8_Range.Borders;
C_8_Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
C_8_Border.Weight = 3d;
Microsoft.Office.Interop.Excel.Borders C_9_Border = c9_Range.Borders;
C_9_Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
C_9_Border.Weight = 3d;
Microsoft.Office.Interop.Excel.Borders C_10_Border = c10_Range.Borders;
C_10_Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
C_10_Border.Weight = 3d;
Microsoft.Office.Interop.Excel.Borders C_11_Border = c11_Range.Borders;
C_11_Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
C_11_Border.Weight = 3d;
WS_TNS.Range[WS_TNS.Cells[1, 1], WS_TNS.Cells[1, 3]].Merge();
WS_TNS.Columns.AutoFit();
TNSE_App.Visible = true;
//WB_TNS.Save();
//WB_TNS.Close();
TNSE_App.Quit();
}
/// <summary>
/// 写入Excel的Sheet表
/// </summary>
/// <param name="wb"></param>
/// <param name="tables"></param>
private void WriteExcelSheet(Microsoft.Office.Interop.Excel.Workbook wb)
{
List <string> SheetNames = GetSheetsName(wb); //Sheet表名字集合,防止相同名称,出现错误!
for (int i = 0; i < _tables.Count; i++)
{
System.Data.DataTable table = _tables[i];
int rows = table.Rows.Count;
int cols = table.Columns.Count;
//获取Sheet表
Microsoft.Office.Interop.Excel.Worksheet wsheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i + 1]; //wb.Worksheets 所以从1开始
//选中表
wsheet.Select();
//设置表名字
wsheet.Name = GetCorrectSheetName(SheetNames, table.TableName, (i + 1)); //Name要特别注意
//保存DataTable和Sheet表的绑定
SheetTable.Add(wsheet.Name, table);
//设置总标题
DrawTitle(wsheet, cols);
//设置HeaderText
if (IsDrawHeader)
{
DrawText(wsheet, _headerdtext, _headerfont, TextAlign.xlHAlignLeft, cols);// Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft, cols);
}
//数据列表标题
//Microsoft.Office.Interop.Excel.Range r = ret.get_Range(ret.Cells[1, 1], ret.Cells[1, table.Columns.Count]);
if (_isbodylistheader)
{
_rowindex++;
Microsoft.Office.Interop.Excel.Range range = wsheet.Range[wsheet.Cells[_rowindex, 1], wsheet.Cells[_rowindex, cols]];
object[] header = new object[cols];
for (int j = 0; j < cols; j++)
{
header[j] = table.Columns[j].ToString();
}
range.Value2 = header;
SetFont(range, _bodyheaderfont);
if (_isalldisplayborder)
{
range.Borders.LineStyle = 1; //边框线
range.Borders.Weight = (int)_borderweight;
}
}
if (rows > 0)
{
_rowindex++;
Microsoft.Office.Interop.Excel.Range range = wsheet.get_Range("A" + _rowindex, Missing.Value);
object[,] objData = new Object[rows, cols];
for (int row = 0; row < rows; row++)
{
for (int col = 0; col < cols; col++)
{
string converttxt = String.Empty;
if (_isautoconverttext)
{
converttxt = "'";
}
objData[row, col] = converttxt + table.Rows[row][col].ToString(); //随后数据后面加单引号
}
}
range = range.get_Resize(rows, cols);
range.Value2 = objData;
SetFont(range, _bodyfont);
if (_isalldisplayborder)
{
range.Borders.LineStyle = 1; //加线框
range.Borders.Weight = (int)_borderweight;
}
else
{
//((Microsoft.Office.Interop.Excel.Range)range.Columns["A:A", Type.Missing]).Borders.LineStyle = 1;
//((Microsoft.Office.Interop.Excel.Range)range.Columns["A:A", Type.Missing]).Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
//指定的列显示边框
foreach (var vk in _columnsborder)
{
Microsoft.Office.Interop.Excel.Borders borders = ((Microsoft.Office.Interop.Excel.Range)range.Columns[vk.Key, Type.Missing]).Borders;
borders.LineStyle = 1;
borders.Weight = (int)vk.Value;
}
}
if (IsAutoFit)
{
range.EntireColumn.AutoFit(); //自动适应列
}
//更新行索引
_rowindex += (rows - 1);
}
//设置FooterText
if (IsDrawFooter)
{
if (rows == 0)
{
_rowindex += 2; //与Body隔一行
}
DrawText(wsheet, _footertext, _footerfont, DrawFooterTextAlign, cols);
}
//所有列自动换行
wsheet.Columns.WrapText = _iswraptext;
//设置列宽
if (!IsAutoFit)
{
foreach (var vk in _columnswidth)
{
((Microsoft.Office.Interop.Excel.Range)wsheet.Columns[vk.Key, System.Type.Missing]).ColumnWidth = vk.Value;
}
}
//保存名字
SheetNames.Add(wsheet.Name);
//还原
_rowindex = _saveindex;
}
//选中第一个表
((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1]).Select();
}
private void buttonExportExcel_Click(object sender, EventArgs e)
{
if (comboBoxStudents.SelectedValue != null)
{
using (SaveFileDialog sfd = new SaveFileDialog()
{
Filter = "Excel Workbook|*.xlsx|Excel 97-2003 Workbook|*.xls|Excel Workbook(.csv)|*.csv"
})
{
sfd.InitialDirectory = "C";
sfd.Title = "SAVE AS EXCEL FILE";
sfd.FileName = "Export note - " + comboBoxStudents.GetItemText(comboBoxStudents.SelectedItem);
if (sfd.ShowDialog() == DialogResult.OK)
{
String path = sfd.FileName;
BLExternals bl = new BLExternals();
int studentId = Convert.ToInt32(comboBoxStudents.SelectedValue);
int studyYear = bl.GetYear(Convert.ToInt32(comboBoxStudents.SelectedValue));
DataTable tableY1 = bl.GetCatalogInfoPerYear(studentId, 1);
DataTable tableY2 = bl.GetCatalogInfoPerYear(studentId, 2);
DataTable tableY3 = bl.GetCatalogInfoPerYear(studentId, 3);
DataTable tableY4 = bl.GetCatalogInfoPerYear(studentId, 4);
tableY1.Columns["CourseName"].ColumnName = "Materie";
tableY1.Columns["PartialExam"].ColumnName = "Examen partial";
tableY1.Columns["FinalExam"].ColumnName = "Examen final";
tableY1.Columns["Laboratory"].ColumnName = "Laborator";
tableY1.Columns["Seminary"].ColumnName = "Seminar";
tableY1.Columns["BonusPoints"].ColumnName = "Puncte bonus";
tableY1.Columns["Total"].ColumnName = "Total";
tableY1.Columns["LastUpdate"].ColumnName = "Ultima actualizare";
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
if (studyYear >= 1)
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = "Catalog";
excelSheet.Cells[1, 1] = comboBoxStudents.GetItemText(comboBoxStudents.SelectedItem) + ", " + DateTime.Now.ToShortDateString();
// loop through each row and add values to our sheet
int rowcount = 2;
for (int i = 1; i <= tableY1.Columns.Count; i++)
{
// on the first iteration we add the column headers
excelSheet.Cells[2, i] = tableY1.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelSheet.Cells[rowcount + 1, 1] = "Anul 1";
excelSheet.Range[excelSheet.Cells[rowcount + 1, 1], excelSheet.Cells[rowcount + 1, tableY1.Columns.Count]].Merge();
excelSheet.Range[excelSheet.Cells[rowcount + 1, 1], excelSheet.Cells[rowcount + 1, tableY1.Columns.Count]].Cells.Font.Size = 14;
excelSheet.Cells[rowcount + 1, 1].Font.Bold = true;
excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount + 1, tableY1.Columns.Count]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rowcount = 3;
foreach (DataRow datarow in tableY1.Rows)
{
rowcount += 1;
for (int i = 1; i <= tableY1.Columns.Count; i++)
{
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
if (i != 1)
{
excelSheet.Cells[rowcount, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
if (i == tableY1.Columns.Count - 1)
{
excelSheet.Cells[rowcount, i].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
//for alternate rows
if (rowcount > 3)
{
if (i == tableY1.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, tableY1.Columns.Count]];
FormattingExcelCells(excelCellrange, "#aec4d1", System.Drawing.Color.Black, false);
excelSheet.Cells[rowcount, i - 1].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
}
}
}
}
if (studyYear >= 2)
{
excelSheet.Cells[rowcount + 2, 1] = "Anul 2";
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY2.Columns.Count]].Merge();
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY2.Columns.Count]].Cells.Font.Size = 14;
excelSheet.Cells[rowcount + 2, 1].Font.Bold = true;
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY2.Columns.Count]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rowcount = rowcount + 2;
foreach (DataRow datarow in tableY2.Rows)
{
rowcount += 1;
for (int i = 1; i <= tableY2.Columns.Count; i++)
{
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
if (i != 1)
{
excelSheet.Cells[rowcount, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
if (i == tableY1.Columns.Count - 1)
{
excelSheet.Cells[rowcount, i].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
//for alternate rows
if (rowcount > 3)
{
if (i == tableY2.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, tableY2.Columns.Count]];
FormattingExcelCells(excelCellrange, "#aec4d1", System.Drawing.Color.Black, false);
excelSheet.Cells[rowcount, i - 1].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
}
}
}
}
}
if (studyYear >= 3)
{
excelSheet.Cells[rowcount + 2, 1] = "Anul 3";
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY3.Columns.Count]].Merge();
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY3.Columns.Count]].Cells.Font.Size = 14;
excelSheet.Cells[rowcount + 2, 1].Font.Bold = true;
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY3.Columns.Count]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rowcount = rowcount + 2;
foreach (DataRow datarow in tableY3.Rows)
{
rowcount += 1;
for (int i = 1; i <= tableY3.Columns.Count; i++)
{
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
if (i != 1)
{
excelSheet.Cells[rowcount, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
if (i == tableY1.Columns.Count - 1)
{
excelSheet.Cells[rowcount, i].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
//for alternate rows
if (rowcount > 3)
{
if (i == tableY3.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, tableY3.Columns.Count]];
FormattingExcelCells(excelCellrange, "#aec4d1", System.Drawing.Color.Black, false);
excelSheet.Cells[rowcount, i - 1].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
}
}
}
}
}
if (studyYear >= 4)
{
excelSheet.Cells[rowcount + 2, 1] = "Anul 4";
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY4.Columns.Count]].Merge();
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY4.Columns.Count]].Cells.Font.Size = 14;
excelSheet.Cells[rowcount + 2, 1].Font.Bold = true;
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY4.Columns.Count]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rowcount = rowcount + 2;
foreach (DataRow datarow in tableY4.Rows)
{
rowcount += 1;
for (int i = 1; i <= tableY4.Columns.Count; i++)
{
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
if (i != 1)
{
excelSheet.Cells[rowcount, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
if (i == tableY1.Columns.Count - 1)
{
excelSheet.Cells[rowcount, i].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
//for alternate rows
if (rowcount > 3)
{
if (i == tableY4.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, tableY4.Columns.Count]];
FormattingExcelCells(excelCellrange, "#aec4d1", System.Drawing.Color.Black, false);
excelSheet.Cells[rowcount, i - 1].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, tableY1.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, tableY1.Columns.Count]];
FormattingExcelCells(excelCellrange, "#007099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
excelworkBook.SaveAs(path);;
excelworkBook.Close();
excel.Quit();
MessageBox.Show("Fisierul a fost exportat cu succes.", "Export", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
}
}
}
private void BtnGenerarPDF_Click(object sender, EventArgs e)
{
if (tablaProductos.RowCount != 0)
{
try
{
txtGenerando.Text = "GENERANDO DOCUMENTO ...";
Cursor.Current = Cursors.WaitCursor;
SaveFileDialog fichero = new SaveFileDialog();
fichero.FileName = "reporteador_" + fecha1 + "-" + fecha2;
fichero.Filter = "Excel (*.xls)|*.xls";
if (fichero.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application aplicacion;
Microsoft.Office.Interop.Excel.Workbook libros_trabajo;
Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo;
Microsoft.Office.Interop.Excel.Range inicio;
Microsoft.Office.Interop.Excel.Range ultimo;
aplicacion = new Microsoft.Office.Interop.Excel.Application();
libros_trabajo = aplicacion.Workbooks.Add();
hoja_trabajo = (Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);
hoja_trabajo.Range["B5:M5"].Font.Bold = true;
//ENCABEZADO
hoja_trabajo.Cells[6, 15] = "Total de cajas usadas:";
hoja_trabajo.Cells[7, 15] = "Total de cuadros:";
hoja_trabajo.Cells[8, 15] = "Total de placas:";
hoja_trabajo.Cells[6, 17] = "" + total_cajas;
hoja_trabajo.Cells[7, 17] = "" + total_cuadros;
hoja_trabajo.Cells[8, 17] = "" + total_placas;
hoja_trabajo.Cells[9, 15] = "Total abonado:";
hoja_trabajo.Cells[10, 15] = "Total resta:";
hoja_trabajo.Cells[11, 15] = "Total:";
hoja_trabajo.Cells[9, 17] = string.Format("{0:c2}", total_abonado);
hoja_trabajo.Cells[10, 17] = string.Format("{0:c2}", total_resta);
hoja_trabajo.Cells[11, 17] = string.Format("{0:c2}", total);
//hoja_trabajo.Cells[2,8] = "Reporteador";
hoja_trabajo.Cells[6, 3] = "Fecha inicial búsqueda: " + fecha1;
hoja_trabajo.Cells[11, 3] = "Fecha termino búsqueda : " + fecha2;
aplicacion.get_Range("H2:L4").Merge(true);
Microsoft.Office.Interop.Excel.Range titulo = hoja_trabajo.get_Range("H2:L4");
titulo.Merge();
titulo.Value = "Reporteador";
//titulo.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
//titulo.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
titulo.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
titulo.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
titulo.Font.Size = 36;
titulo.BorderAround2(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium);
titulo.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbGreen;
titulo.Font.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbWhite;
///////////////////////////////
//
aplicacion.get_Range("H7:L10").Merge(true);
Microsoft.Office.Interop.Excel.Range nombre = hoja_trabajo.get_Range("H7:L10");
nombre.Merge();
nombre.Value = "BASES Y MOLDURAS";
nombre.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
nombre.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
nombre.Font.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbGreen;
nombre.Font.Size = 30;
//Microsoft.Office.Interop.Excel.Range oRange = (Microsoft.Office.Interop.Excel.Range)hoja_trabajo.Cells[6, 9];
//float Left = (float)((double)oRange.Left);
//float Top = (float)((double)oRange.Top);
//C:\\Users\\Alejandro\\Source\\Repos\\BasesYMolduras\\BasesYMolduras\\Resources\\logo.png
//Resources/logo.png
//hoja_trabajo.Shapes.AddPicture(@"\Resources\logo.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, 175, 100);
////////////////////////////
aplicacion.get_Range("O6", "P11").Merge(true);
aplicacion.get_Range("C6", "E6").Merge(true);
aplicacion.get_Range("C11", "E11").Merge(true);
Microsoft.Office.Interop.Excel.Range encabezado = hoja_trabajo.get_Range("B5:R12");
encabezado.BorderAround2(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium);
encabezado.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbWhiteSmoke;
//O6:Q11
Microsoft.Office.Interop.Excel.Range rango_precios = hoja_trabajo.get_Range("O6:Q11");
Microsoft.Office.Interop.Excel.Borders borde_total_precios = rango_precios.Borders;
borde_total_precios.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
borde_total_precios.Weight = 3d;
Microsoft.Office.Interop.Excel.Range rango_fecha1 = hoja_trabajo.get_Range("C6:E6");
Microsoft.Office.Interop.Excel.Borders borde_fecha1 = rango_fecha1.Borders;
borde_fecha1.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
borde_fecha1.Weight = 3d;
Microsoft.Office.Interop.Excel.Range rango_fecha2 = hoja_trabajo.get_Range("C11:E11");
Microsoft.Office.Interop.Excel.Borders borde_fecha2 = rango_fecha2.Borders;
borde_fecha2.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
borde_fecha2.Weight = 3d;
//CONTENIDO
hoja_trabajo.Cells[13, 2] = "No. Cotizacion";
hoja_trabajo.Cells[13, 3] = "Vendedor";
hoja_trabajo.Cells[13, 5] = "Nombre del cliente";
hoja_trabajo.Cells[13, 8] = "No. Pedido";
hoja_trabajo.Cells[13, 9] = "Fecha de cotización";
hoja_trabajo.Cells[13, 11] = "T. Cajas";
hoja_trabajo.Cells[13, 12] = "T. Cuadros";
hoja_trabajo.Cells[13, 13] = "T. Placas";
hoja_trabajo.Cells[13, 14] = "T. Abonado";
hoja_trabajo.Cells[13, 15] = "T. Resta";
hoja_trabajo.Cells[13, 16] = "Total";
hoja_trabajo.Cells[13, 17] = "Estatus del pedido";
int hi = 14;
int hj = 2;
for (int i = 0; i < tablaProductos.Rows.Count; i++)
{
for (int j = 0; j < tablaProductos.Columns.Count; j++)
{
if (j == 2)
{
hoja_trabajo.Cells[hi, hj] = tablaProductos.Rows[i].Cells[j].Value.ToString();
hj = hj + 2;
aplicacion.get_Range("E" + hi.ToString(), "G" + hi.ToString()).Merge(true);
}
else if (j == 1)
{
hoja_trabajo.Cells[hi, hj] = tablaProductos.Rows[i].Cells[j].Value.ToString();
hj = hj + 1;
aplicacion.get_Range("C" + hi.ToString(), "D" + hi.ToString()).Merge(true);
}
else if (j == 4)
{
DateTime fecha = DateTime.Parse(tablaProductos.Rows[i].Cells[j].Value.ToString());
hoja_trabajo.Cells[hi, hj] = fecha.ToString("yyyy-MM-dd");
//hoja_trabajo.Range[hi, hj].Style.Color = Color.LightBlue;
hj = hj + 1;
aplicacion.get_Range("I" + hi.ToString(), "J" + hi.ToString()).Merge(true);
}
else if (j == 4)
{
DateTime fecha = DateTime.Parse(tablaProductos.Rows[i].Cells[j].Value.ToString());
hoja_trabajo.Cells[hi, hj] = fecha.ToString("yyyy-MM-dd");
//hoja_trabajo.Range[hi, hj].Style.Color = Color.LightBlue;
hj = hj + 1;
aplicacion.get_Range("I" + hi.ToString(), "J" + hi.ToString()).Merge(true);
}
else if (j == 11)
{
hoja_trabajo.Cells[hi, hj] = tablaProductos.Rows[i].Cells[j].Value.ToString();
hj = hj + 1;
aplicacion.get_Range("Q" + hi.ToString(), "R" + hi.ToString()).Merge(true);
}
else if (j == 8 || j == 9 || j == 10)
{
float num = (float)Convert.ToDouble(tablaProductos.Rows[i].Cells[j].Value.ToString());
String numero = string.Format("{0:c2}", num);
hoja_trabajo.Cells[hi, hj] = numero;
}
else
{
hoja_trabajo.Cells[hi, hj] = tablaProductos.Rows[i].Cells[j].Value.ToString();
}
hj++;
}
hj = 2;
hi++;
}
//bordes
inicio = hoja_trabajo.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
ultimo = hoja_trabajo.get_Range("B13", inicio);
Microsoft.Office.Interop.Excel.Borders bordeTotal = ultimo.Borders;
bordeTotal.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
bordeTotal.Weight = 3d;
//
//bold
hoja_trabajo.Range["B13:R13"].Font.Bold = true;
hoja_trabajo.Range["O6:P11"].Font.Bold = true;
hoja_trabajo.Range["O6:Q8"].Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightGreen;
hoja_trabajo.Range["O9:Q11"].Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightYellow;
hoja_trabajo.Range["B13:R13"].Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightGreen;
hoja_trabajo.Range["C6"].Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightSkyBlue;
hoja_trabajo.Range["C11"].Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightSkyBlue;
aplicacion.get_Range("C13", "D13").Merge(true);
aplicacion.get_Range("E13", "G13").Merge(true);
aplicacion.get_Range("I13", "J13").Merge(true);
aplicacion.get_Range("Q13", "R13").Merge(true);
libros_trabajo.SaveAs(fichero.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
libros_trabajo.Close(true);
aplicacion.Quit();
txtGenerando.Text = "";
try {
System.Diagnostics.Process.Start(fichero.FileName);
}
catch {
DialogResult pregunta2;
pregunta2 = MetroFramework.MetroMessageBox.Show(this, "No se puede abrir el documento", "AVISO", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
DialogResult pregunta;
pregunta = MetroFramework.MetroMessageBox.Show(this, "\nDocumento generado con exito\n Guardado en: " + fichero.FileName + " ", "Documento", MessageBoxButtons.OK, MessageBoxIcon.Question);
Cursor.Current = Cursors.Default;
}
else
{
Cursor.Current = Cursors.Default;
txtGenerando.Text = "";
}
}
catch (Exception ex)
{
DialogResult pregunta;
pregunta = MetroFramework.MetroMessageBox.Show(this, "Ya se ha generado este documento", "AVISO" + ex, MessageBoxButtons.OK, MessageBoxIcon.Warning);
Console.WriteLine("Error" + ex);
Cursor.Current = Cursors.Default;
txtGenerando.Text = "";
}
}
else
{
DialogResult pregunta;
pregunta = MetroFramework.MetroMessageBox.Show(this, "No existen datos para generar el documento", "AVISO", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
