static void Main(string[] args)
{
Console.WriteLine("---- Reading Data ----");
employees = new List <Employee>();
//Create COM Objects. Create a COM object for everything that is referenced
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
//change below path according to your system the employee file is zipped along with
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\employee.csv");
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
Employee emp;
for (int i = 1; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
if (i != 1 & i <= 17)
{
emp = new Employee();
emp.Lastname = xlRange.Cells[i, j].Value2;
emp.Firstname = xlRange.Cells[i, j + 1].Value2;
emp.DateOfBirth = DateTime.FromOADate(xlRange.Cells[i, j + 2].Value2);
var today = DateTime.Now;
var m = (today.Year * 100 + today.Month) * 100 + emp.DateOfBirth.Day;
var y = (emp.DateOfBirth.Year * 100 + emp.DateOfBirth.Month) * 100 + emp.DateOfBirth.Day;
emp.age = (m - y) / 10000;
emp.Group = xlRange.Cells[i, j + 3].Value2;
emp.Money = double.Parse(xlRange.Cells[i, j + 4].Value2.ToString());
employees.Add(emp);
break;
//employees.Add(new Employee { Lastname = (xlRange.Cells[i, j]).Value2,Firstname= (xlRange.Cells[i, j]).Value2,DateOfBirth= });
}
}
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
Console.WriteLine("Read Success");
StringBuilder sb = new StringBuilder();
int num_employeA = 0, num_employeB = 0, num_employeC = 0, num_employeD = 0;
double a, b, c, d;
a = employees.Where(item => item.Group == "A").Sum(item => item.Money);
b = employees.Where(item => item.Group == "B").Sum(item => item.Money);
c = employees.Where(item => item.Group == "C").Sum(item => item.Money);
d = employees.Where(item => item.Group == "D").Sum(item => item.Money);
num_employeA = employees.Where(item => item.Group == "A").Count();
num_employeB = employees.Where(item => item.Group == "B").Count();
num_employeC = employees.Where(item => item.Group == "C").Count();
num_employeD = employees.Where(item => item.Group == "D").Count();
var line = string.Format("{0},{1},{2},{3},{4},{5}", "Group", "num_employee", "min_age", "max_age", "ave_age", "sum_money");
sb.AppendLine(line);
line = string.Format("{0},{1},{2},{3},{4},{5}", "A", num_employeA, employees.Where(item => item.Group == "A").Min(item => item.age), employees.Where(item => item.Group == "A").Max(item => item.age), employees.Where(item => item.Group == "A").Average(item => item.age), a);
sb.AppendLine(line);
line = string.Format("{0},{1},{2},{3},{4},{5}", "B", num_employeB, employees.Where(item => item.Group == "B").Min(item => item.age), employees.Where(item => item.Group == "B").Max(item => item.age), employees.Where(item => item.Group == "B").Average(item => item.age), b);
sb.AppendLine(line);
line = string.Format("{0},{1},{2},{3},{4},{5}", "C", num_employeC, employees.Where(item => item.Group == "C").Min(item => item.age), employees.Where(item => item.Group == "C").Max(item => item.age), employees.Where(item => item.Group == "C").Average(item => item.age), c);
sb.AppendLine(line);
line = string.Format("{0},{1},{2},{3},{4},{5}", "D", num_employeD, employees.Where(item => item.Group == "D").Min(item => item.age), employees.Where(item => item.Group == "D").Max(item => item.age), employees.Where(item => item.Group == "D").Average(item => item.age), d);
sb.AppendLine(line);
line = string.Format("{0},{1},{2},{3},{4},{5}", "Total", num_employeA + num_employeB + num_employeC + num_employeD,
(employees.Where(item => item.Group == "A").Min(item => item.age) + employees.Where(item => item.Group == "B").Min(item => item.age) + employees.Where(item => item.Group == "C").Min(item => item.age) + employees.Where(item => item.Group == "D").Min(item => item.age)),
(employees.Where(item => item.Group == "A").Max(item => item.age) + employees.Where(item => item.Group == "B").Max(item => item.age) + employees.Where(item => item.Group == "C").Max(item => item.age) + employees.Where(item => item.Group == "D").Max(item => item.age)),
(employees.Where(item => item.Group == "A").Average(item => item.age) + employees.Where(item => item.Group == "B").Average(item => item.age) + employees.Where(item => item.Group == "C").Average(item => item.age) + employees.Where(item => item.Group == "D").Average(item => item.age)),
(a / num_employeA) + (b / num_employeB) + (c / num_employeC) + (d / num_employeD));
sb.AppendLine(line);
File.WriteAllText("group.csv", sb.ToString());
Console.WriteLine("Write Success");
Console.ReadKey();
}
private void button1_Click(object sender, EventArgs e)
{
//khoi tao excel
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
//khoi taoWorbppk
Microsoft.Office.Interop.Excel._Workbook worbook = app.Workbooks.Add(Type.Missing);
//khoi tao sheet
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
worksheet = worbook.Sheets["Sheet1"];
worksheet = worbook.ActiveSheet;
app.Visible = true;
//khoi tao vào sheet
worksheet.Cells[2, 3] = "BÁO CÁO HÓA ĐƠN VÀ LỢI NHUẬN";
worksheet.Cells[4, 3] = "STT";
worksheet.Cells[4, 4] = "MaHD";
worksheet.Cells[4, 5] = "Ngày Lập HD";
worksheet.Cells[4, 6] = "Tổng Tiền";
for (int i = 0; i < dgv_baocao.Rows.Count; i++)
{
for (int j = 0; j < 4; j++)
{
worksheet.Cells[i + 5, j + 3] = dgv_baocao.Rows[i].Cells[j].Value;
}
}
int cdai = dgv_baocao.RowCount;
worksheet.Cells[cdai + 5, 5] = "Tổng Tiền:";
worksheet.Cells[cdai + 5, 6] = lbltongdoanhthu.Text;
worksheet.Cells[cdai + 6, 5] = "Tổng tiền nguyên liệu: ";
worksheet.Cells[cdai + 6, 6] = lbltongngyuenlieu.Text;
worksheet.Cells[cdai + 7, 5] = "Tổng lợi nhuận: ";
worksheet.Cells[cdai + 7, 6] = lbltongloinhuan.Text;
//định dạng trang
worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
worksheet.PageSetup.LeftMargin = 0;
worksheet.PageSetup.RightMargin = 0;
worksheet.PageSetup.TopMargin = 0;
worksheet.PageSetup.BottomMargin = 0;
//định dạng cột
worksheet.Range["C1"].ColumnWidth = 8.25;
worksheet.Range["D1"].ColumnWidth = 8.25;
worksheet.Range["E1"].ColumnWidth = 28;
worksheet.Range["F1"].ColumnWidth = 15;
//định dạng font
worksheet.Range["C2", "F100"].Font.Name = "Times New Roman";
worksheet.Range["C2", "F100"].Font.Size = 14;
worksheet.Range["C2", "F2"].MergeCells = true;
worksheet.Range["C2", "F2"].Font.Bold = true;
worksheet.Range["C4", "F4"].Font.Bold = true;
//ke bảng
worksheet.Range["C4", "F" + (cdai + 4)].Borders.LineStyle = 1;
worksheet.Range["C2", "F2"].HorizontalAlignment = 3;
worksheet.Range["C4", "F4"].HorizontalAlignment = 3;
worksheet.Range["C5", "C" + (cdai + 4)].HorizontalAlignment = 3;
worksheet.Range["D4", "D" + (cdai + 4)].HorizontalAlignment = 3;
worksheet.Range["E5", "E" + (cdai + 4)].HorizontalAlignment = 3;
worksheet.Range["F5", "F100"].NumberFormat = "#,##0";
}
private void btnSectors_Insert_Click(object sender, RoutedEventArgs e)
{
Thread th = new Thread(() =>
{
SectorButtonContent = " Učitavanje EXCEL fajla... ";
SectorButtonEnabled = false;
DateTime createTime = DateTime.Now;
List <AgencyViewModel> agencies = new List <AgencyViewModel>();
List <SectorViewModel> sectors = new List <SectorViewModel>();
#region Excel
OpenFileDialog oDlg = new OpenFileDialog();
oDlg.InitialDirectory = "C:\\";
oDlg.Filter = "xlsx Files (*.xlsx)|*.xlsx";
if (true == oDlg.ShowDialog())
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(oDlg.FileName);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int i = 2; i <= rowCount; i++)
{
SectorButtonContent = i + " od " + rowCount;
SectorViewModel sector = new SectorViewModel();
sector.Code = xlRange.Cells[i, 3].Text;
sector.SecondCode = xlRange.Cells[i, 3].Text;
sector.Name = xlRange.Cells[i, 4].Text;
sector.Country = new CountryViewModel()
{
Mark = xlRange.Cells[i, 5].Text
};
sector.Identifier = Guid.NewGuid();
sector.IsSynced = false;
sector.CreatedBy = new UserViewModel()
{
Id = MainWindow.CurrentUserId
};
sector.Company = new CompanyViewModel()
{
Id = MainWindow.CurrentCompanyId
};
sector.CreatedAt = createTime;
sector.UpdatedAt = createTime;
if (sectors.Where(x => x.Code == sector.Code).Count() == 0)
{
sectors.Add(sector);
}
AgencyViewModel agency = new AgencyViewModel();
agency.Code = xlRange.Cells[i, 1].Text;
agency.Name = xlRange.Cells[i, 2].Text;
agency.Country = new CountryViewModel()
{
Mark = xlRange.Cells[i, 5].Text
};
agency.Sector = new SectorViewModel()
{
SecondCode = xlRange.Cells[i, 3].Text
};
agency.Identifier = Guid.NewGuid();
agency.IsSynced = false;
agency.CreatedBy = new UserViewModel()
{
Id = MainWindow.CurrentUserId
};
agency.Company = new CompanyViewModel()
{
Id = MainWindow.CurrentCompanyId
};
agency.CreatedAt = createTime;
agency.UpdatedAt = createTime;
if (agencies.Where(x => x.Code == agency.Code).Count() == 0)
{
agencies.Add(agency);
}
}
}
#endregion
SectorButtonContent = " Unos podataka u toku... ";
SectorButtonEnabled = false;
string apiUrl = BaseApiUrl + "/SeedData/SeedSectors";
string values = JsonConvert.SerializeObject(
sectors,
Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
SendData(apiUrl, values);
apiUrl = BaseApiUrl + "/SeedData/SeedAgencies";
values = JsonConvert.SerializeObject(
agencies,
Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
SendData(apiUrl, values);
SectorButtonContent = " Sektori ";
SectorButtonEnabled = true;
});
th.IsBackground = true;
th.Start();
}
private void AbrirExcel(DataGridView dataGrid)
{
string file = ""; //variable for the Excel File Location
DataTable dt = new DataTable(); //container for our excel data
DataRow row;
OpenFileDialog openFileDialog = new OpenFileDialog();
DialogResult result = openFileDialog.ShowDialog(); // Show the dialog.
if (result == DialogResult.OK) // Check if Result == "OK".
{
file = openFileDialog.FileName; //get the filename with the location of the file
try
{
//Create Object for Microsoft.Office.Interop.Excel that will be use to read excel file
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(file);
Microsoft.Office.Interop.Excel._Worksheet excelWorksheet = excelWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range excelRange = excelWorksheet.UsedRange;
int rowCount = excelRange.Rows.Count; //get row count of excel data
int colCount = excelRange.Columns.Count; // get column count of excel data
//Get the first Column of excel file which is the Column Name
for (int i = 1; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
dt.Columns.Add(excelRange.Cells[i, j].Value2.ToString());
}
break;
}
//Get Row Data of Excel
int rowCounter; //This variable is used for row index number
for (int i = 2; i <= rowCount; i++) //Loop for available row of excel data
{
row = dt.NewRow(); //assign new row to DataTable
rowCounter = 0;
for (int j = 1; j <= colCount; j++) //Loop for available column of excel data
{
//check if cell is empty
if (excelRange.Cells[i, j] != null && excelRange.Cells[i, j].Value2 != null)
{
row[rowCounter] = excelRange.Cells[i, j].Value2.ToString();
}
else
{
row[i] = "";
}
rowCounter++;
}
dt.Rows.Add(row); //add row to DataTable
}
dataGrid.DataSource = dt; //assign DataTable as Datasource for DataGridview
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void btnTaxAdministrations_Insert_Click(object sender, RoutedEventArgs e)
{
Thread th = new Thread(() =>
{
TaxAdministrationButtonContent = " Priprema ... ";
TaxAdministrationButtonEnabled = false;
DateTime createTime = DateTime.Now;
List <TaxAdministrationViewModel> taxAdministrations = new List <TaxAdministrationViewModel>();
#region Excel
OpenFileDialog oDlg = new OpenFileDialog();
oDlg.InitialDirectory = "C:\\";
oDlg.Filter = "xlsx Files (*.xlsx)|*.xlsx";
if (true == oDlg.ShowDialog())
{
TaxAdministrationButtonContent = " Brisanje postojecih podataka ... ";
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(oDlg.FileName);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
string apiUrlDelete = BaseApiUrl + "/SeedData/DeleteTaxAdministrations";
SendData(apiUrlDelete, MainWindow.CurrentCompanyId.ToString());
TaxAdministrationButtonContent = " Učitavanje EXCEL fajla ... ";
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int i = 2; i <= rowCount; i++)
{
TaxAdministrationButtonContent = i + " od " + rowCount;
TaxAdministrationViewModel taxAdministration = new TaxAdministrationViewModel();
taxAdministration.SecondCode = xlRange.Cells[i, 1]?.Text;
taxAdministration.Name = xlRange.Cells[i, 2]?.Text;
taxAdministration.City = new CityViewModel()
{
Name = xlRange.Cells[i, 3]?.Text
};
taxAdministration.Address1 = xlRange.Cells[i, 4]?.Text;
taxAdministration.Address2 = xlRange.Cells[i, 5]?.Text;
taxAdministration.Address3 = xlRange.Cells[i, 6]?.Text;
taxAdministration.Bank1 = new BankViewModel()
{
Name = xlRange.Cells[i, 7]?.Text
};
taxAdministration.IBAN1 = xlRange.Cells[i, 8]?.Text;
taxAdministration.SWIFT = xlRange.Cells[i, 9]?.Text;
taxAdministration.Bank2 = new BankViewModel()
{
Name = xlRange.Cells[i, 10]?.Text
};
taxAdministration.Identifier = Guid.NewGuid();
taxAdministration.IsSynced = false;
taxAdministration.CreatedBy = new UserViewModel()
{
Id = MainWindow.CurrentUserId
};
taxAdministration.Company = new CompanyViewModel()
{
Id = MainWindow.CurrentCompanyId
};
if (taxAdministrations.Where(x => x.SecondCode == taxAdministration.SecondCode).Count() == 0)
{
taxAdministrations.Add(taxAdministration);
if (i % 50 == 0)
{
TaxAdministrationButtonContent = " Unos podataka u toku... ";
TaxAdministrationButtonEnabled = false;
string apiUrlTmp = BaseApiUrl + "/SeedData/SeedTaxAdministrations";
string valuesTmp = JsonConvert.SerializeObject(
taxAdministrations,
Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
SendData(apiUrlTmp, valuesTmp);
taxAdministrations.Clear();
}
}
}
}
#endregion
TaxAdministrationButtonContent = " Unos podataka u toku... ";
TaxAdministrationButtonEnabled = false;
string apiUrl = BaseApiUrl + "/SeedData/SeedTaxAdministrations";
string values = JsonConvert.SerializeObject(
taxAdministrations,
Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
SendData(apiUrl, values);
TaxAdministrationButtonContent = " Poreska uprava ";
TaxAdministrationButtonEnabled = true;
});
th.IsBackground = true;
th.Start();
}
private void btnProfessions_Insert_Click(object sender, RoutedEventArgs e)
{
Thread th = new Thread(() =>
{
ProfessionButtonContent = " Učitavanje EXCEL fajla... ";
ProfessionButtonEnabled = false;
List <ProfessionViewModel> banks = new List <ProfessionViewModel>();
#region Excel
OpenFileDialog oDlg = new OpenFileDialog();
oDlg.InitialDirectory = "C:\\";
oDlg.Filter = "xlsx Files (*.xlsx)|*.xlsx";
if (true == oDlg.ShowDialog())
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(oDlg.FileName);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int i = 2; i <= rowCount; i++)
{
ProfessionViewModel profession = new ProfessionViewModel();
profession.Code = xlRange.Cells[i, 1].Text;
profession.SecondCode = xlRange.Cells[i, 1].Text;
profession.Name = xlRange.Cells[i, 2].Text;
profession.Country = new CountryViewModel()
{
Mark = xlRange.Cells[i, 3].Text
};
profession.Identifier = Guid.NewGuid();
profession.IsSynced = false;
profession.CreatedBy = new UserViewModel()
{
Id = MainWindow.CurrentUserId
};
profession.Company = new CompanyViewModel()
{
Id = MainWindow.CurrentCompanyId
};
profession.CreatedAt = DateTime.Now;
profession.UpdatedAt = DateTime.Now;
banks.Add(profession);
}
}
#endregion
ProfessionButtonContent = " Unos podataka u toku... ";
ProfessionButtonEnabled = false;
string apiUrl = BaseApiUrl + "/SeedData/SeedProfessions";
string values = JsonConvert.SerializeObject(
banks,
Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
SendData(apiUrl, values);
ProfessionButtonContent = " Profesije ";
ProfessionButtonEnabled = true;
});
th.IsBackground = true;
th.Start();
}
public static void saveExcel(SaveFileDialog saveDialog, DataGridView dataGridView1, Datashit recviz)
{
long numDoc = recviz.platNumber2;
string senderRah = recviz.rahunok2;
string edrpou = recviz.ToString();
// 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;
//worksheet.Cells.Style;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "ExportedFromDatGrid";
int cellRowIndex = 1;
int cellColumnIndex = 1;
//Loop through each row and read value from each column.
// worksheet.Cells["D:D"].NumberFormat = "@";
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
switch (cellColumnIndex)
{
case 6:
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Columns[j + 1].HeaderText;
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j + 1].Value.ToString();
}
break;
case 7:
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = "Номер платежу";
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = numDoc++;
}
break;
case 8:
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex].NumberFormat = "@";
worksheet.Cells[cellRowIndex, cellColumnIndex] = "ЄРДПО платника";
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = recviz.edrpou;
}
break;
case 9:
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = "Рахунок платника";
worksheet.Cells[cellRowIndex, cellColumnIndex].NumberFormat = "@";
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = recviz.rahunok2;
}
break;
case 10:
case 11:
break;
default:
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Columns[j].HeaderText;
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex].NumberFormat = "@";
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
break;
}
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
// SaveFileDialog saveDialog = new SaveFileDialog();
// saveDialog.Filter = "Excel files(2003)| *.xls|Excel Files(2007+)|*.xlsx"; ;
// saveDialog.FilterIndex = 2;
//if (saveDialog.ShowDialog() == DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Експорт завершено");
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
public static void ExportFile(Dictionary <AttributeTypeCode, List <entityParam> > entityParam, EntityInfo entityInfo)
{
SaveFileDialog sfd = null;
DataColumns[] fromatedList = FormatDataForExport(entityParam);
String[] columnsHeaderName = new string[] {
"Display Name", "Schema Name", "Type", "Target", "Managed/Unmanaged", "IsAuditable", "IsSearchable", "Required Level", "Introduced Version", "CreatedOn", "Percentage Of Use"
};
int headerIndex = 9;
int lineIndex = headerIndex + 1;
if (fromatedList.Length > 0)
{
sfd = new SaveFileDialog();
sfd.Filter = "Excel (.xlsx)| *.xlsx;*.xls;";
sfd.FileName = entityInfo.entityName + "_EntityKPIsExport_" + DateTime.Now.ToShortDateString().Replace('/', '-') + ".xlsx";
bool fileError = false;
if (sfd.ShowDialog() == DialogResult.OK)
{
if (File.Exists(sfd.FileName))
{
try
{
File.Delete(sfd.FileName);
}
catch (IOException ex)
{
fileError = true;
MessageBox.Show("It wasn't possible to write the data to the disk." + ex.Message);
}
}
if (!fileError)
{
try
{
Microsoft.Office.Interop.Excel._Application XcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = XcelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
#region DataGrid
worksheet = workbook.ActiveSheet;
if (entityInfo.entityName.Length > 21)
{
worksheet.Name = entityInfo.entityName.Substring(0, 21) + "_MetaData";
}
else
{
worksheet.Name = entityInfo.entityName + "_MetaData";
}
if (entityInfo != null)
{
for (int i = 1; i <= 6; i++)
{
worksheet.Cells[i, 1].Font.Bold = true;
worksheet.Cells[i, 1].Interior.Color = Color.Wheat;
worksheet.Cells[i, 1].Font.Size = 12;
}
worksheet.Cells[1, 1] = "Entity Display Name";
worksheet.Cells[1, 2] = entityInfo.entityName;
worksheet.Cells[2, 1] = "Entity Technical Name";
worksheet.Cells[2, 2] = entityInfo.entityTechnicalName;
worksheet.Cells[3, 1] = "CreatedOn";
worksheet.Cells[3, 2] = entityInfo.entityDateOfCreation != DateTime.MinValue ? entityInfo.entityDateOfCreation.ToShortDateString() : String.Empty;
worksheet.Cells[4, 1] = "Number Of Fields";
worksheet.Cells[4, 2] = entityInfo.entityFieldsCount;
worksheet.Cells[5, 1] = "Number Of Records";
worksheet.Cells[5, 2] = entityInfo.entityRecordsCount;
worksheet.Cells[6, 1] = "Entity Fields Volume Usage";
worksheet.Cells[6, 2] = ((entityInfo.entityTotalUseOfColumns * 100) / entityInfo.entityDefaultColumnSize).ToString("0.##\\%");
}
for (int i = 1; i < columnsHeaderName.Length + 1; i++)
{
worksheet.Cells[headerIndex, i] = columnsHeaderName[i - 1];
worksheet.Cells[headerIndex, i].Font.NAME = "Calibri";
worksheet.Cells[headerIndex, i].Font.Bold = true;
worksheet.Cells[headerIndex, i].Interior.Color = Color.Wheat;
worksheet.Cells[headerIndex, i].Font.Size = 12;
}
for (int i = 0; i < fromatedList.Length; i++)
{
worksheet.Cells[i + lineIndex, 1] = fromatedList[i].displayName;
worksheet.Cells[i + lineIndex, 2] = fromatedList[i].fieldName;
worksheet.Cells[i + lineIndex, 3] = fromatedList[i].fieldType;
worksheet.Cells[i + lineIndex, 4] = fromatedList[i].target;
worksheet.Cells[i + lineIndex, 5] = fromatedList[i].isManaged;
worksheet.Cells[i + lineIndex, 6] = fromatedList[i].isAuditable;
worksheet.Cells[i + lineIndex, 7] = fromatedList[i].isSearchable;;
worksheet.Cells[i + lineIndex, 8] = fromatedList[i].requiredLevel;;
worksheet.Cells[i + lineIndex, 9] = fromatedList[i].introducedVersion;
worksheet.Cells[i + lineIndex, 10] = fromatedList[i].dateOfCreation != DateTime.MinValue ? fromatedList[i].dateOfCreation.ToShortDateString() : String.Empty;
worksheet.Cells[i + lineIndex, 11] = fromatedList[i].percentageOfUse.Replace(",", ".");
if (fromatedList[i].target == String.Empty)
{
worksheet.Cells[i + lineIndex, 4].Interior.Color = Color.Gainsboro;
}
}
worksheet.Columns.AutoFit();
#endregion
var xlSheets = workbook.Sheets as Microsoft.Office.Interop.Excel.Sheets;
var xlNewSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(Type.Missing, xlSheets[1], Type.Missing, Type.Missing);
xlNewSheet.Name = "Charts";
#region chartManagedUnmanaged
//add data
xlNewSheet.Cells[2, 2] = "Managed";
xlNewSheet.Cells[2, 2].Font.Color = Color.White;
xlNewSheet.Cells[2, 3] = entityInfo.managedFieldsCount;
xlNewSheet.Cells[2, 3].Font.Color = Color.White;
xlNewSheet.Cells[3, 2] = "Unmanaged";
xlNewSheet.Cells[3, 2].Font.Color = Color.White;
xlNewSheet.Cells[3, 3] = entityInfo.unmanagedFieldsCount;
xlNewSheet.Cells[3, 3].Font.Color = Color.White;
Microsoft.Office.Interop.Excel.Range chartRange;
Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)xlNewSheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(10, 10, 300, 250);
Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;
chartPage.HasTitle = true;
chartPage.ChartTitle.Text = @"Managed\Unmanaged Fields";
chartRange = xlNewSheet.get_Range("B2", "C3");
chartPage.SetSourceData(chartRange, System.Reflection.Missing.Value);
chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlDoughnut;
#endregion
#region EntityFieldsCreated
//add data
xlNewSheet.Cells[2, 10] = "Available Fields To Create";
xlNewSheet.Cells[2, 10].Font.Color = Color.White;
xlNewSheet.Cells[2, 11] = entityInfo.entityDefaultColumnSize - entityInfo.entityTotalUseOfColumns;
xlNewSheet.Cells[2, 11].Font.Color = Color.White;
xlNewSheet.Cells[3, 10] = "Created Fields";
xlNewSheet.Cells[3, 10].Font.Color = Color.White;
xlNewSheet.Cells[3, 11] = entityInfo.entityTotalUseOfColumns;
xlNewSheet.Cells[3, 11].Font.Color = Color.White;
Microsoft.Office.Interop.Excel.Range chartRangeTotaluse;
Microsoft.Office.Interop.Excel.ChartObjects xlChartsTotalUse = (Microsoft.Office.Interop.Excel.ChartObjects)xlNewSheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject totalUseChartChart = (Microsoft.Office.Interop.Excel.ChartObject)xlChartsTotalUse.Add(510, 10, 300, 250);
Microsoft.Office.Interop.Excel.Chart chartPageTotalUse = totalUseChartChart.Chart;
chartPageTotalUse.HasTitle = true;
chartPageTotalUse.ChartTitle.Text = @"Entity Fields Created";
chartRangeTotaluse = xlNewSheet.get_Range("J2", "K3");
chartPageTotalUse.SetSourceData(chartRangeTotaluse, System.Reflection.Missing.Value);
chartPageTotalUse.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlDoughnut;
#endregion
#region CustomStandar
//add data
xlNewSheet.Cells[2, 20] = "Standard Fields";
xlNewSheet.Cells[2, 20].Font.Color = Color.White;
xlNewSheet.Cells[2, 21] = entityInfo.entityStandardFieldsCount;
xlNewSheet.Cells[2, 21].Font.Color = Color.White;
xlNewSheet.Cells[3, 20] = "Custom Fields";
xlNewSheet.Cells[3, 20].Font.Color = Color.White;
xlNewSheet.Cells[3, 21] = entityInfo.entityCustomFieldsCount;
xlNewSheet.Cells[3, 21].Font.Color = Color.White;
Microsoft.Office.Interop.Excel.Range chartRangeCustomStandard;
Microsoft.Office.Interop.Excel.ChartObjects xlChartsCustomStandard = (Microsoft.Office.Interop.Excel.ChartObjects)xlNewSheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject customStandardChart = (Microsoft.Office.Interop.Excel.ChartObject)xlChartsCustomStandard.Add(1010, 10, 300, 250);
Microsoft.Office.Interop.Excel.Chart chartPageCustomStandard = customStandardChart.Chart;
chartPageCustomStandard.HasTitle = true;
chartPageCustomStandard.ChartTitle.Text = @"Custom\Standard Fields";
chartRangeCustomStandard = xlNewSheet.get_Range("T2", "U3");
chartPageCustomStandard.SetSourceData(chartRangeCustomStandard, System.Reflection.Missing.Value);
chartPageCustomStandard.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlDoughnut;
#endregion
#region FieldsType
//add data
int indicator = 0;
foreach (var item in entityParam)
{
xlNewSheet.Cells[indicator + 21, 10] = item.Key.ToString();
xlNewSheet.Cells[indicator + 21, 10].Font.Color = Color.White;
xlNewSheet.Cells[indicator + 21, 11] = item.Value.Count;
xlNewSheet.Cells[indicator + 21, 11].Font.Color = Color.White;
indicator++;
}
Microsoft.Office.Interop.Excel.Range chartRangeFieldType;
Microsoft.Office.Interop.Excel.ChartObjects xlChartsFieldTypes = (Microsoft.Office.Interop.Excel.ChartObjects)xlNewSheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject fieldTypes = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(485, 270, 350, 300);
Microsoft.Office.Interop.Excel.Chart chartPageFieldTypes = fieldTypes.Chart;
chartPageFieldTypes.HasTitle = true;
chartPageFieldTypes.ChartTitle.Text = @"Entity Fields Types";
chartRangeFieldType = xlNewSheet.get_Range("J21", ("K" + (21 + (indicator - 1))).ToString());
chartPageFieldTypes.SetSourceData(chartRangeFieldType, System.Reflection.Missing.Value);
chartPageFieldTypes.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlDoughnut;
#endregion
Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.Worksheets[1];
sheet.Activate();
workbook.SaveAs(sfd.FileName);
XcelApp.Quit();
ReleaseObject(worksheet);
ReleaseObject(xlNewSheet);
ReleaseObject(workbook);
ReleaseObject(XcelApp);
if (MessageBox.Show("Would you like to open it?", "Information", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
Process.Start(sfd.FileName);
}
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex.Message);
}
}
}
}
}
public static void GenerateExcel(DataTable dataTable, string path)
{
dataTable.TableName = "Table1";
DataSet dataSet = new DataSet();
dataSet.Tables.Add(dataTable);
// create a excel app along side with workbook and worksheet and give a name to it
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelWorkBook = excelApp.Workbooks.Add();
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = excelWorkBook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
foreach (DataTable table in dataSet.Tables)
{
//Add a new worksheet to workbook with the Datatable name
// Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
excelWorkSheet.Cells.EntireColumn.NumberFormat = "@";
excelWorkSheet.Name = table.TableName;
// add all the columns
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
excelWorkSheet.Cells[1, i].Font.Bold = true;
excelWorkSheet.Cells[1, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelWorkSheet.Cells[1, i].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excelWorkSheet.Cells[1, i].Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
excelWorkSheet.Cells[1, i].Borders.Weight = 2;
excelWorkSheet.Cells[1, i].Font.Size = 14;
excelWorkSheet.Cells[1, i].ColumnWidth = 22;
}
// add all the rows
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
excelWorkSheet.Cells[j + 2, k + 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelWorkSheet.Cells[j + 2, k + 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelWorkSheet.Cells[j + 2, k + 1].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excelWorkSheet.Cells[j + 2, k + 1].Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
excelWorkSheet.Cells[j + 2, k + 1].Borders.Weight = 2;
excelWorkSheet.Cells[j + 2, k + 1].Font.Size = 12;
excelWorkSheet.Cells[j + 2, k + 1].ColumnWidth = 22;
}
}
}
// excelWorkBook.Save(); -> this will save to its default location
Debug.WriteLine("----->" + path);
excelWorkBook.SaveAs(path); // -> this will do the custom
excelWorkBook.Close();
excelApp.Quit();
}
public int print(bool preview)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//System.IO.Directory.GetCurrentDirectory;
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\BPVBag\SOP-MFG-306-R02A BPV生产前确认记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
// 设置该进程是否可见
//oXL.Visible = true;
// 修改Sheet中某行某列的值
int rowStartAt = 5;
my.Cells[3, 1].Value = "生产日期：" + Convert.ToDateTime(dt记录.Rows[0]["生产日期"]).ToString("yyyy年MM月dd日") + "\t" + fill生产班次();
my.Cells[3, 5].Value = fill生产产品() + "生产指令编号：" + dt记录.Rows[0]["生产指令编号"];
//EVERY SHEET CONTAINS 11 RECORDS
int rowNumPerSheet = 10;
int rowNumTotal = dt记录详情.Rows.Count;
for (int i = 0; i < (rowNumTotal > rowNumPerSheet ? rowNumPerSheet : rowNumTotal); i++)
{
my.Cells[i + rowStartAt, 1].Value = dt记录详情.Rows[i]["序号"];
try
{
my.Cells[i + rowStartAt, 2].Valie = dt记录详情.Rows[i]["确认项目"];
}
catch
{ }
//my.Cells[i + rowStartAt, 2].Value = Convert.ToDateTime(dt记录详情.Rows[i]["生产日期时间"]).ToString("MM/dd HH:mm");
//my.Cells[i + rowStartAt, 2].Font.Size = 11;
//my.Cells[i + rowStartAt, 3].Value = dt记录详情.Rows[i]["领取管数量米"];
my.Cells[i + rowStartAt, 4].Value = dt记录详情.Rows[i]["确认内容"];
//my.Cells[i + rowStartAt, 5].Value = dt记录详情.Rows[i]["切管数量个"];
}
//THIS PART HAVE TO INSERT NOEW BETWEEN THE HEAD AND BOTTM
if (rowNumTotal > rowNumPerSheet)
{
for (int i = rowNumPerSheet; i < rowNumTotal; i++)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)my.Rows[rowStartAt + i, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
my.Cells[i + rowStartAt, 1].Value = dt记录详情.Rows[i]["序号"];
try
{
my.Cells[i + rowStartAt, 2].Valie = dt记录详情.Rows[i]["确认项目"];
}
catch
{ }
//my.Cells[i + rowStartAt, 2].Value = Convert.ToDateTime(dt记录详情.Rows[i]["生产日期时间"]).ToString("MM/dd HH:mm");
//my.Cells[i + rowStartAt, 2].Font.Size = 11;
//my.Cells[i + rowStartAt, 3].Value = dt记录详情.Rows[i]["领取管数量米"];
my.Cells[i + rowStartAt, 4].Value = dt记录详情.Rows[i]["确认内容"];
//my.Cells[i + rowStartAt, 5].Value = dt记录详情.Rows[i]["切管数量个"];
}
}
Microsoft.Office.Interop.Excel.Range range1 = (Microsoft.Office.Interop.Excel.Range)my.Rows[rowStartAt + rowNumTotal, Type.Missing];
range1.EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
//THE BOTTOM HAVE TO CHANGE LOCATE ACCORDING TO THE ROWS NUMBER IN DT.
int varOffset = (rowNumTotal > rowNumPerSheet) ? rowNumTotal - rowNumPerSheet - 1 : 0;
my.Cells[19 + varOffset, 23].Value = "审核员： " + dt记录.Rows[0]["操作员"] + "\t日期： " + Convert.ToDateTime(dt记录.Rows[0]["操作日期"]).ToString("yyyy年MM月dd日") + "审核员： " + dt记录.Rows[0]["审核员"] + "\t日期： " + Convert.ToDateTime(dt记录.Rows[0]["审核日期"]).ToString("yyyy年MM月dd日");
if (preview)
{
my.Select();
oXL.Visible = true; //加上这一行 就相当于预览功能
return(0);
}
else
{
//add footer
my.PageSetup.RightFooter = Instruction + "-10-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count;; // &P 是页码
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
}
catch { }
int pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
oXL = null;
my = null;
wb = null;
return(pageCount);
}
}
/// <summary>
/// Export DataTable to Excel file
/// </summary>
/// <param name="dataTable">Source DataTable</param>
/// <param name="fileName">Path to result file name</param>
public static bool ExportToExcel(this System.Data.DataTable dataTable, string fileName = null)
{
bool bResult = false;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// single worksheet
Microsoft.Office.Interop.Excel._Worksheet worksheet = excel.ActiveSheet;
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
excel.Workbooks.Add();
if (!worksheet.IsNotNullOrEmpty())
{
worksheet = excel.ActiveSheet;
}
object[] header = new object[columnsCount];
header = dataTable.ColumnNames();
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.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
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; // dataTable.AsEnumerable().Select(row => row.ItemArray).ToArray();
worksheet.Columns.AutoFit();
worksheet.Columns[3].NumberFormat = "dd/MM/yyyy";
if (fileName != null && fileName != "")
{
try
{
excel.DisplayAlerts = false;
worksheet.SaveAs(fileName);
bResult = true;
}
catch (Exception ex)
{
ex.LogException();
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)
{
ex.LogException();
throw new Exception("ExportToExcel: \n" + ex.Message);
}
finally
{
excel.Quit();
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet) != 0)
{
}
worksheet = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(bResult);
}
private void btnExcel_Click(object sender, EventArgs e)
{
//Creating DataTable
DataTable dt = new DataTable();
//Adding the Columns with gridview devexpress
foreach (GridColumn c in gridViewTonKho.Columns)
{
dt.Columns.Add(c.FieldName, c.ColumnType);
}
//Adding the Rows with gridview devexpress
for (int r = 0; r < gridViewTonKho.RowCount; r++)
{
object[] rowValues = new object[dt.Columns.Count];
for (int c = 0; c < dt.Columns.Count; c++)
{
rowValues[c] = gridViewTonKho.GetRowCellValue(r, dt.Columns[c].ColumnName);
}
dt.Rows.Add(rowValues);
}
//Exporting to Excel
SaveFileDialog fsave = new SaveFileDialog();
//kiem tra duoi
fsave.Filter = "Excel|*.xls|Excel 2010|*.xlsx";
fsave.ShowDialog();
if (fsave.FileName != null)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet sheet = null;
try
{
sheet = wb.ActiveSheet;
sheet.Name = "WMS_Inventory";
for (int i = 1; i <= dt.Columns.Count; i++)
{
sheet.Cells[1, i] = dt.Columns[i - 1].Caption;
}
for (int i = 1; i <= dt.Rows.Count; i++)
{
sheet.Cells[1 + i, 1] = dt.Rows[i - 1].ItemArray[0];
sheet.Cells[1 + i, 2] = dt.Rows[i - 1].ItemArray[1];
sheet.Cells[1 + i, 3] = dt.Rows[i - 1].ItemArray[2];
sheet.Cells[1 + i, 4] = dt.Rows[i - 1].ItemArray[3];
sheet.Cells[1 + i, 5] = dt.Rows[i - 1].ItemArray[4];
sheet.Cells[1 + i, 6] = dt.Rows[i - 1].ItemArray[5];
sheet.Cells[1 + i, 7] = dt.Rows[i - 1].ItemArray[6];
sheet.Cells[1 + i, 8] = dt.Rows[i - 1].ItemArray[7];
sheet.Cells[1 + i, 9] = dt.Rows[i - 1].ItemArray[8];
//sheet.Cells[1 + i, 10] = dt.Rows[i - 1].ItemArray[9];
//sheet.Cells[1 + i, 11] = dt.Rows[i - 1].ItemArray[10];
}
sheet.Columns.AutoFit();
sheet.Rows.AutoFit();
wb.SaveAs(fsave.FileName);
MessageBox.Show("Xuất dữ liệu thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
app.Quit();
wb = null;
}
}
}
private void export()
{
try
{
#region ===========khởi tạo excel=====
//khởi tạo excell
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
//khởi tạo workbook
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
//khởi tọa worksheet
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Danh sách hoc sinh";
app.Visible = true;//cho hiển thị excel
#endregion ===========khởi tạo excel==========
#region ===========đổ dữ liệu vào sheet======
worksheet.Cells[1, 1] = "SỞ GIÁO DỤC VÀ ĐÀO TẠO HÀ NỘI";
worksheet.Cells[2, 1] = "TRƯỜNG MẦM NON HOA LINH";
worksheet.Cells[3, 8] = "Ngày " + DateTime.Today.Day + " tháng " + DateTime.Today.Month + " năm " + DateTime.Today.Year + "";
worksheet.Cells[4, 1] = "DANH SÁCH HỌC SINH";
worksheet.Cells[5, 1] = "Năm học:" + cbbNamhoc.Text;
worksheet.Cells[6, 1] = "Khối:" + cbbKhoihoc.Text;
worksheet.Cells[7, 1] = "Lớp:" + cbbLophoc.Text;
worksheet.Cells[9, 1] = "STT";
worksheet.Cells[9, 2] = "Mã học sinh";
worksheet.Cells[9, 3] = "Họ";
worksheet.Cells[9, 4] = "Tên";
worksheet.Cells[9, 5] = "Ngày sinh";
worksheet.Cells[9, 6] = "Giới tính";
worksheet.Cells[9, 7] = "Địa chỉ";
worksheet.Cells[9, 8] = "Tình trạng";
worksheet.Cells[9, 9] = "Tổng thu";
//duyệt dết các dòng trong trong gridcontrol
for (int i = 0; i < grDanhSachHocSinh.RowCount; i++)
{
#region ==== tinh tien hoc tung hoc sinh======
ReceivableDetail_StudentDAO dt = new ReceivableDetail_StudentDAO();
ReceivableDetailDAO dc = new ReceivableDetailDAO();
List <ReceivableDetail_Student> a = new List <ReceivableDetail_Student>();
List <ReceivableDetail> b = new List <ReceivableDetail>();
decimal tong = 0;
int perferredID = (int)grDanhSachHocSinh.GetRowCellValue(i, grDanhSachHocSinh.Columns["PreferredID"]);
a = dt.ListReceivableDetail_Student((int)grDanhSachHocSinh.GetRowCellValue(i, grDanhSachHocSinh.Columns["StudentID"]));
//b = dc.ListReceivableDetail((int)cbbDotthu.SelectedValue);
foreach (var j in a)
{
ReceivableDetail c = new ReceivableDetail();
c = dc.ReceivableDetaiByStudenID(j.ReceivableDetailID, (int)cbbDotthu.SelectedValue);
if (c != null)
{
b.Add(c);
}
}
foreach (var j in b)
{
string mg = j.PreferredID;
if (mg != "")
{
List <string> b1 = new List <string>();
for (int sj = 0; sj < (mg.Length - 1); sj += 2)
{
string c = mg.Substring(sj, 1);
b1.Add(c);
}
for (int k = 0; k < b1.Count; k++)
{
if (int.Parse(b1[k].ToString()) == perferredID)
{
PreferredDAO dv = new PreferredDAO();
float pr = dv.lookPreferredPercent(perferredID);
tong += (decimal)j.TotalPriceDetail;
tong = tong - (((decimal)j.TotalPriceDetail * (decimal)pr) / 100);
//worksheet.Cells[10 + i, 9] = tong;
break;
}
if (k == b1.Count - 1)
{
tong += (decimal)j.TotalPriceDetail;
}
}
//tong += (decimal)j.TotalPriceDetail;
}
else
{
tong += (decimal)j.TotalPriceDetail;
}
}
worksheet.Cells[10 + i, 9] = tong;
// grDanhsachkhoanthu.DataSource = b;
#endregion ==== tinh tien hoc tung hoc sinh====
#region ---- thong tin hoc sinh----
worksheet.Cells[10 + i, 1] = i + 1;
worksheet.Cells[10 + i, 2] = grDanhSachHocSinh.GetRowCellValue(i, grDanhSachHocSinh.Columns["StudentCode"]);
worksheet.Cells[10 + i, 3] = grDanhSachHocSinh.GetRowCellValue(i, grDanhSachHocSinh.Columns["FirstName"]);
worksheet.Cells[10 + i, 4] = grDanhSachHocSinh.GetRowCellValue(i, grDanhSachHocSinh.Columns["LastName"]);
worksheet.Cells[10 + i, 5] = grDanhSachHocSinh.GetRowCellValue(i, grDanhSachHocSinh.Columns["Birthday"]);
worksheet.Cells[10 + i, 6] = grDanhSachHocSinh.GetRowCellValue(i, grDanhSachHocSinh.Columns["Gender"]);
worksheet.Cells[10 + i, 7] = grDanhSachHocSinh.GetRowCellValue(i, grDanhSachHocSinh.Columns["AdressDetail"]);
worksheet.Cells[10 + i, 8] = grDanhSachHocSinh.GetRowCellValue(i, grDanhSachHocSinh.Columns["tinhtrang"]);
#endregion ---thong tin hoc sinh------
}
int dongData = grDanhSachHocSinh.RowCount;
worksheet.Cells[dongData + 13, 8] = "Hà Nội, ngày tháng năm . ";
worksheet.Cells[dongData + 14, 8] = "HIỆU TRƯỞNG. ";
#endregion ============đổ dữ liệu vào sheet=======
#region =====căn chỉnh======
//định dạng trang
worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape; // Giấy dọc
worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4; // Loại giấy A4
worksheet.PageSetup.LeftMargin = 0; //can le trai
worksheet.PageSetup.TopMargin = 0;
worksheet.PageSetup.RightMargin = 0;
worksheet.PageSetup.BottomMargin = 0;
worksheet.PageSetup.CenterHorizontally = true; //can giua theo chieu ngang
//dinh dang cot
worksheet.Range["A1"].ColumnWidth = 3;
worksheet.Range["B1"].ColumnWidth = 13;
worksheet.Range["C1"].ColumnWidth = 13;
worksheet.Range["D1"].ColumnWidth = 8;
worksheet.Range["E1"].ColumnWidth = 13;
worksheet.Range["F1"].ColumnWidth = 10;
worksheet.Range["G1"].ColumnWidth = 28;
worksheet.Range["H1"].ColumnWidth = 14;
worksheet.Range["I1"].ColumnWidth = 11;
worksheet.Range["J1"].ColumnWidth = 14;
worksheet.Range["K1"].ColumnWidth = 11;
//dinh dang font chu
worksheet.Range["A1", "K100"].Font.Name = "Times New Roman";
worksheet.Range["A1", "K100"].Font.Size = 10; // size cho font chữ
worksheet.Range["A4", "K4"].Font.Size = 12; // Size tiêu đề lớn hơn chút
worksheet.Range["A1", "K2"].Font.Size = 16; // Size tiêu đề lớn hơn chút
worksheet.Range["A5", "K7"].Font.Size = 12;
worksheet.Range["A1", "I1"].MergeCells = true; // Nhập dòng tiêu đề
worksheet.Range["A2", "I2"].MergeCells = true;
worksheet.Range["H3", "I3"].MergeCells = true;
worksheet.Range["A4", "I4"].MergeCells = true;
worksheet.Range["A5", "I5"].MergeCells = true;
worksheet.Range["A6", "I6"].MergeCells = true;
worksheet.Range["A7", "I7"].MergeCells = true;
worksheet.Range["A1", "K7"].Font.Bold = true; //to dam tieu de
worksheet.Range["A9", "K9"].Font.Bold = true; //to dam ten cot
worksheet.Range["A9", "I" + (dongData + 9)].Borders.LineStyle = 1; //ke vien bang
worksheet.Range["A3", "A9"].HorizontalAlignment = 3; // Căn giữa tiêu đề bảng
worksheet.Range["A8", "K8"].HorizontalAlignment = 3; // Tiêu đề cột bảng căn giữa
worksheet.Range["A4", "K4"].HorizontalAlignment = 3;
worksheet.Range["A5", "K5"].HorizontalAlignment = 3;
worksheet.Range["A6", "K6"].HorizontalAlignment = 3;
#endregion ====căn chỉnh=====
}
catch
{
}
}
private void SaveBTN_Click(object sender, EventArgs e)
{
string savetext = "";
using (FolderBrowserDialog fbd = new FolderBrowserDialog()
{
Description = "Select your path"
})
{
if (fbd.ShowDialog() == DialogResult.OK)
{
savetext = fbd.SelectedPath;
}
}
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;
app.Visible = false;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "ImportedProducts";
for (int i = 1; i < ProductsGridView.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = ProductsGridView.Columns[i - 1].HeaderText;
}
for (int i = 0; i < ProductsGridView.Rows.Count; i++)
{
for (int j = 0; j < ProductsGridView.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = ProductsGridView.Rows[i].Cells[j].Value.ToString();
}
}
plus++;
FileStream sb = new FileStream("ImportedPlusNumber.txt", FileMode.OpenOrCreate);
StreamWriter sw = new StreamWriter(sb);
sw.Write(plus);
sw.Close();
using (StreamReader st = new StreamReader("ImportedPlusNumber.txt"))
{
string text = st.ReadToEnd();
var result = int.Parse(text);
plus = result;
}
string excelname = (savetext + $"/ImportedProducts{plus}.xlsx");
try
{
workbook.SaveAs(excelname, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception)
{
cMessageBox.Show("ეს ფაილი უკვე არსებობს!");
}
workbook.Close();
}
public static void exportexcel(System.Data.DataTable dt, string filename, string charttitle, bool tableflag, bool bl)
{
//System.Data.DataTable dt = new System.Data.DataTable();
if (dt == null)
{
return;
}
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel.Series oSeries;
//Microsoft.Office.Interop.Excel.Range oResizeRange;
Microsoft.Office.Interop.Excel._Chart oChart;
//String sMsg;
//int iNumQtrs;
GC.Collect();//系统的垃圾回收
//string filename = @"C:\Documents and Settings\tongxl\桌面\nnn.xls";
//Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(filename);
Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(true);
if (ep == null)
{
MsgBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
ep.Visible = true;
Microsoft.Office.Interop.Excel.Sheets sheets = wb.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet ws = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);// [System.Type.Missing];//.get.get_Item("xx");
ws.UsedRange.Select();
ws.UsedRange.Copy(System.Type.Missing);
// wb.Charts.Add(System.Type.Missing, System.Type.Missing, 1, System.Type.Missing);
int rowIndex = 1;
int colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
ws.Cells[rowIndex, colIndex++] = col.ColumnName;
}
for (int drvIndex = 0; drvIndex < dt.Rows.Count; drvIndex++)
{
DataRow row = dt.Rows[drvIndex];
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
ws.Cells[drvIndex + 2, colIndex] = row[col.ColumnName].ToString();
colIndex++;
}
}
oWB = (Microsoft.Office.Interop.Excel._Workbook)ws.Parent;
oChart = (Microsoft.Office.Interop.Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
int rcount = dt.Rows.Count;
int ccount = dt.Columns.Count;
oChart.ChartWizard(ws.get_Range(ws.Cells[1, 1], ws.Cells[rcount + 2, ccount + 2]), Microsoft.Office.Interop.Excel.XlChartType.xlLine, Missing.Value,
Microsoft.Office.Interop.Excel.XlRowCol.xlRows, 1, true, true,
charttitle, Missing.Value, Missing.Value, Missing.Value);
// oSeries = (Microsoft.Office.Interop.Excel.Series)oChart.SeriesCollection(1);
//string str = String.Empty;
//for (int I = 1; I < 15; I++)
//{
// str += I.ToString() + "\t";
//}
//try { oSeries.XValues = str; }
//catch { }
// oSeries.HasDataLabels = true;
// string charttitle ,bool tableflag ,bool bl)
if (tableflag == true)
{
oChart.HasDataTable = true;
}
else
{
oChart.HasDataTable = false;
}
oChart.PlotVisibleOnly = false;
//
Microsoft.Office.Interop.Excel.Axis axis = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
Microsoft.Office.Interop.Excel.XlAxisType.xlValue,
Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
//axis.HasTitle = true;
//axis.AxisTitle.Text = "Sales Figures";
// axis.HasMinorGridlines=true;
Microsoft.Office.Interop.Excel.Axis ax = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
//ax.HasTitle = true;
//ax.AxisTitle.Text = "Sales Figures";
ax.HasMajorGridlines = true;
// string filename = @"C:\Documents and Settings\tongxl\桌面\ccsb.xls";
// ws.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
try
{
wb.Saved = true;
wb.SaveCopyAs(filename);
}
catch (Exception ex)
{
MsgBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
ep.Quit();
GC.Collect();//强行销毁
}
private void button4_Click(object sender, EventArgs e)
{
if (dataGridView1.Rows.Count > 0)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel (.xlsx)| *.xlsx";
sfd.FileName = "Output.xlsx";
bool fileError = false;
if (sfd.ShowDialog() == DialogResult.OK)
{
if (File.Exists(sfd.FileName))
{
try
{
File.Delete(sfd.FileName);
}
catch (IOException ex)
{
fileError = true;
MessageBox.Show("It wasn't possible to write the data to the disk." + ex.Message);
}
}
if (!fileError)
{
try
{
Microsoft.Office.Interop.Excel.Application XcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = XcelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Output";
worksheet.Application.ActiveWindow.SplitRow = 1;
worksheet.Application.ActiveWindow.FreezePanes = true;
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
worksheet.Cells[1, i].Font.NAME = "Calibri";
worksheet.Cells[1, i].Font.Bold = true;
worksheet.Cells[1, i].Interior.Color = Color.Wheat;
worksheet.Cells[1, i].Font.Size = 12;
}
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
worksheet.Columns.AutoFit();
workbook.SaveAs(sfd.FileName);
XcelApp.Quit();
ReleaseObject(worksheet);
ReleaseObject(workbook);
ReleaseObject(XcelApp);
MessageBox.Show("Data Exported Successfully !!!", "Info");
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex.Message);
}
}
}
}
else
{
MessageBox.Show("No Record To Export !!!", "Info");
}
}
/// <summary>
/// 打印
/// </summary>
private void printInfo()
{
Object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application m_objExcel = null;
Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
Microsoft.Office.Interop.Excel.Range m_objRange = null;
try
{
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
DirectoryInfo Dir = new DirectoryInfo(".");
m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "/Templete/Apply.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));
// 号牌种类
m_objRange = m_objSheet.get_Range("D8", oMissing);
m_objRange.Value = this.category.Text;
// 号牌号码
m_objRange = m_objSheet.get_Range("I8", oMissing);
m_objRange.Value = "辽B" + this.license.Text;
// 行驶证换领原因
m_objRange = m_objSheet.get_Range("D13", oMissing);
m_objRange.Value = this.driverChange.Text;
// 登记证书申领原因
m_objRange = m_objSheet.get_Range("D14", oMissing);
m_objRange.Value = this.licenseApply.Text;
// 登记证书换领原因
m_objRange = m_objSheet.get_Range("D16", oMissing);
m_objRange.Value = this.licenseChange.Text;
// 检验合格标志换领
m_objRange = m_objSheet.get_Range("D19", oMissing);
m_objRange.Value = this.signChange.Text;
// 机动车所有人姓名/名称
m_objRange = m_objSheet.get_Range("E3", oMissing);
m_objRange.Value = this.ownerName.Text;
// 机动车所有人邮寄地址
m_objRange = m_objSheet.get_Range("E4", oMissing);
m_objRange.Value = this.ownerAddress.Text;
// 机动车所有人邮政编码
m_objRange = m_objSheet.get_Range("I3", oMissing);
m_objRange.Value = this.ownerPostcode.Text;
// 机动车所有人固定电话
m_objRange = m_objSheet.get_Range("I5", oMissing);
m_objRange.Value = this.ownerPhone.Text;
// 机动车所有人移动电话
m_objRange = m_objSheet.get_Range("E5", oMissing);
m_objRange.Value = this.ownerMobile.Text;
// 代理人姓名、名称
m_objRange = m_objSheet.get_Range("E6", oMissing);
m_objRange.Value = this.agentName.Text;
// 代理人联系电话
m_objRange = m_objSheet.get_Range("H6", oMissing);
m_objRange.Value = this.agentPhone.Text;
// 办理日期
m_objRange = m_objSheet.get_Range("F21", oMissing);
m_objRange.Value = DateTime.Today.Year + " " + DateTime.Today.Month + " " + DateTime.Today.Day + " ";
m_objExcel.DisplayAlerts = false;
m_objBook.Save();
m_objSheet.PrintOut();
}
catch (Exception ex)
{
// 输出异常信息
MessageBox.Show("打印失败,异常信息为：" + ex.Message);
}
finally
{
if (m_objBook != null)
{
m_objBook.Close(oMissing, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
}
if (m_objExcel != null)
{
m_objExcel.Workbooks.Close();
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
m_objBook = null;
m_objExcel = null;
GC.Collect();
}
}
public int print(bool b)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\CSBag\SOP-MFG-109-R01A 产品内包装记录.xlsx");
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\PTV\3 SOP-MFG-102-R01A 生产领料记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[wb.Worksheets.Count];
// 修改Sheet中某行某列的值
fill_excel(my, wb);
//"生产指令-步骤序号- 表序号 /&P"
my.PageSetup.RightFooter = Instruction + "-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count; // &P 是页码
if (b)
{
//true->预览
// 设置该进程是否可见
oXL.Visible = true;
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
return(0);
}
else
{
int pageCount = 0;
bool isPrint = true;
//false->打印
try
{
// 设置该进程是否可见
//oXL.Visible = false; // oXL.Visible=false 就会直接打印该Sheet
// 直接用默认打印机打印该Sheet
my.PrintOut();
}
catch
{ isPrint = false; }
finally
{
if (isPrint)
{
//写日志
string log = "=====================================\n";
log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色.Text + "：" + mySystem.Parameter.userName + " 打印文档\n";
dt记录.Rows[0]["日志"] = dt记录.Rows[0]["日志"].ToString() + log;
bs记录.EndEdit();
da记录.Update((DataTable)bs记录.DataSource);
}
// 关闭文件，false表示不保存
pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
}
return(pageCount);
}
}
public void ExcelExport()
{
if (ErrorCheck())
{
if (CheckBeforeExport())
{
if (bbl.ShowMessage("Q205") == DialogResult.Yes)
{
msce = new M_StoreClose_Entity();
msce = GetStoreClose_Data();
DateTime now = Convert.ToDateTime(txtDate.Text.ToString() + "/01 00:00:00");
string[] strmonth = new string[12];
for (int i = 11; i >= 0; i--)
{
strmonth[i] = now.AddMonths(-i).ToString().Substring(0, 7).ToString();
}
DataTable dt = ukkthbl.Select_DataToExport(msce);
//DataRow dr = dt.NewRow();
//dr["CustomerCD"] = "";
//dr["CustomerName"] = "";
//dr["SaleA"] = "";
//dr["11"] = Convert.ToString(strmonth[11]);
//dr["10"] = strmonth[10].ToString();
//dr["9"] = strmonth[9].ToString();
//dr["8"] = strmonth[8].ToString();
//dr["7"] = strmonth[7].ToString();
//dr["6"] = strmonth[6].ToString();
//dr["5"] = strmonth[5].ToString();
//dr["4"] = strmonth[4].ToString();
//dr["3"] = strmonth[3].ToString();
//dr["2"] = strmonth[2].ToString();
//dr["1"] = strmonth[1].ToString();
//dr["0"] = strmonth[0].ToString();
//dt.Rows.Add(dr);
if (dt.Rows.Count > 0)
{
string customerCD = string.Empty;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (customerCD != dt.Rows[i]["CustomerCD"].ToString())
{
customerCD = dt.Rows[i]["CustomerCD"].ToString();
}
DataTable dtResult = dt.Select("SaleA='売上' and CustomerCD='" + customerCD + "'").CopyToDataTable();
if (dtResult.Rows.Count == 1)
{
dt.Rows[i]["Result"] = dtResult.Rows[0]["Result"].ToString();
}
}
//dt.Columns["CustomerCD"].ColumnName =" ";
//dt.Columns["CustomerName"].ColumnName = " ";
//dt.Columns["SaleA"].ColumnName = " ";
dt.Columns["11"].ColumnName = strmonth[11].ToString();
dt.Columns["10"].ColumnName = strmonth[10].ToString();
dt.Columns["9"].ColumnName = strmonth[9].ToString();
dt.Columns["8"].ColumnName = strmonth[8].ToString();
dt.Columns["7"].ColumnName = strmonth[7].ToString();
dt.Columns["6"].ColumnName = strmonth[6].ToString();
dt.Columns["5"].ColumnName = strmonth[5].ToString();
dt.Columns["4"].ColumnName = strmonth[4].ToString();
dt.Columns["3"].ColumnName = strmonth[3].ToString();
dt.Columns["2"].ColumnName = strmonth[2].ToString();
dt.Columns["1"].ColumnName = strmonth[1].ToString();
dt.Columns["0"].ColumnName = strmonth[0].ToString();
DataTable dtExport = dt;
string folderPath = "C:\\SMS\\";
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
SaveFileDialog savedialog = new SaveFileDialog();
savedialog.Filter = "Excel Files|*.xlsx;";
savedialog.Title = "Save";
savedialog.FileName = "売掛滞留一覧表印刷";
savedialog.InitialDirectory = folderPath;
savedialog.RestoreDirectory = true;
if (savedialog.ShowDialog() == DialogResult.OK)
{
#region Test
//Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
//excel.Application.Workbooks.Add(Type.Missing);
//excel.Columns.AutoFit();
//for(int i=3; i< dtExport.Columns.Count+1; i++)
//{
// Microsoft.Office.Interop.Excel.Range xlRange = (Microsoft.Office.Interop.Excel.Range)excel.Cells[3, i];
// //xlRange.Font.Bold = -1;
// xlRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
// xlRange.Borders.Weight = 1d;
// xlRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// excel.Cells[3, i] = dtExport.Columns[i - 1].ColumnName;
//}
///*For storing Each row and column value to excel sheet*/
//for (int i = 1; i < dtExport.Rows.Count; i++)
//{
// for (int j = 0; j < dtExport.Columns.Count; j++)
// {
// if (dtExport.Rows[i][j] != null)
// {
// Microsoft.Office.Interop.Excel.Range xlRange = (Microsoft.Office.Interop.Excel.Range)excel.Cells[i + 2, j + 1];
// xlRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
// xlRange.Borders.Weight = 1d;
// excel.Cells[i + 2, j + 1] = "'"+dtExport.Rows[i][j].ToString();
// }
// }
//}
//excel.Columns.AutoFit(); // Auto fix the columns size
//System.Windows.Forms.Application.DoEvents();
//string name = "売掛滞留一覧表印刷";
//if (Directory.Exists("C:\\CTR_Data\\")) // Folder dic
//{
// excel.ActiveWorkbook.SaveCopyAs("C:\\CTR_Data\\" +name + ".xlsx");
//}
//else
//{
// Directory.CreateDirectory("C:\\CTR_Data\\");
// excel.ActiveWorkbook.SaveCopyAs("C:\\CTR_Data\\" + name + ".xlsx");
//}
//excel.ActiveWorkbook.Saved = true;
//System.Windows.Forms.Application.DoEvents();
#endregion
if (Path.GetExtension(savedialog.FileName).Contains(".xlsx"))
{
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;
worksheet = workbook.ActiveSheet;
worksheet.Name = "worksheet";
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dtExport, "worksheet");
wb.Worksheet("worksheet").Row(1).InsertRowsAbove(1);
wb.Worksheet("worksheet").Row(1).InsertRowsAbove(1);
wb.Worksheet("worksheet").Row(1).InsertRowsAbove(1);
wb.Worksheet("worksheet").Cell(1, 1).Value = "年月：";
wb.Worksheet("worksheet").Cell(2, 1).Value = "店舗:";
wb.Worksheet("worksheet").Cell(1, 2).Value = "'" + strmonth[11].ToString();
wb.Worksheet("worksheet").Cell(1, 3).Value = "～";
wb.Worksheet("worksheet").Cell(1, 4).Value = "'" + strmonth[0].ToString();
wb.Worksheet("worksheet").Cell(2, 2).Value = "'" + cboStore.SelectedValue.ToString();
wb.Worksheet("worksheet").Cell(2, 3).Value = cboStore.Text.ToString();
wb.Worksheet("worksheet").Cell(4, 1).Value = " ";
wb.Worksheet("worksheet").Cell(4, 2).Value = " ";
wb.Worksheet("worksheet").Cell(4, 3).Value = " ";
wb.Worksheet("worksheet").Cell(4, 16).Value = "売掛月数";
//wb.Worksheet("worksheet").Cell(3, 4).Value = "'" + strmonth[11].ToString();
//wb.Worksheet("worksheet").Cell(3, 5).Value = "'" + strmonth[10].ToString();
//wb.Worksheet("worksheet").Cell(3, 6).Value = "'" + strmonth[9].ToString();
//wb.Worksheet("worksheet").Cell(3, 7).Value = "'" + strmonth[8].ToString();
//wb.Worksheet("worksheet").Cell(3, 8).Value = "'" + strmonth[7].ToString();
//wb.Worksheet("worksheet").Cell(3, 9).Value = "'" + strmonth[6].ToString();
//wb.Worksheet("worksheet").Cell(3, 10).Value = "'" + strmonth[5].ToString();
//wb.Worksheet("worksheet").Cell(3, 11).Value = "'" + strmonth[4].ToString();
//wb.Worksheet("worksheet").Cell(3, 12).Value = "'" + strmonth[3].ToString();
//wb.Worksheet("worksheet").Cell(3, 13).Value = "'" + strmonth[2].ToString();
//wb.Worksheet("worksheet").Cell(3, 14).Value = "'" + strmonth[1].ToString();
//wb.Worksheet("worksheet").Cell(3, 15).Value = "'" + strmonth[0].ToString();
//wb.Worksheet("worksheet").Cell(3, 16).Value = "売掛月数";
//wb.Worksheet("worksheet").SetShowRowColHeaders(true);
wb.Worksheet("worksheet").Tables.FirstOrDefault().ShowAutoFilter = false;
//wb.Worksheet("worksheet").Tables.FirstOrDefault().ShowHeaderRow = false;
//wb.Worksheet("worksheet").Rows("4").Delete();
wb.SaveAs(savedialog.FileName);
bbl.ShowMessage("I203", string.Empty, string.Empty, string.Empty, string.Empty, string.Empty);
}
Process.Start(Path.GetDirectoryName(savedialog.FileName));
}
// Process.Start(Path.GetDirectoryName(savedialog.FileName));
}
}
else
{
bbl.ShowMessage("E128");
txtDate.Focus();
}
}
}
}
}
private void btnCities_Insert_Click(object sender, RoutedEventArgs e)
{
Thread th = new Thread(() =>
{
CityButtonContent = " Učitavanje EXCEL fajla... ";
CityButtonEnabled = false;
DateTime createTime = DateTime.Now;
List <CityViewModel> cities = new List <CityViewModel>();
List <MunicipalityViewModel> municipalities = new List <MunicipalityViewModel>();
List <RegionViewModel> regions = new List <RegionViewModel>();
#region Excel
OpenFileDialog oDlg = new OpenFileDialog();
oDlg.InitialDirectory = "C:\\";
oDlg.Filter = "xlsx Files (*.xlsx)|*.xlsx";
if (true == oDlg.ShowDialog())
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(oDlg.FileName);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int i = 2; i <= rowCount; i++)
{
CityButtonContent = i + " od " + rowCount;
RegionViewModel region = new RegionViewModel();
region.Code = xlRange.Cells[i, 3].Text;
region.RegionCode = xlRange.Cells[i, 3].Text;
region.Name = xlRange.Cells[i, 4].Text;
region.Country = new CountryViewModel()
{
Mark = xlRange.Cells[i, 7].Text
};
region.Identifier = Guid.NewGuid();
region.IsSynced = false;
region.CreatedBy = new UserViewModel()
{
Id = MainWindow.CurrentUserId
};
region.Company = new CompanyViewModel()
{
Id = MainWindow.CurrentCompanyId
};
region.CreatedAt = createTime;
region.UpdatedAt = createTime;
if (regions.Where(x => x.RegionCode == region.RegionCode).Count() == 0)
{
regions.Add(region);
}
MunicipalityViewModel municipality = new MunicipalityViewModel();
municipality.Code = xlRange.Cells[i, 5].Text;
municipality.MunicipalityCode = xlRange.Cells[i, 5].Text;
municipality.Name = xlRange.Cells[i, 6].Text;
municipality.Region = new RegionViewModel()
{
RegionCode = xlRange.Cells[i, 3].Text
};
municipality.Country = new CountryViewModel()
{
Mark = xlRange.Cells[i, 7].Text
};
municipality.Identifier = Guid.NewGuid();
municipality.IsSynced = false;
municipality.CreatedBy = new UserViewModel()
{
Id = MainWindow.CurrentUserId
};
municipality.Company = new CompanyViewModel()
{
Id = MainWindow.CurrentCompanyId
};
municipality.CreatedAt = createTime;
municipality.UpdatedAt = createTime;
if (municipalities.Where(x => x.MunicipalityCode == municipality.MunicipalityCode).Count() == 0)
{
municipalities.Add(municipality);
}
CityViewModel city = new CityViewModel();
city.Code = xlRange.Cells[i, 1].Text;
city.ZipCode = xlRange.Cells[i, 1].Text;
city.Name = xlRange.Cells[i, 2].Text;
city.Country = new CountryViewModel()
{
Mark = xlRange.Cells[i, 7].Text
};
city.Municipality = new MunicipalityViewModel()
{
MunicipalityCode = xlRange.Cells[i, 5].Text
};
city.Region = new RegionViewModel()
{
RegionCode = xlRange.Cells[i, 3].Text
};
city.Identifier = Guid.NewGuid();
city.IsSynced = false;
city.CreatedBy = new UserViewModel()
{
Id = MainWindow.CurrentUserId
};
city.Company = new CompanyViewModel()
{
Id = MainWindow.CurrentCompanyId
};
city.CreatedAt = createTime;
city.UpdatedAt = createTime;
if (cities.Where(x => x.ZipCode == city.ZipCode).Count() == 0)
{
cities.Add(city);
if (i % 100 == 0)
{
CityButtonContent = " Unos regiona u toku... ";
CityButtonEnabled = false;
string apiUrlTmp = BaseApiUrl + "/SeedData/SeedRegions";
string valuesTmp = JsonConvert.SerializeObject(
regions,
Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
SendData(apiUrlTmp, valuesTmp);
regions.Clear();
CityButtonContent = " Unos opstina u toku... ";
CityButtonEnabled = false;
apiUrlTmp = BaseApiUrl + "/SeedData/SeedMunicipalities";
valuesTmp = JsonConvert.SerializeObject(
municipalities,
Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
SendData(apiUrlTmp, valuesTmp);
municipalities.Clear();
CityButtonContent = " Unos gradova u toku... ";
CityButtonEnabled = false;
apiUrlTmp = BaseApiUrl + "/SeedData/SeedCities";
valuesTmp = JsonConvert.SerializeObject(
cities,
Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
SendData(apiUrlTmp, valuesTmp);
cities.Clear();
}
}
}
}
#endregion
CityButtonContent = " Unos podataka u toku... ";
CityButtonEnabled = false;
string apiUrl = BaseApiUrl + "/SeedData/SeedRegions";
string values = JsonConvert.SerializeObject(
regions,
Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
SendData(apiUrl, values);
apiUrl = BaseApiUrl + "/SeedData/SeedMunicipalities";
values = JsonConvert.SerializeObject(
municipalities,
Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
SendData(apiUrl, values);
apiUrl = BaseApiUrl + "/SeedData/SeedCities";
values = JsonConvert.SerializeObject(
cities,
Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
SendData(apiUrl, values);
CityButtonContent = " Gradovi ";
CityButtonEnabled = true;
});
th.IsBackground = true;
th.Start();
}
/// <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.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
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)
{
try
{
Worksheet.SaveAs(ExcelFilePath);
Excel.Quit();
MessageBox.Show("Excel file saved!");
}
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);
}
}
private void btnLicenceTypes_Insert_Click(object sender, RoutedEventArgs e)
{
Thread th = new Thread(() =>
{
LicenceTypeButtonContent = " Učitavanje EXCEL fajla... ";
LicenceTypeButtonEnabled = false;
List <LicenceTypeViewModel> licenceTypes = new List <LicenceTypeViewModel>();
#region Excel
OpenFileDialog oDlg = new OpenFileDialog();
oDlg.InitialDirectory = "C:\\";
oDlg.Filter = "xlsx Files (*.xlsx)|*.xlsx";
if (true == oDlg.ShowDialog())
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(oDlg.FileName);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int i = 2; i <= rowCount; i++)
{
LicenceTypeButtonContent = i + " od " + rowCount;
LicenceTypeViewModel licenceType = new LicenceTypeViewModel();
licenceType.Category = xlRange.Cells[i, 1].Text;
licenceType.Description = xlRange.Cells[i, 2].Text;
licenceType.Country = new CountryViewModel()
{
Mark = xlRange.Cells[i, 3].Text
};
licenceType.Identifier = Guid.NewGuid();
licenceType.IsSynced = false;
licenceType.CreatedBy = new UserViewModel()
{
Id = MainWindow.CurrentUserId
};
licenceType.Company = new CompanyViewModel()
{
Id = MainWindow.CurrentCompanyId
};
licenceType.CreatedAt = DateTime.Now;
licenceType.UpdatedAt = DateTime.Now;
licenceTypes.Add(licenceType);
if (i % 100 == 0)
{
BankButtonContent = " Unos podataka u toku... ";
BankButtonEnabled = false;
string apiUrlTmp = BaseApiUrl + "/SeedData/SeedLicenceTypes";
string valuesTmp = JsonConvert.SerializeObject(
licenceTypes,
Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
SendData(apiUrlTmp, valuesTmp);
licenceTypes.Clear();
}
}
}
#endregion
LicenceTypeButtonContent = " Unos podataka u toku... ";
LicenceTypeButtonEnabled = false;
string apiUrl = BaseApiUrl + "/SeedData/SeedLicenceTypes";
string values = JsonConvert.SerializeObject(
licenceTypes,
Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
SendData(apiUrl, values);
LicenceTypeButtonContent = " Tip dozvole ";
LicenceTypeButtonEnabled = true;
});
th.IsBackground = true;
th.Start();
}
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 WorkSheet(Microsoft.Office.Interop.Excel._Worksheet worksheet)
{
_workSheet = worksheet;
}
private void bt_XuatFile_Click(object sender, EventArgs e)
{
data_ThongTin.DataSource = dt.data_xuatExcel();
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;
app.Visible = true;
// Dua du lieu vao excel
worksheet.Cells[1, 4] = "Bảng Thống Kê Hóa Đơn ";
worksheet.Cells[2, 3] = "";
worksheet.Cells[3, 1] = "STT";
worksheet.Cells[3, 2] = "Mã HD";
worksheet.Cells[3, 3] = "Tên khách hàng";
worksheet.Cells[3, 4] = "Tên nhân viên";
worksheet.Cells[3, 5] = "Ngày lập hóa đơn";
worksheet.Cells[3, 6] = "Giảm giá (%)";
worksheet.Cells[3, 7] = "Tổng tiền (VNĐ)";
for (int i = 0; i < data_ThongTin.RowCount - 1; i++)
{
for (int j = 0; j < 6; j++)
{
worksheet.Cells[i + 4, 1] = i + 1;
worksheet.Cells[i + 4, j + 2] = data_ThongTin.Rows[i].Cells[j].Value;
}
}
int dem = data_ThongTin.RowCount - 1;
// Định dạng trang
worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
worksheet.PageSetup.LeftMargin = 0;
worksheet.PageSetup.RightMargin = 0;
worksheet.PageSetup.TopMargin = 0;
worksheet.PageSetup.BottomMargin = 0;
// Đinh dạng cột
worksheet.Range["A1"].ColumnWidth = 7;
worksheet.Range["B1"].ColumnWidth = 45;
worksheet.Range["C1"].ColumnWidth = 49;
worksheet.Range["D1"].ColumnWidth = 33;
worksheet.Range["E1"].ColumnWidth = 31;
worksheet.Range["F1"].ColumnWidth = 52;
worksheet.Range["G1"].ColumnWidth = 23;
// Định dạng font chữ
// worksheet.Range["A1", "J100"].Font.Name = "";
worksheet.Range["A1", "A100"].Font.Size = 24;
worksheet.Range["A3", "G100"].Font.Size = 16;
worksheet.Range["A1", "G1"].MergeCells = true;
worksheet.Range["A1", "G1"].Font.Bold = true;
//worksheet.Range["A3", "J3"].MergeCells = true;
worksheet.Range["A3", "G3"].Font.Bold = true;
// kẻ bảng
worksheet.Range["A3", "G" + (dem + 3)].Borders.LineStyle = 1;
//Định dạng dòng text
worksheet.Range["A1", "G1"].HorizontalAlignment = 3;
worksheet.Range["A3", "G3"].HorizontalAlignment = 3;
worksheet.Range["A4", "G" + (dem + 4)].HorizontalAlignment = 3;
worksheet.Range["B4", "G" + (dem + 4)].HorizontalAlignment = 3;
worksheet.Range["C4", "G" + (dem + 4)].HorizontalAlignment = 3;
worksheet.Range["D4", "G" + (dem + 4)].HorizontalAlignment = 3;
worksheet.Range["E4", "G" + (dem + 4)].HorizontalAlignment = 3;
worksheet.Range["F4", "G" + (dem + 4)].HorizontalAlignment = 3;
worksheet.Range["G4", "G" + (dem + 4)].HorizontalAlignment = 3;
worksheet.Range["H4", "G" + (dem + 4)].HorizontalAlignment = 3;
}
private void XuatCTHD(string file)
{
try
{
// creating Excel Application
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;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Bán hàng";
Microsoft.Office.Interop.Excel.Range head = worksheet.Range["A1", "E1"];
head.MergeCells = true;
head.Value2 = "HÓA ĐƠN BÁN HÀNG";
head.Font.Bold = true;
head.Font.Size = 20;
head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//--------------TOTAL----------------------
int r = gdvCTHD.Rows.Count + 8;
Microsoft.Office.Interop.Excel.Range total = worksheet.Range["D" + r.ToString(), "D" + r.ToString()];
total.MergeCells = true;
total.Value2 = "Tổng tiền:";
total.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//-----------------------------------------------------------------------------------------------------
Microsoft.Office.Interop.Excel.Range total1 = worksheet.Range["E" + r.ToString(), "E" + r.ToString()];
total1.MergeCells = true;
total1.Value2 = txtTT.Text;
total1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//tên khách hàng
Microsoft.Office.Interop.Excel.Range tenkh = worksheet.Range["A3", "A3"];
tenkh.MergeCells = true;
tenkh.Value2 = "Tên KH:";
tenkh.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//-----------------------------------------------------
Microsoft.Office.Interop.Excel.Range tenkh1 = worksheet.Range["B3", "B3"];
tenkh1.MergeCells = true;
tenkh1.Value2 = cbo_TKH.Text;
tenkh1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//tên nhân vien bán
Microsoft.Office.Interop.Excel.Range tennv = worksheet.Range["D3", "D3"];
tennv.MergeCells = true;
tennv.Value2 = "Tên NV:";
tennv.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//-----------------------------------------------------
Microsoft.Office.Interop.Excel.Range tennv1 = worksheet.Range["E3", "E3"];
tennv1.MergeCells = true;
tennv1.Value2 = cboTNV.Text;
tennv1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//Mã hóa đơn
Microsoft.Office.Interop.Excel.Range mahd = worksheet.Range["A5", "A5"];
mahd.MergeCells = true;
mahd.Value2 = "Mã Hóa Đơn:";
mahd.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//-----------------------------------------------------
Microsoft.Office.Interop.Excel.Range mahd1 = worksheet.Range["B5", "B5"];
mahd1.MergeCells = true;
mahd1.Value2 = txtMaHD.Text;
mahd1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
//Ngày lập
Microsoft.Office.Interop.Excel.Range ngaylap = worksheet.Range["D5", "D5"];
ngaylap.Value2 = "Ngày Lập:";
ngaylap.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//-----------------------------------------------------
Microsoft.Office.Interop.Excel.Range ngaylap1 = worksheet.Range["E5", "E5"];
dateEdit1.EditValue = DateTime.Now;
ngaylap1.Value2 = dateEdit1.EditValue.ToString();
ngaylap1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
// storing header part in Excel
for (int i = 1; i <= gdvCTHD.Columns.Count; i++)
{
worksheet.Cells[7, i] = gdvCTHD.Columns[i - 1].HeaderText;
}
worksheet.Range["A7", "E7"].Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// storing Each row and column value to excel sheet
for (int i = 0; i < gdvCTHD.Rows.Count - 1; i++)
{
for (int j = 0; j < gdvCTHD.Columns.Count; j++)
{
worksheet.Cells[i + 8, j + 1] = gdvCTHD.Rows[i].Cells[j].Value.ToString();
}
}
// save the application
workbook.SaveAs(file);
// see the excel sheet behind the program
if (MessageBox.Show("Xuất tệp excel thành công!") == DialogResult.OK)
{
app.Visible = true;
}
}
catch
{
MessageBox.Show("Xuất tệp excel thất bại!");
}
}
private void mitExportToExcel_Click(object sender, RoutedEventArgs e)
{
int intRowCounter;
int intRowNumberOfRecords;
int intColumnCounter;
int intColumnNumberOfRecords;
// 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
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "OpenOrders";
int cellRowIndex = 1;
int cellColumnIndex = 1;
intRowNumberOfRecords = TheTotalHoursDataSet.totalhours.Rows.Count;
intColumnNumberOfRecords = TheTotalHoursDataSet.totalhours.Columns.Count;
for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = TheTotalHoursDataSet.totalhours.Columns[intColumnCounter].ColumnName;
cellColumnIndex++;
}
cellRowIndex++;
cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (intRowCounter = 0; intRowCounter < intRowNumberOfRecords; intRowCounter++)
{
for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = TheTotalHoursDataSet.totalhours.Rows[intRowCounter][intColumnCounter].ToString();
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 1;
saveDialog.ShowDialog();
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
catch (System.Exception ex)
{
TheEventLogClass.InsertEventLogEntry(DateTime.Now, "Blue Jay ERP // Find Employee Hours // Export to Excel " + ex.Message);
MessageBox.Show(ex.ToString());
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
public int Export()
{
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;
int result = 0;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "ExportedFromDatGrid";
int cellRowIndex = 1;
int cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (int i = 0; i < list.Count; i++)
{
for (int j = 0; j < columns.Length; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = columns[j];
}
else
{
var item = list.ElementAt(i).GetResult();
var dictionaryFromObj = ParseObj.ToDictionary(item);
if (j == 0)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = i;
}
else
{
var valueCell = dictionaryFromObj[columnsEng[j]];
//string strValue = valueCell.ToString();
/*if (valueCell.GetType() == typeof(TimeSpan))
* {
* strValue = TimeSpan.Parse(valueCell.ToString()).ToString("hh':'mm':'ss'.'fff");
* Console.WriteLine("timespan: {0}", strValue.ToString());
* }*/
worksheet.Cells[cellRowIndex, cellColumnIndex] = valueCell;
}
}
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
workbook.SaveAs("exportList.xlsx");
Console.WriteLine("export success");
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
result = 1;
}
return(result);
}
public void InitExcel()
{
//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(""));
//Izzet Sheet
IzzetSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
IzzetSheet.Name = "Red Blue";
IzzetSheet.Cells[1, 1] = "Card Name";
IzzetSheet.Cells[1, 2] = "Mana Cost";
IzzetSheet.Cells[1, 1].EntireRow.Font.Bold = true;
IzzetSheet.Columns[1].ColumnWidth = 26;
IzzetSheet.Columns[2].ColumnWidth = 10;
IzzetSheet.Columns[3].ColumnWidth = 12;
//Dimir Sheet
DimirSheet = oWB.Sheets.Add(missing, missing, 1, missing)
as Microsoft.Office.Interop.Excel.Worksheet;
DimirSheet.Name = "Blue Black";
DimirSheet.Cells[1, 1] = "Card Name";
DimirSheet.Cells[1, 2] = "Mana Cost";
DimirSheet.Cells[1, 1].EntireRow.Font.Bold = true;
DimirSheet.Columns[1].ColumnWidth = 26;
DimirSheet.Columns[2].ColumnWidth = 10;
DimirSheet.Columns[3].ColumnWidth = 12;
//Golgari Sheet
GolgariSheet = oWB.Sheets.Add(missing, missing, 1, missing)
as Microsoft.Office.Interop.Excel.Worksheet;
GolgariSheet.Name = "Black Green";
GolgariSheet.Cells[1, 1] = "Card Name";
GolgariSheet.Cells[1, 2] = "Mana Cost";
GolgariSheet.Cells[1, 1].EntireRow.Font.Bold = true;
GolgariSheet.Columns[1].ColumnWidth = 26;
GolgariSheet.Columns[2].ColumnWidth = 10;
GolgariSheet.Columns[3].ColumnWidth = 12;
//Selesnya Sheet
SelesnyaSheet = oWB.Sheets.Add(missing, missing, 1, missing)
as Microsoft.Office.Interop.Excel.Worksheet;
SelesnyaSheet.Name = "Green White";
SelesnyaSheet.Cells[1, 1] = "Card Name";
SelesnyaSheet.Cells[1, 2] = "Mana Cost";
SelesnyaSheet.Cells[1, 1].EntireRow.Font.Bold = true;
SelesnyaSheet.Columns[1].ColumnWidth = 26;
SelesnyaSheet.Columns[2].ColumnWidth = 10;
SelesnyaSheet.Columns[3].ColumnWidth = 12;
//Boros Sheet
BorosSheet = oWB.Sheets.Add(missing, missing, 1, missing)
as Microsoft.Office.Interop.Excel.Worksheet;
BorosSheet.Name = "White Red";
BorosSheet.Cells[1, 1] = "Card Name";
BorosSheet.Cells[1, 2] = "Mana Cost";
BorosSheet.Cells[1, 1].EntireRow.Font.Bold = true;
BorosSheet.Columns[1].ColumnWidth = 26;
BorosSheet.Columns[2].ColumnWidth = 10;
BorosSheet.Columns[3].ColumnWidth = 12;
//Red Sheet
RedSheet = oWB.Sheets.Add(missing, missing, 1, missing)
as Microsoft.Office.Interop.Excel.Worksheet;
RedSheet.Name = "Red";
RedSheet.Cells[1, 1] = "Card Name";
RedSheet.Cells[1, 2] = "Mana Cost";
RedSheet.Cells[1, 1].EntireRow.Font.Bold = true;
RedSheet.Columns[1].ColumnWidth = 26;
RedSheet.Columns[2].ColumnWidth = 10;
RedSheet.Columns[3].ColumnWidth = 12;
//Black Sheet
BlackSheet = oWB.Sheets.Add(missing, missing, 1, missing)
as Microsoft.Office.Interop.Excel.Worksheet;
BlackSheet.Name = "Black";
BlackSheet.Cells[1, 1] = "Card Name";
BlackSheet.Cells[1, 2] = "Mana Cost";
BlackSheet.Cells[1, 1].EntireRow.Font.Bold = true;
BlackSheet.Columns[1].ColumnWidth = 26;
BlackSheet.Columns[2].ColumnWidth = 10;
BlackSheet.Columns[3].ColumnWidth = 12;
//Green Sheet
GreenSheet = oWB.Sheets.Add(missing, missing, 1, missing)
as Microsoft.Office.Interop.Excel.Worksheet;
GreenSheet.Name = "Green";
GreenSheet.Cells[1, 1] = "Card Name";
GreenSheet.Cells[1, 2] = "Mana Cost";
GreenSheet.Cells[1, 1].EntireRow.Font.Bold = true;
GreenSheet.Columns[1].ColumnWidth = 26;
GreenSheet.Columns[2].ColumnWidth = 10;
GreenSheet.Columns[3].ColumnWidth = 12;
//White Sheet
WhiteSheet = oWB.Sheets.Add(missing, missing, 1, missing)
as Microsoft.Office.Interop.Excel.Worksheet;
WhiteSheet.Name = "White";
WhiteSheet.Cells[1, 1] = "Card Name";
WhiteSheet.Cells[1, 2] = "Mana Cost";
WhiteSheet.Cells[1, 1].EntireRow.Font.Bold = true;
WhiteSheet.Columns[1].ColumnWidth = 26;
WhiteSheet.Columns[2].ColumnWidth = 10;
WhiteSheet.Columns[3].ColumnWidth = 12;
//Blue Sheet
BlueSheet = oWB.Sheets.Add(missing, missing, 1, missing)
as Microsoft.Office.Interop.Excel.Worksheet;
BlueSheet.Name = "Blue";
BlueSheet.Cells[1, 1] = "Card Name";
BlueSheet.Cells[1, 2] = "Mana Cost";
BlueSheet.Cells[1, 1].EntireRow.Font.Bold = true;
BlueSheet.Columns[1].ColumnWidth = 26;
BlueSheet.Columns[2].ColumnWidth = 10;
BlueSheet.Columns[3].ColumnWidth = 12;
//Else Sheet
ElseSheet = oWB.Sheets.Add(missing, missing, 1, missing)
as Microsoft.Office.Interop.Excel.Worksheet;
ElseSheet.Name = "Else";
ElseSheet.Cells[1, 1] = "Card Name";
ElseSheet.Cells[1, 2] = "Mana Cost";
ElseSheet.Cells[1, 1].EntireRow.Font.Bold = true;
ElseSheet.Columns[1].ColumnWidth = 26;
ElseSheet.Columns[2].ColumnWidth = 10;
ElseSheet.Columns[3].ColumnWidth = 12;
}
private void XuatExcel(string file)
{
try
{
// creating Excel Application
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;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Bán hàng";
Microsoft.Office.Interop.Excel.Range head = worksheet.Range["A1", "I1"];
head.MergeCells = true;
head.Value2 = "PHIẾU GIẶT";
head.Font.Bold = true;
head.Font.Size = 20;
head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//from
Microsoft.Office.Interop.Excel.Range fromDate = worksheet.Range["A3", "C3"];
fromDate.MergeCells = true;
fromDate.Value2 = "Ngày nhận: " + dt_ngaynhan.Text;
fromDate.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//to
Microsoft.Office.Interop.Excel.Range toDate = worksheet.Range["A4", "C3"];
toDate.MergeCells = true;
toDate.Value2 = "ngày ngày trả: " + dt_ngaytra.Text;
toDate.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// storing header part in Excel
for (int i = 1; i < dv_ctdg.Columns.Count; i++)
{
worksheet.Cells[5, i] = dv_ctdg.Columns[i - 1].HeaderText;
}
worksheet.Range["A5", "I5"].Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// storing Each row and column value to excel sheet
for (int i = 0; i < dv_ctdg.Rows.Count; i++)
{
for (int j = 0; j < dv_ctdg.Columns.Count - 3; j++)
{
worksheet.Cells[i + 6, j + 1] = dv_ctdg.Rows[i].Cells[j].Value.ToString();
}
}
// save the application
workbook.SaveAs(file);
// see the excel sheet behind the program
if (MessageBox.Show("Xuất tệp excel thành công!") == DialogResult.OK)
{
app.Visible = true;
}
}
catch
{
MessageBox.Show("Xuất tệp excel thất bại!");
}
}
private void f_excel(string path, string batDau, string ketThuc)
{
try
{
byte[] dsgiahan_bytes = Properties.Resources.DanhSachGiaHan;
System.IO.File.WriteAllBytes(path, dsgiahan_bytes);
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(path);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
DateTime _batdau = Convert.ToDateTime(batDau);
DateTime _ketthuc = Convert.ToDateTime(ketThuc);
xlWorksheet.Cells[4, 1] = "Từ ngày " + _batdau.ToString("dd/MM/yyyy") + " đến ngày " + _ketthuc.ToString("dd/MM/yyyy");
xlWorksheet.Cells[1, 1] = "Địa chỉ: 279/006C Âu Cơ, P.5, Q.11, Tp.HCM, Việt Nam";
xlWorksheet.Cells[2, 1] = "Tên: Cty An Phu Viet";
int rowindex = 0;
var listData = (List <ThongKeGiaHanViewModel>)HttpContext.Current.Session[CommonConstants.SessionThongKeGiaHan];
var sumTk = (SumThongKeGiaHan)HttpContext.Current.Session[CommonConstants.SessionSumThongKeGiaHan];
listData.Add(new ThongKeGiaHanViewModel
{
NgayGiaHan = "Số lượt xe",
GiaVe = sumTk.SoLuotXe
});
foreach (var item in sumTk.ListLoaiGiaVe)
{
listData.Add(new ThongKeGiaHanViewModel
{
NgayGiaHan = item.Name,
GiaVe = item.TongTien
});
}
listData.Add(new ThongKeGiaHanViewModel
{
NgayGiaHan = "Tổng tiền",
GiaVe = sumTk.TongIien.ToString()
});
var data = new object[listData.Count, 8];
foreach (var item in listData)
{
data[rowindex, 0] = item.STT;
data[rowindex, 1] = item.BienSo == null ? "" : item.BienSo;
data[rowindex, 2] = item.NgayGiaHan == null ? "" : item.NgayGiaHan;
data[rowindex, 3] = item.GiaHanHoTen == null ? "" : item.GiaHanHoTen;
data[rowindex, 4] = item.VeThangHoTen == null ? "" : item.VeThangHoTen;
data[rowindex, 5] = item.GiaVe == null ? "" : item.GiaVe;
data[rowindex, 6] = item.NgayHetHan == null ? "" : item.NgayHetHan;
data[rowindex, 7] = item.NhanVien == null ? "" : item.NhanVien;
rowindex++;
}
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.Range[xlWorksheet.Cells[7, 1], xlWorksheet.Cells[7 + listData.Count, 8]];
xlRange.Value2 = data;
xlWorkbook.Save();
xlWorkbook.Close();
//Marshal.FinalReleaseComObject(xlRange);
Marshal.FinalReleaseComObject(xlWorksheet);
Marshal.FinalReleaseComObject(xlWorkbook);
//xlRange = null;
xlWorksheet = null;
xlWorkbook = null;
xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);
xlApp = null;
}
catch (Exception)
{
}
}
