public void DataReport(string filename)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workBook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet workSheet = null;
workSheet = workBook.Sheets["Sheet1"];
workSheet = workBook.ActiveSheet;
workSheet.Name = "Sale Report";
app.Columns.ColumnWidth = 30;
app.DisplayAlerts = false;
System.Globalization.CultureInfo _cultureTHInfo = new System.Globalization.CultureInfo("th-TH");
DateTime dt;
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
workSheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (j == 0) // Datetime
{
dt = Convert.ToDateTime(dataGridView1.Rows[i].Cells[j].Value.ToString());
workSheet.Cells[i + 2, j + 1] = dt.ToString("dd MMM yyyy", _cultureTHInfo);
}
else
{
workSheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
}
string lastRow = "E" + Convert.ToString(dataGridView1.Rows.Count);
workSheet.get_Range("A1", lastRow).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
workSheet.get_Range("A1", "E1").Cells.Font.Bold = true; // Header
workBook.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workBook.Close();
app.Quit();
}
private void button1_Click(object sender, System.EventArgs e)
{
Microsoft.Office.Interop.Excel.Application xlexcel = null;
Microsoft.Office.Interop.Excel._Workbook xlWorkbook = null;
Microsoft.Office.Interop.Excel._Worksheet xlWorkSheet = null;
Excel.Range oRng;
try
{
//open existing workbook.
xlexcel.Workbooks.Add("C:\\vehicledet.xlsx");
xlWorkSheet = (Excel._Worksheet) xlWorkbook.ActiveSheet;
//Get a new workbook.
xlWorkbook = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value));
xlWorkSheet = (Excel._Worksheet) xlWorkbook.ActiveSheet;
//Add table headers going cell by cell.
xlWorkSheet.Cells[1, 1] = "Plate Number";
xlWorkSheet.Cells[1, 2] = "Car Model";
xlWorkSheet.Cells[1, 3] = "Car Brand";
xlWorkSheet.Cells[1, 4] = "Mileage";
//Format A1:D1 as bold, vertical alignment = center.
xlWorkSheet.get_Range("A1", "D1").Font.Bold = true;
xlWorkSheet.get_Range("A1", "D1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
// insert text at every last row
int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1;
xlWorkSheet.Cells[_lastRow, 1] = textBox1.Text;
xlWorkSheet.Cells[_lastRow, 2] = textBox2.Text;
xlWorkSheet.Cells[_lastRow, 3] = textBox3.Text;
xlWorkSheet.Cells[_lastRow, 4] = textBox4.Text;
//AutoFit columns A:D.
oRng = xlWorkSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
//Make sure Excel is visible and give the user control of Microsoft Excel's lifetime.
xlexcel.Visible = true;
xlexcel.UserControl = true;
xlWorkbook.Save();
xlWorkbook.Close();
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
/// <summary>
/// Выравнивает столбцы
/// </summary>
/// <param name="sheet"></param>
/// <param name="start"></param>
/// <param name="rows"></param>
/// <param name="col"></param>
public void Col(Microsoft.Office.Interop.Excel._Worksheet sheet, string start, int rows, int col)
{
Excel.Range r = sheet.get_Range(start, System.Reflection.Missing.Value);
r = r.get_Resize(rows, col);
r.Columns.AutoFit();
ReleaseOb(r);
}
public void createDDetailsReport(List <NodeRecord> nodeRecordsList)
{
try {
//Start Excel and get Application object.
oXL1 = new Microsoft.Office.Interop.Excel.Application();
//oXL1.Visible = true;
//Get a new workbook.
oWB1 = (Microsoft.Office.Interop.Excel._Workbook)(oXL1.Workbooks.Add(""));
oSheet1 = (Microsoft.Office.Interop.Excel._Worksheet)oWB1.ActiveSheet;
oSheet1.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait;
int rows = 1;
for (int i = 0; i < nodeRecordsList.Count(); i++)
{
oSheet1.get_Range("A" + rows, "B" + rows).Merge();
oSheet1.Cells[rows, 1].Value2 = nodeRecordsList[i].name;
oSheet1.Cells[rows, 1].Font.Bold = true;
oSheet1.Cells[rows++, 1].Font.Size = 15;
for (int j = 0; j < nodeRecordsList[i].record.Count(); j++)
{
oSheet1.Cells[rows, 1].Value2 = nodeRecordsList[i].record[j].status;
if (nodeRecordsList[i].record[j].status.Equals("Online"))
{
oSheet1.Cells[rows, 1].Font.Color = 0x22EE22;
}
else
{
oSheet1.Cells[rows, 1].Font.Color = 0x000000FF;
}
oSheet1.Cells[rows, 1].Font.Bold = true;
oSheet1.Cells[rows, 2].Font.Bold = true;
oSheet1.Cells[rows++, 2].Value2 = nodeRecordsList[i].record[j].timeDate;
}
}
//oRng1 = oSheet.get_Range("A1", "E1");
//oRng1.EntireColumn.AutoFit();
//oSheet.Columns["A:F"].ColumnWidth = 18;
// oXL.Visible = false;
oXL1.UserControl = false;
oXL1.DisplayAlerts = false;
oWB1.SaveAs(resPath + "Details" + ".xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB1.Close();
reportLabel.Invoke((MethodInvoker) delegate {
reportLabel.Text = "Details Report is saved successfully";
});
} catch (Exception e) {
MessageBoxButtons buttons = MessageBoxButtons.OK;
DialogResult result;
// Displays the MessageBox.
result = MessageBox.Show(e.Message, "Details Report", buttons);
}
}
public static void GenerateSheet(string courseAlias, int sem, string filePath)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
worksheet.Name = "AttendanceSheet";
app.DisplayAlerts = false;
System.Data.DataTable dt = GetStudent(courseAlias, sem);
if (dt.Rows.Count > 0)
{
int count = 1;
int date = 1;
for (int i = 0; i < 33; i++)
{
if (i == 0)
{
worksheet.Cells[1, count] = "RollNo";
}
else if (i == 1)
{
worksheet.Cells[1, count] = "Student Name";
}
else
{
worksheet.Cells[1, count] = date.ToString();
date++;
}
count++;
}
worksheet.Columns.Locked = false;
worksheet.get_Range((object)worksheet.Cells[1, 1], (object)worksheet.Cells[1, 33]).EntireRow.Locked = true;
worksheet.EnableSelection = Excel.XlEnableSelection.xlUnlockedCells;
Missing mv = Missing.Value;
worksheet.Protect(mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv);
count = 2;
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
worksheet.Cells[count, 2] = dt.Rows[i][j].ToString();
}
count++;
}
}
workbook.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
app.Quit();
}
private void mnutripXuatExcel_Click(object sender, EventArgs e)
{
// Creating a Excel object.
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
{
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
//worksheet.Name = "Exported";
//lúc nào cũng có dòng trắng cuối cùng nên mảng luu bao gom có tieu de = row.cout + 1(tieu de) - 1 (dong trang cuo)
object[,] arr = new object[dtgChiThiSX.Rows.Count, dtgChiThiSX.Columns.Count + 1];
for (int c = 0; c < dtgChiThiSX.Columns.Count; c++)
{
arr[0, c] = dtgChiThiSX.Columns[c].HeaderText;
}
int rowindex = 1;
int colindex = 0;
for (int r = 0; r < dtgChiThiSX.Rows.Count - 1; r++)
{
for (int c = 0; c < dtgChiThiSX.Columns.Count; c++)
{
arr[rowindex, colindex] = dtgChiThiSX.Rows[r].Cells[c].Value.ToString();
colindex++;
}
colindex = 0;
rowindex++;
}
Excel.Range c1 = (Excel.Range)worksheet.Cells[1, 1];
Excel.Range c2 = (Excel.Range)worksheet.Cells[1 + dtgChiThiSX.Rows.Count - 1, dtgChiThiSX.Columns.Count + 1];
Excel.Range range = worksheet.get_Range(c1, c2);
range.Value = arr;
workbook.SaveAs(saveDialog.FileName);
excel.Visible = true;
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//excel.Quit();
workbook = null;
excel = null;
worksheet = null;
}
}
private void fillHeaders()
{
oSheet.Cells[1, 1] = "First Name";
oSheet.Cells[1, 2] = "Last Name";
oSheet.Cells[1, 3] = "Employee ID";
cellRow = cellRow + 1;
//Format A1:D1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "C1").Font.Bold = true;
oSheet.get_Range("A1", "C1").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
//AutoFit columns A:D.
oRng = oSheet.get_Range("A1", "C1");
oRng.EntireColumn.AutoFit();
oRng = oSheet.get_Range("F1");
oRng.Formula = "=COUNTIF(A:A,\"*\")";
oRng.Style.locked = true;
numberOfEntries = Convert.ToInt16(oSheet.Cells[1, 6].value2);
}
private void отчетToolStripMenuItem_Click(object sender, EventArgs e)
{
SelectDateRange srd = new SelectDateRange();
srd.dateTimePicker1.Value = (DateTime)db.Invoices.First().sale_date;
DialogResult dr = srd.ShowDialog(this);
DateTime startDate = srd.dateTimePicker1.Value;
DateTime endDate = srd.dateTimePicker2.Value;
List <Invoice> invoices = db.Invoices.Where(i => i.sale_date.Value >= startDate && i.sale_date.Value <= endDate).OrderByDescending(i => i.Product.name).ToList();
//string[] head = { "Код", "Название товара", "Цена за единицу", "Заказано КГ.", "Дата продажи", "Общая стоимость" };
if ((dr == DialogResult.OK) && (0 < invoices.Count))
{
// Load Excel application
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// Create empty workbook
excel.Workbooks.Add();
// Create Worksheet from active sheet
Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet;
try
{
workSheet.Cells[1, "A"] = "Код накладной";
workSheet.Cells[1, "B"] = "Название товара";
workSheet.Cells[1, "C"] = "Цена за единицу";
workSheet.Cells[1, "D"] = "Заказано (КГ.)";
workSheet.Cells[1, "E"] = "Дата покупки";
workSheet.Cells[1, "F"] = "Сумма за покупку";
workSheet.Cells[1, "H"] = "Название товара";
workSheet.Cells[1, "I"] = "Количество заказанного товара";
workSheet.Cells[1, "K"] = "Нужно закупить больше:";
workSheet.Cells[1, "L"] = "Нужно закупить меньше:";
int row = 2;
ArrayList nameArray = new ArrayList();
ArrayList sumArray = new ArrayList();
int sum;
string name;
foreach (Invoice invoice in invoices)
{
workSheet.Cells[row, "A"] = invoice.invoice_code;
workSheet.Cells[row, "B"] = invoice.Product.name;
workSheet.Cells[row, "C"] = string.Format("{0} грн.", invoice.Product.price);
workSheet.Cells[row, "D"] = string.Format("{0} кг.", invoice.quantity);
workSheet.Cells[row, "E"] = invoice.sale_date.Value.Date;
workSheet.Cells[row, "F"] = string.Format("{0} грн.", invoice.price);
name = db.Invoices.Where(nn => nn.product_id == invoice.Product.id && nn.sale_date.Value >= startDate && nn.sale_date.Value <= endDate).First().Product.name.ToString();
sum = (int)db.Invoices.Where(i => i.Product.name == invoice.Product.name && i.sale_date.Value >= startDate && i.sale_date.Value <= endDate).Sum(s => s.quantity);
if (!nameArray.Contains(name))
{
nameArray.Add(name);
}
if (!sumArray.Contains(sum))
{
sumArray.Add(sum);
}
row++;
}
row = 2;
foreach (string nameRow in nameArray)
{
workSheet.Cells[row, "H"] = nameRow;
row++;
}
row = 2;
foreach (int sumRow in sumArray)
{
workSheet.Cells[row, "I"] = sumRow;
row++;
}
Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet.ChartObjects(Type.Missing);
Excel.ChartObject chartObj = chartObjs.Add(100, 20, 150, 200);
Excel.Chart xlChart = chartObj.Chart;
Excel.Range rg = workSheet.get_Range("H2:H" + (row - 1).ToString(), "I2:I" + (row - 1).ToString());
xlChart.ChartType = Excel.XlChartType.xlPieExploded;
xlChart.SetSourceData(rg, Type.Missing);
//rg.FormulaLocal = "MAX(I2:I"+(row-1).ToString();
//workSheet.Cells[2, "M"].Value = "=MAX(I2:I" + (row - 1).ToString();
//workSheet.Cells[2, "N"].Value = "=MIN(I2:I" + (row - 1).ToString();
//for (int i = 2; i < row; i++)
// {
// workSheet.Cells[i, "K"].Value = "=ЕСЛИ(M2=I" + i.ToString() + ";H" + i.ToString() + ";0)";
// }
//for (int i = 2; i < row; i++)
// {
// workSheet.Cells[i, "L"].Value = "=ЕСЛИ(N2=I" + i.ToString() + ";H" + i.ToString() + ";0)";
// }
// rg.FormulaLocal = "MAX(I2:I"+(row-1).ToString();
//rg.FormulaLocal = "MIN(I2:I"+(row-1).ToString();
// Apply some predefined styles for data to look nicely :)
workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
workSheet.Range["H1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
workSheet.Range["K1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
// Define filename
string fileName = string.Format(@"{0}\ExcelData.xlsx", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));
// Save this data as a file
workSheet.SaveAs(fileName);
// Display SUCCESS message
MessageBox.Show(string.Format("Файл '{0}' успешно сохранен!", fileName));
}
catch (Exception exception)
{
MessageBox.Show("Exception",
"Ошибка записи файла!\n" + exception.Message,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// Quit Excel application
excel.Quit();
//excel.Workbooks.Open(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));
// Release COM objects (very important!)
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
}
// Empty variables
excel = null;
workSheet = null;
// Force garbage collector cleaning
GC.Collect();
}
}
else
{
MessageBox.Show("В выбраном диапазоне, нет накладных!");
}
if (dr == DialogResult.Cancel)
{
return;
}
}
/// <summary>
/// Export DataTable to Excel file
/// </summary>
/// <param name="DataTable">Source DataTable</param>
/// <param name="ExcelFilePath">Path to result file name</param>
public static void ExportToExcel(this System.Data.DataTable DataTable, string ExcelFilePath = null)
{
try {
int ColumnsCount;
if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0)
{
throw new Exception("ExportToExcel: Null or empty input table!\n");
}
// load excel, and create a new workbook
Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbooks.Add();
// single worksheet
Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet;
object[] Header = new object[ColumnsCount];
// column headings
for (int i = 0; i < ColumnsCount; i++)
{
Header[i] = DataTable.Columns[i].ColumnName;
}
Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnsCount]));
HeaderRange.Value = Header;
HeaderRange.Font.Bold = true;
// DataCells
int RowsCount = DataTable.Rows.Count;
object[,] Cells = new object[RowsCount, ColumnsCount];
for (int j = 0; j < RowsCount; j++)
{
for (int i = 0; i < ColumnsCount; i++)
{
Cells[j, i] = DataTable.Rows[j][i];
}
}
Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount + 1, ColumnsCount])).Value = Cells;
// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
try {
Worksheet.SaveAs(ExcelFilePath);
Excel.Quit();
} catch (Exception ex) {
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else // no filepath is given
{
Excel.Visible = true;
}
} catch (Exception ex) {
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
public void createColorScaleExcel()
{
try {
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
//oXL.Visible = true;
//Get a new workbook.
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
oSheet.Columns["A:F"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
oSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
oSheet.get_Range("A1:A2", "B1:B2").Merge();
oSheet.Cells[1, 1].Value2 = "From: " + reportDate[0] + " at " + reportTimePeriod[0] + "\nTo: " + reportDate[1] + " at " + reportTimePeriod[1];
int m = 3;
for (int i = 0; i < nodePathInfoList.Count(); i++)
{
oSheet.Cells[m, 1].Value2 = nodePathInfoList[i].portName + ":";
oSheet.Cells[m, 1].Font.Bold = true;
oSheet.Cells[m, 1].Font.Size = 20;
oSheet.Cells[m, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
oSheet.get_Range("A" + m.ToString(), "B" + m.ToString()).Merge();
// Down to top counter
int z = nodePathInfoList[i].nodePathData.Count() - 1;
for (int j = 0; j < nodePathInfoList[i].nodePathData.Count(); j++)
{
if (j != 0 && j % 6 == 0)
{
m += 4;
}
oSheet.Cells[m + 1, (j % 6) + 1].Value2 = nodePathInfoList[i].nodePathData[z].ElementAt(1);
oSheet.Cells[m + 2, (j % 6) + 1].Value2 = nodePathInfoList[i].nodePathData[z].ElementAt(2);
oSheet.Cells[m + 3, (j % 6) + 1].Value2 = nodePathInfoList[i].nodePathData[z].ElementAt(3);
z--;
// Set all borders for table
oSheet.Cells[m + 1, (j % 6) + 1].Borders.Color = System.Drawing.Color.Black.ToArgb();
oSheet.Cells[m + 2, (j % 6) + 1].Borders.Color = System.Drawing.Color.Black.ToArgb();
oSheet.Cells[m + 3, (j % 6) + 1].Borders.Color = System.Drawing.Color.Black.ToArgb();
// Create a color scale for third row
cfColorScale = (ColorScale)(oSheet.get_Range("A" + (m + 3).ToString(), "F" + (m + 3).ToString()).FormatConditions.AddColorScale(2));
// Min and Max color
cfColorScale.ColorScaleCriteria[1].FormatColor.Color = 0x000000FF; // Red
cfColorScale.ColorScaleCriteria[2].FormatColor.Color = 0x0000FF00; // Green
// Set table font size and bold
oSheet.get_Range("A" + (m + 1).ToString(), "F" + (m + 1).ToString()).Font.Size = 11;
oSheet.get_Range("A" + (m + 2).ToString(), "F" + (m + 2).ToString()).Font.Size = 11;
oSheet.get_Range("A" + (m + 3).ToString(), "F" + (m + 3).ToString()).Font.Size = 11;
oSheet.get_Range("A" + (m + 1).ToString(), "F" + (m + 1).ToString()).Font.Bold = true;
oSheet.get_Range("A" + (m + 2).ToString(), "F" + (m + 2).ToString()).Font.Bold = true;
oSheet.get_Range("A" + (m + 3).ToString(), "F" + (m + 3).ToString()).Font.Bold = true;
}
m += 6;
}
oRng = oSheet.get_Range("A1", "E1");
oRng.EntireColumn.AutoFit();
oSheet.Columns["A:F"].ColumnWidth = 18;
// oXL.Visible = false;
oXL.UserControl = false;
oXL.DisplayAlerts = false;
oWB.SaveAs(resPath + ".xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close();
reportLabel.Invoke((MethodInvoker) delegate {
reportLabel.Text = "Report is saved successfully";
});
} catch (Exception e) {
MessageBoxButtons buttons = MessageBoxButtons.OK;
DialogResult result;
// Displays the MessageBox.
result = MessageBox.Show(e.Message, "Reports", buttons);
}
}
//
//Отчет по картинам на экспозиции
//
private void bExpToPic_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Лист1"];
worksheet = workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Отчет";
// storing header part in Excel
int cell = 1;
for (int i = 0; i < dGVExpToPic.Columns.Count; i++)
{
if (dGVExpToPic.Columns[i].Visible == true)
{
worksheet.Cells[1, cell] = dGVExpToPic.Columns[i].HeaderText;
cell++;
}
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dGVExpToPic.Rows.Count; i++)
{
for (int j = 0; j < dGVExpToPic.Columns.Count; j++)
{
if (dGVExpToPic.Columns[j].Visible == true)
{
worksheet.Cells[i + 2, j] = dGVExpToPic.Rows[i].Cells[j].Value.ToString();
}
}
}
Excel.Range cellRange = (Excel.Range)worksheet.Cells[1, 1];
Excel.Range rowRange = cellRange.EntireRow;
Excel.Range colRange = cellRange.EntireColumn;
rowRange.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
colRange.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
Excel.Range excelCells = (Excel.Range)worksheet.get_Range("B1").Cells;
excelCells.Merge(Type.Missing);
Expositions ex1 = (Expositions)cbExposition.SelectedItem;
worksheet.Cells[1, 2] = "Перечень картин на " + ex1.Name;
Excel.Range range1 = worksheet.UsedRange;
range1.Font.Name = "Times New Roman";
range1.Font.Size = 12;
// выравниваем колонки, чтобы все данные были на виду
range1.EntireColumn.AutoFit();
range1.EntireRow.AutoFit();
range1.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
range1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// Расставляем рамки со всех сторон
range1.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
range1.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;
//range1.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick, Excel.XlColorIndex.xlColorIndexAutomatic);
app.Visible = true;
// save the application
//workbook.SaveAs("C:\\Users\\Banana\\Desktop\\Report\\dbExportPicToExp.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Exit from the application
//app.Quit();
}
public void exportDataExcel(DataSet dsTemp, string reportName)
{
ArrayList paramArraylist = new ArrayList();
string Excelpath = ConfigurationSettings.AppSettings["ExcelFile"] + "CarrierReports.xlsx";
string dateTime = "CarrierReports " + System.DateTime.Now.Year.ToString() + System.DateTime.Now.Month.ToString() + System.DateTime.Now.Day.ToString() + System.DateTime.Now.Hour.ToString() + System.DateTime.Now.Minute.ToString() + System.DateTime.Now.Second.ToString() + ".xlsx";
string Destpath = ConfigurationSettings.AppSettings["ExcelFile"] + dateTime;
DataSet DsExportedData = (DataSet)dsTemp;
File.Copy(Excelpath, Destpath);
Microsoft.Office.Interop.Excel.ApplicationClass XcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel._Workbook workbook = XcelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Workbook workbookDummy = XcelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
Microsoft.Office.Interop.Excel.Range chartrange;
#region Excel
//workbook = XcelApp.Workbooks.Open(Destpath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true); SVN
try
{
if (DsExportedData != null)
{
if (DsExportedData.Tables[0] != null)
{
#region TemplateData
worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["TemplateData"];
//worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
DataTable dtTemplateData = (DataTable)DsExportedData.Tables[0];
for (int j = 0; j < dtTemplateData.Rows.Count; j++)
{
for (int k = 0; k < dtTemplateData.Columns.Count; k++)
{
worksheet.Cells[j + 2, k + 1] = dtTemplateData.Rows[j].ItemArray[k].ToString();
}
}
chartrange = worksheet.get_Range("A1", "D1");
chartrange.Font.Bold = true;
chartrange.Cells.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
chartrange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
chartrange.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
XcelApp.Columns.AutoFit();
workbook.Save();
#endregion
}
if (DsExportedData.Tables[1] != null)
{
#region TemplateBillingData
worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["TemplateBillingData"];
//worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
DataTable dtTemplateBillingData = (DataTable)DsExportedData.Tables[1];
for (int j = 0; j < dtTemplateBillingData.Rows.Count; j++)
{
for (int k = 0; k < dtTemplateBillingData.Columns.Count; k++)
{
worksheet.Cells[j + 2, k + 1] = dtTemplateBillingData.Rows[j].ItemArray[k].ToString();
}
}
chartrange = worksheet.get_Range("A1", "E1");
chartrange.Font.Bold = true;
chartrange.Cells.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
chartrange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
chartrange.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
XcelApp.Columns.AutoFit();
workbook.RefreshAll();
workbook.Save();
#endregion
}
else
{
}
}
else
{
}
// workbook.Close(); SVN
}
catch (Exception ex)
{
// workbook.Close(); SVN
ErrorHandler.WriteError(ex.Message);
}
#endregion
}
public void ExportToExcel(List <FaturaOrnegi> faturas)
{
// Load Excel application
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//excel.Visible = false;
Microsoft.Office.Interop.Excel.Range excelCell;
excel.DisplayAlerts = false;
// Create empty workbook
excel.Workbooks.Add();
// Create Worksheet from active sheet
Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet;
// I created Application and Worksheet objects before try/catch,
// so that i can close them in finnaly block.
// It's IMPORTANT to release these COM objects!!
try
{
// ------------------------------------------------
// Creation of header cells
// ------------------------------------------------
workSheet.Cells[1, "A"] = "Hesap no";
workSheet.Cells[1, "B"] = "Dönem";
workSheet.Cells[1, "C"] = "Kurulu Güç";
workSheet.Cells[1, "D"] = "Sözleşme Gücü";
workSheet.Cells[1, "E"] = "Enerji Bedeli Tüketim";
workSheet.Cells[1, "F"] = "Enerji Bedeli Birim Fiyat";
workSheet.Cells[1, "G"] = "Enerji Bedeli Tutar";
workSheet.Cells[1, "H"] = "Endüktif Tüketim";
workSheet.Cells[1, "I"] = "Endüktif Birim Fiyat";
workSheet.Cells[1, "J"] = "Endüktif Tutar";
workSheet.Cells[1, "K"] = "Kapasitif Tüketim";
workSheet.Cells[1, "L"] = "Kapasitif Birim Fiyat";
workSheet.Cells[1, "M"] = "Kapasitif Tutar";
workSheet.Cells[1, "N"] = "Vergi No";
workSheet.Cells[1, "O"] = "Müşteri Grubu";
workSheet.Cells[1, "P"] = "Dosya Adı";
// ------------------------------------------------
// Populate sheet with some real data from "Faturalar" list
// ------------------------------------------------
int row = 2; // start row (in row 1 are header cells)
foreach (FaturaOrnegi fatura in faturas)
{
workSheet.Cells[row, "A"] = fatura.hesapNo;
workSheet.Cells[row, "B"] = fatura.donem;
workSheet.Cells[row, "C"] = fatura.kuruluGuc;
workSheet.Cells[row, "D"] = fatura.sozlesmeGucu;
workSheet.Cells[row, "E"] = fatura.enerjiBedeliTuketim;
workSheet.Cells[row, "F"] = fatura.enerjiBedeliBirim;
workSheet.Cells[row, "G"] = fatura.enerjiBedeli;
workSheet.Cells[row, "H"] = fatura.enduktifTuketim;
workSheet.Cells[row, "I"] = fatura.enduktifBirim;
workSheet.Cells[row, "J"] = fatura.enduktif;
workSheet.Cells[row, "K"] = fatura.kapasitifTuketim;
workSheet.Cells[row, "L"] = fatura.kapasitifBirim;
workSheet.Cells[row, "M"] = fatura.kapasitif;
workSheet.Cells[row, "N"] = fatura.vergiNo;
workSheet.Cells[row, "O"] = fatura.musteriGrubu;
excelCell = (Microsoft.Office.Interop.Excel.Range)workSheet.get_Range(("P" + row), ("P" + row));
workSheet.Hyperlinks.Add(excelCell, fatura.faturaYolu, Type.Missing, Type.Missing, fatura.faturaYolu.Substring(1 + fatura.faturaYolu.LastIndexOf(@"\")));
row++;
}
// Apply some predefined styles for data to look nicely :)
workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
// Save this data as a file
workSheet.SaveAs(excelPath);
// Display SUCCESS message
MessageBox.Show(string.Format("The file '{0}' is saved successfully!", excelPath));
}
catch (Exception exception)
{
MessageBox.Show("Exception",
"There was a PROBLEM saving Excel file!\n" + exception.Message,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// Quit Excel application
excel.Quit();
// Release COM objects (very important!)
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
}
// Empty variables
excel = null;
workSheet = null;
// Force garbage collector cleaning
GC.Collect();
}
}
