private void exportButton_Click(object sender, EventArgs e)
{
// 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 = "Exported from gridview";
worksheet.DisplayRightToLeft = true;
DialogResult res = FMessegeBox.FarsiMessegeBox.Show("نسبت به گرفتن خروجی اکسل اطمینان دارید؟", "پرسش", FMessegeBox.FMessegeBoxButtons.YesNo, FMessegeBox.FMessegeBoxIcons.Question, FMessegeBox.FMessegeBoxDefaultButton.button1);
if (res != DialogResult.Yes)
{
app.Quit();
return;
}
for (int i = 1; i < membersView.Columns.Count; i++)
{
worksheet.Cells[1, i] = membersView.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < membersView.Rows.Count; i++)
{
for (int j = 0; j < membersView.Columns.Count - 1; j++)
{
if (membersView.Rows[i].Cells[j].Value.GetType().ToString() == "System.DateTime")
{
worksheet.Cells[i + 2, j + 1] = ExtensionFunction.ToPersian(Convert.ToDateTime(membersView.Rows[i].Cells[j].Value.ToString()));
}
else
{
worksheet.Cells[i + 2, j + 1] = membersView.Rows[i].Cells[j].Value.ToString();
}
}
}
// see the excel sheet behind the program
app.Visible = true;
// save the application
//workbook.SaveAs("c:\\output.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Exit from the application
//app.Quit();
}
// код для импрота данных из таблицы Excel. Проверено только с форматом xlsx (версия Excel 2010)
private void importToolStripMenuItem_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "Files Excel (*.xlsx)|*.xlsx|All files (*.*)|*.*";
dialog.FilterIndex = 1;
if (dialog.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
// понятия не имею, что обозначают аргументы в вызове метода excel.Workbooks.Open
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Open(dialog.FileName, 0, true, 5, "", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
workbook.ReadOnlyRecommended = true;
try
{
Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Worksheets.get_Item(1);
for (byte i = 2; i < 30; i++) // 2 - 29 - номера ячеек в стоблцах таблицы (сверху вниз), значения которых нужно импортировать
{
for (byte j = 4; j < 7; j++) // 4 - 6 - номера ячеек в рядках таблицы (слева направо), значения которых нужно импортировать
{
// проверка на null-значение вместо null импортируется пустая строка
string cellValue = (worksheet.Cells[i, j] as Microsoft.Office.Interop.Excel.Range).Value != null ?
(worksheet.Cells[i, j] as Microsoft.Office.Interop.Excel.Range).Value.ToString() : "";
Grid.Rows[i - 1].Cells[j - 1].Value = cellValue;
// i - 1, j - 1 - поправка на смещение индексов
}
}
for (ushort i = 0; i < Grid.Rows.Count; i++)
{
Grid_CellValidated(new object(), new DataGridViewCellEventArgs(0, i)); // "триггер" для того, чтобы значения ячеек,
}
// в которые не вводятся данные,
// начали рассчитываться. Небольшой костыль
MessageBox.Show("Data imported successfully");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error!");
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
}
private void btnin_Click(object sender, EventArgs e)
{
//Khởi tạo Excel
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ạo WorkSheet
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
app.Visible = true;
worksheet.Cells[2, 2] = "Danh sách phí đoàn viên ";
worksheet.Cells[7, 1] = "STT";
worksheet.Cells[7, 2] = "ID";
worksheet.Cells[7, 3] = "Năm";
worksheet.Cells[7, 4] = "Mã sinh viên";
worksheet.Cells[7, 5] = "Phí đoàn viên";
for (int i = 0; i < dtgrvPhiDoan.RowCount; i++)
{
for (int j = 0; j < 4; j++)
{
worksheet.Cells[i + 8, 1] = i + 1;
worksheet.Cells[i + 8, j + 2] = dtgrvPhiDoan.Rows[i].Cells[j].Value;
}
}
int HD_Doan = dtgrvPhiDoan.RowCount;
//Đị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 dang cột
worksheet.Range["A1"].ColumnWidth = 8.43;
worksheet.Range["B1"].ColumnWidth = 10;
worksheet.Range["C1"].ColumnWidth = 27.71;
worksheet.Range["D1"].ColumnWidth = 17;
worksheet.Range["E1"].ColumnWidth = 18;
//Định dạng font chữ
worksheet.Range["A1", "E100"].Font.Name = "Times New Roman";
worksheet.Range["A1", "E100"].Font.Size = 14;
worksheet.Range["B2", "E2"].MergeCells = true;
worksheet.Range["B2", "E2"].Font.Bold = true;
worksheet.Range["A2", "E2"].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.Range["B2", "E2"].Font.Size = 17;
worksheet.Range["A7", "E" + (HD_Doan + 7)].Borders.LineStyle = 1;
}
private void DGV_To_Excel(DataGridView dataGridView)
{
backgroundWorker.ReportProgress(2);
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 = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
//for (int i = 1; i < dataGridView.Columns.Count + 1; i++)
//{
// worksheet.Cells[i, i + 1] = dataGridView.Columns[i - 1].HeaderText;
//}
int k = 1;
worksheet.Cells[k, k + 1] = dataGridView.Columns[k - 1].HeaderText;
worksheet.Cells[k, k + 2] = dataGridView.Columns[k].HeaderText;
worksheet.Cells[k, k + 3] = dataGridView.Columns[k + 1].HeaderText;
worksheet.Cells[k, k + 4] = dataGridView.Columns[k + 2].HeaderText;
var index = 1;
var process = dataGridView.Rows.Count;
for (int i = 0; i < dataGridView.Rows.Count; i++)
{
backgroundWorker.ReportProgress(index++ *100 / process);
for (int j = 0; j < dataGridView.Columns.Count; j++)
{
if (dataGridView.Rows[i].Cells[j].Value != null)
{
worksheet.Cells[i + 2, j + 2] = dataGridView.Rows[i].Cells[j].Value.ToString();
}
else
{
worksheet.Cells[i + 2, j + 2] = "";
}
}
}
//for (int i = 0; i < dataGridView.Rows.Count; i++)
//{
// worksheet.Cells[i + 2, i] = dataGridView.Rows[i].HeaderCell.Value;
//}
app.Visible = true;
}
private void btnExceleAktar_Click(object sender, EventArgs e)
{
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;
// see the excel sheet behind the program
app.Visible = true;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets[1];
worksheet = workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Exported from gridview";
// storing header part in Excel
for (int i = 1; i < dataGridViewListe.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridViewListe.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dataGridViewListe.Rows.Count; i++)
{
for (int j = 0; j < dataGridViewListe.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridViewListe.Rows[i].Cells[j].Value.ToString();
}
}
// save the application
//FolderBrowserDialog folderBrowserDialog = new FolderBrowserDialog();
//folderBrowserDialog.ShowDialog();
//string fileName = folderBrowserDialog.SelectedPath + "\\" + KayitIsmi;
//KayitIsmi = $"c:\\{fileName}";
//workbook.SaveAs(KayitIsmi, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//// Exit from the application
//app.Quit();
}
catch (Exception exception)
{
HelpClass.MessageBoxOperation.MessageBoxError(exception.Message);
}
}
public string TaxRegimeExcel(List <string> list)
{
var indexs = MapTaxRegimeToExcel(list);
Microsoft.Office.Interop.Excel.Application oXL = null;
Microsoft.Office.Interop.Excel._Workbook oWB = null;
Microsoft.Office.Interop.Excel._Worksheet oSheet = null;
oXL = new Microsoft.Office.Interop.Excel.Application();
oWB = oXL.Workbooks.Open("D:\\Users\\staff\\Desktop\\Book1.xlsx");
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.Worksheets["Sheet3"];
for (var i = 1; i <= list.Count; i++)
{
var countParent = 1;
JObject obj = JObject.Parse(list[i - 1]);
oSheet.Cells[i + 1, countParent++] = ((string)obj["federalTaxNumber"] ?? "");
oSheet.Cells[i + 1, countParent++] = ((string)obj["name"] ?? "");
oSheet.Cells[i + 1, countParent++] = ((string)obj["taxRegime"] ?? "");
oSheet.Cells[i + 1, countParent++] = ((string)obj["optedInOn"] ?? "");
if ((obj["previousDetails"]) != null)
{
var couPhone = countParent;
var x = 0;
for (x = 0; x < obj["previousDetails"].Count(); x++)
{
oSheet.Cells[i + 1, couPhone++] = ((string)obj["previousDetails"][x]["endOn"] ?? "");
oSheet.Cells[i + 1, couPhone++] = ((string)obj["previousDetails"][x]["beginOn"] ?? "");
oSheet.Cells[i + 1, couPhone++] = ((string)obj["previousDetails"][x]["description"] ?? "");
oSheet.Cells[i + 1, couPhone++] = ((string)obj["previousDetails"][x]["taxRegime"] ?? "");
}
}
countParent = indexs.Item2;
}
oWB.Save();
if (oWB != null)
{
oWB.Close();
}
return(null);
}
private void excelButton_Click(object sender, EventArgs e)
{
try
{
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 = true;
worksheet = workbook.Sheets["Лист1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Export";
for (int i = 1; i < table1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = table1.Columns[i - 1].HeaderText;
worksheet.Columns.AutoFit();
}
for (int i = 0; i < table1.Rows.Count; i++)
{
for (int j = 0; j < table1.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = table1.Rows[i].Cells[j].Value.ToString();
worksheet.Columns.AutoFit();
}
}
var saveFileDialoge = new SaveFileDialog();
saveFileDialoge.FileName = "Табель№";
// saveFileDialoge.DefaultExt = "";
saveFileDialoge.Filter = "Excel (*.xlsx)|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
saveFileDialoge.AddExtension = true;
if (saveFileDialoge.ShowDialog() == DialogResult.OK)
{
workbook.SaveAs(saveFileDialoge.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
app.Quit();
}
catch {
MessageBox.Show("Отмена создания отчёта...\nСмените язык MS Office на русский.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
//populate a list of Sheet objects for a workbook
private void LoadSheets(Book b, Microsoft.Office.Interop.Excel._Workbook xlBook)
{
Microsoft.Office.Interop.Excel.Sheets xlSheets = xlBook.Worksheets;
foreach (Microsoft.Office.Interop.Excel._Worksheet w in xlSheets)
{
Sheet s = new Sheet()
{
Id = _sheets.Count(),
BookId = b.Id,
Name = w.Name
};
LoadRows(s, w);
_sheets.Add(s);
}
}
protected void btnxuatex_Click(object sender, EventArgs e)
{
try
{
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;
///đổ dữ liệu vào sheet
worksheet.Cells[1, 7] = "DANH SÁCH BOOKING THEO TÊN NHÂN VIÊN";
worksheet.Cells[2, 2] = "E-TOUR";
worksheet.Cells[2, 10] = "Địa chỉ: Tòa nhà FPTS 52 Lạc Long Quân, Tây Hồ, Hà Nội";
worksheet.Cells[3, 2] = "Ngày:" + txttimngaybd.Text + "-" + txttimngaykt.Text;
worksheet.Cells[5, 1] = "STT";
worksheet.Cells[5, 2] = "Mã vé";
worksheet.Cells[5, 3] = "Mã Tour";
worksheet.Cells[5, 4] = "Mã Lịch Trình";
worksheet.Cells[5, 5] = "Mã Khách hàng";
worksheet.Cells[5, 6] = "Mã Nhân Viên";
worksheet.Cells[5, 7] = "Ngày book";
worksheet.Cells[5, 8] = "Giá Tiền";
worksheet.Cells[5, 9] = "Tên Nhân viên";
worksheet.Cells[5, 10] = "Mã Nhân viên";
worksheet.Cells[5, 11] = "Nghiệp vụ";
worksheet.Cells[5, 12] = "Giới tính";
worksheet.Cells[5, 13] = "Địa chỉ";
worksheet.Cells[5, 14] = "Số điện thoại";
worksheet.Cells[5, 15] = "ngày sinh";
//int n = myDT.Rows.Count;
for (int i = 0; i < dgvtimbooktheoten.Rows.Count; i++)
{
for (int j = 0; j < 14; j++)
{
worksheet.Cells[i + 9, 1] = i + 1;
worksheet.Cells[i + 9, j + 2] = dgvtimbooktheoten.Rows[i].Cells[j].Text.ToString(); /*myDT.Rows[i][j].ToString();*/ //dgvtimbooktheoten.Rows[i].Cells[j].Text.ToString();
}
}
}
catch (Exception)
{
Response.Write("<script>alert('không thể xuất fiel excel, kiểm tra lại');</script>");
}
}
public ExcelFile()
{
Console.WriteLine("I'm doing something!");
//Start Excel and get Application object
app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
//Get a new workbook
workBook = app.Workbooks.Add("");
workSheet = workBook.ActiveSheet;
//Add table headers going cell by cell
workSheet.Cells[1, 1] = "Class name";
workSheet.Cells[1, 2] = "Time";
//Format A1 and B1 as bold, vertical alignment = center.
workSheet.Range["A1", "B1"].Font.Bold = true;
workSheet.Range["A1", "B1"].VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//Create an array to multiple values at once
string[,] className = new string[10, 1];
className[0, 0] = "Smart Bubble Sort";
className[1, 0] = "Bubble Sort";
string[,] timeSpent = new string[10, 1];
timeSpent[0, 0] = "0.12";
//Fill with the name of the class name
workSheet.Range["A2", "A10"].Value2 = className;
//Fill with the time specific for all the classes
workSheet.Range["B2", "B10"].Value2 = timeSpent;
//AutoFit columns A and B
range = workSheet.Range["A1", "B1"];
range.EntireColumn.AutoFit();
app.Visible = false;
app.UserControl = false;
try
{
workBook.SaveAs("C:\\Users\\codru\\Desktop\\ExcelData.xls");
}
catch (Exception)
{
}
workBook.Close();
}
public void DataTableToExcel()
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
try
{
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = true;
// 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 = "Employees";
// storing header part in Excel
for (int i = 1; i < mIS_FTTIMECARTSQL1DataGridView.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = mIS_FTTIMECARTSQL1DataGridView.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i <= mIS_FTTIMECARTSQL1DataGridView.Rows.Count - 1; i++)
{
for (int j = 0; j < mIS_FTTIMECARTSQL1DataGridView.Columns.Count; j++)
{
string values = string.Empty;
values = mIS_FTTIMECARTSQL1DataGridView.Rows[i].Cells[j].Value.ToString();
worksheet.Cells[i + 2, j + 1] = values;
}
}
}
finally
{
//Release the resources
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
}
private void DataReport(string filename)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workBook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet workSheet = null;
workSheet = workBook.Sheets["Sheet1"];
workSheet = workBook.ActiveSheet;
workSheet.Name = "Sale Report";
app.Columns.ColumnWidth = 30;
app.DisplayAlerts = false;
System.Globalization.CultureInfo _cultureTHInfo = new System.Globalization.CultureInfo("th-TH");
DateTime dt;
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
workSheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (j == 0) // Datetime
{
dt = Convert.ToDateTime(dataGridView1.Rows[i].Cells[j].Value.ToString());
workSheet.Cells[i + 2, j + 1] = dt.ToString("dd MMM yyyy", _cultureTHInfo);
}
else
{
workSheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
}
string lastRow = "H" + Convert.ToString(dataGridView1.Rows.Count);
workSheet.get_Range("A1", lastRow).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
workSheet.get_Range("A1", "H1").Cells.Font.Bold = true; // Header
workBook.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workBook.Close();
app.Quit();
}
private void exportToExcel_Click(object sender, EventArgs e)
{
// 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;
// see the excel sheet behind the program
app.Visible = true;
// 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 = "Exported from gridview";
// storing header part in Excel
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView3.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView3.Columns.Count; j++)
{
if (dataGridView3.Rows[i].Cells[j].Value != null)
{
worksheet.Cells[i + 2, j + 1] = dataGridView3.Rows[i].Cells[j].Value.ToString();
}
else
{
worksheet.Cells[i + 2, j + 1] = String.Empty;
}
}
}
// save the application
try
{
workbook.SaveAs("c:\\output.xls", 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 ex)
{
}// Exit from the application
app.Quit();
}
private void Excel()
{
if (!ErrorCheck())
{
return;
}
if (dtData.Columns.Contains("AdminNO"))
{
dtData.Columns.Remove("AdminNO");
}
string folderPath = "C:\\Excel\\";
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
SaveFileDialog savedialog = new SaveFileDialog();
savedialog.Filter = "Excel Files|*.xlsx;";
savedialog.Title = "Save";
savedialog.FileName = "ZaikoShoukai";
savedialog.InitialDirectory = folderPath;
savedialog.RestoreDirectory = true;
if (savedialog.ShowDialog() == DialogResult.OK)
{
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(dtData, "worksheet");
wb.SaveAs(savedialog.FileName);
bbl.ShowMessage("I203", string.Empty, string.Empty, string.Empty, string.Empty, string.Empty);
}
Process.Start(Path.GetDirectoryName(savedialog.FileName));
}
}
}
public int print(bool b)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
string dir = System.IO.Directory.GetCurrentDirectory();
dir += "./../../xls/Extrusion/B/SOP-MFG-301-R02A 吹膜生产日报表.xlsx";
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(dir);
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
// 修改Sheet中某行某列的值
fill_excel(my);
my.PageSetup.RightFooter = dt_prodinstr.Rows[0]["生产指令"] + "-02-001 &P/" + wb.ActiveSheet.PageSetup.Pages.Count;
if (b)
{
// 设置该进程是否可见
oXL.Visible = true;
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
return(0);
}
else
{
int pageCount = 0;
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
}
catch
{ }
finally
{
pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
}
return(pageCount);
}
}
private void _btnExport_Click(object sender, EventArgs e)
{
// 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;
// see the excel sheet behind the program
app.Visible = true;
// 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 = "Inventario";
// storing header part in Excel
for (int i = 1; i < _dgvInventary.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = _dgvInventary.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < _dgvInventary.Rows.Count - 1; i++)
{
for (int j = 0; j < _dgvInventary.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = _dgvInventary.Rows[i].Cells[j].Value.ToString();
}
}
// save the application
string root = @"C:\Registros\Inventarios";
//string subdir = @"C:\Temp\Mahesh";
// If directory does not exist, create it.
if (!Directory.Exists(root))
{
Directory.CreateDirectory(root);
}
DateTime Savetoday = DateTime.Today;
string _sSaveMonth = Savetoday.ToString("dd-MM-yyyy");
workbook.SaveAs("c:\\Registros\\Inventarios\\Inventario_" + _sSaveMonth + ".xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Exit from the application
MessageBox.Show("Datos exportados exitosamente!");
}
private void ImportToExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
if (dataGridView1.Rows.Count == 0)
{
MessageBox.Show("DataGrid is empty!");
return;
}
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1);
worksheet = workbook.ActiveSheet;
worksheet.Name = "Report";
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
worksheet.Range["A1", "S1"].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
var value = dataGridView1.Rows[i].Cells[j].Value;
if (value == null)
{
value = "null";
}
worksheet.Cells[i + 2, j + 1] = value.ToString();
}
}
var saveDialog = new SaveFileDialog();
saveDialog.FileName = "Report " + DateTime.UtcNow.Date.ToString("dd.MM.yyyy");
saveDialog.DefaultExt = ".xlsx";
if (saveDialog.ShowDialog() == DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
app.Quit();
}
private void button4_Click(object sender, EventArgs e)
{
try
{
DialogResult dialogResult = MessageBox.Show("Are you sure you want to Reset Data?", "Some Title", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
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 = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (dataGridView1.Rows[i].Cells[j].Value != null)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
else
{
worksheet.Cells[i + 2, j + 1] = "";
}
}
}
con.connection();
con.qurry("update sales_s set item_price=0,item_cost=0,no_of_s=0;");
con.nonqurry();
}
else if (dialogResult == DialogResult.No)
{
MessageBox.Show("aaaaaaaaaaa");
}
}
catch (Exception EX)
{
MessageBox.Show(EX.Message);
}
}
public void GridToExcel(DataGridView Grid, string SheetName)
{
//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 = SheetName;
//storing Each row and column value to excel sheet
for (int i = 1; i < Grid.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = Grid.Columns[i - 1].HeaderText;
}
for (int i = 0; i < Grid.Rows.Count; i++)
{
for (int j = 0; j < Grid.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = Grid.Rows[i].Cells[j].Value.ToString();
}
}
//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 = 2;
if (saveDialog.ShowDialog() == DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful", "Mr Solution", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
public static void Write(List <SimulationData> excelData)
{
foreach (var process in System.Diagnostics.Process.GetProcessesByName("Excel"))
{
process.Kill();
}
oXL = new Microsoft.Office.Interop.Excel.Application
{
Visible = true
};
//Add new Excel workbook
oWB = oXL.Workbooks.Add("");
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
oSheet.EnableSelection = Microsoft.Office.Interop.Excel.XlEnableSelection.xlNoSelection;
var i = 1;
foreach (var data in excelData)
{
//Populate excel worksheet with data
oSheet.Cells[i, 1] = data.Time;
oSheet.Cells[i, 2] = data.Buffer;
oSheet.Cells[i, 3] = data.BandWidth;
oSheet.Cells[i, 4] = data.BitRate;
i += 1;
}
//Draw line chart based on data
Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)oSheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject myChart = xlCharts.Add(10, 80, 300, 250);
Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;
chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;
var seriesCollection = (Microsoft.Office.Interop.Excel.SeriesCollection)chartPage.SeriesCollection();
Microsoft.Office.Interop.Excel.Series series1 = seriesCollection.NewSeries();
series1.Name = "Długość Bufora";
series1.XValues = oSheet.get_Range("A1", "A" + i);
series1.Values = oSheet.get_Range("B1", "B" + i);
Microsoft.Office.Interop.Excel.Series series2 = seriesCollection.NewSeries();
series2.Name = "Pasmo";
series2.XValues = oSheet.get_Range("A1", "A" + i);
series2.Values = oSheet.get_Range("C1", "C" + i);
Microsoft.Office.Interop.Excel.Series series3 = seriesCollection.NewSeries();
series3.Name = "Przeplywnosc fragmentu";
series3.XValues = oSheet.get_Range("A1", "A" + i);
series3.Values = oSheet.get_Range("D1", "D" + i);
}
//输入用户姓名是否合法
//private bool input_Judge()
//{
// //int index = dataGridView1.Rows.Count - 1;
// //string str登记员=dt台帐.Rows[index]["登记员"].ToString();
// //string str审核员 = dt台帐.Rows[index]["审核员"].ToString();
// //if (mySystem.Parameter.NametoID(str登记员) <= 0 || mySystem.Parameter.NametoID(str审核员)<=0)
// //{
// // return false;
// //}
// //else
// // return true;
//}
//打印功能
public int print(bool isShow)
{
// 打开一个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\miejun\SOP-MFG-106-R03A 辐照灭菌台帐.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[2];
if (isShow)
{
//true->预览
// 设置该进程是否可见
oXL.Visible = true;
// 修改Sheet中某行某列的值
my = printValue(my, wb);
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
return(0);
}
else
{
//false->打印
// 设置该进程是否可见
oXL.Visible = false;
// 修改Sheet中某行某列的值
my = printValue(my, wb);
try
{
// 直接用默认打印机打印该Sheet
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);
return(pageCount);
}
}
/*
* @brief Btn_MkExcel_Click
* @note DummyData で Test1.xlsx を作成(グラフなし)
*/
private void Btn_MkExcel_Click(object sender, EventArgs e)
{
string ExcelBookFileName = "C:\\TEMP\\Test1.xlsx";
Microsoft.Office.Interop.Excel.Application ExcelApp
= new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Visible = false;
Microsoft.Office.Interop.Excel._Workbook wb = ExcelApp.Workbooks.Add();
Microsoft.Office.Interop.Excel._Worksheet ws1 = wb.Sheets[1];
ws1.Select(Type.Missing);
//----- Excel Data 作成 ------
// Cells[y, x]
Microsoft.Office.Interop.Excel.Range rgn;
// 列のヘッダ情報作成
rgn = ws1.Cells[1, 2]; rgn.Value2 = 2014;
rgn = ws1.Cells[1, 3]; rgn.Value2 = 2015;
rgn = ws1.Cells[1, 4]; rgn.Value2 = "2016";
// 縦のヘッダ情報作成
rgn = ws1.Cells[2, 1]; rgn.Value2 = "Data1";
rgn = ws1.Cells[3, 1]; rgn.Value2 = "Data2";
rgn = ws1.Cells[4, 1]; rgn.Value2 = "Data3";
rgn = ws1.Cells[5, 1]; rgn.Value2 = "Data4";
// 1列目(2014)データ
rgn = ws1.Cells[2, 2]; rgn.Value2 = 12;
rgn = ws1.Cells[3, 2]; rgn.Value2 = 56;
rgn = ws1.Cells[4, 2]; rgn.Value2 = 52;
rgn = ws1.Cells[5, 2]; rgn.Value2 = 30;
// 2列目(2015)データ
rgn = ws1.Cells[2, 3]; rgn.Value2 = 15;
rgn = ws1.Cells[3, 3]; rgn.Value2 = 73;
rgn = ws1.Cells[4, 3]; rgn.Value2 = 61;
rgn = ws1.Cells[5, 3]; rgn.Value2 = 32;
// 2列目(2016)データ
rgn = ws1.Cells[2, 4]; rgn.Value2 = 21;
rgn = ws1.Cells[3, 4]; rgn.Value2 = 86;
rgn = ws1.Cells[4, 4]; rgn.Value2 = 69;
rgn = ws1.Cells[5, 4]; rgn.Value2 = 0;
wb.SaveAs(ExcelBookFileName);
wb.Close(false);
ExcelApp.Quit();
MessageBox.Show("Made Dummy Excel Data");
}
private void ToExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
Microsoft.Office.Interop.Excel._Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
excelApp.Visible = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "export";
// Storing header
for (int i = 1; i < dgvTickets.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dgvTickets.Columns[i - 1].HeaderText;
}
// Storing cell values
for (int i = 0; i < dgvTickets.Rows.Count - 1; i++)
{
for (int j = 0; j < dgvTickets.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dgvTickets.Rows[i].Cells[j].Value.ToString();
}
}
string desktoppath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
if (desktoppath.Substring(desktoppath.Length - 1) != @"\")
{
desktoppath += @"\";
}
desktoppath += "Tickets.xlsx";
workbook.SaveAs(desktoppath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//exit application
excelApp.Quit();
DisplayStatus(desktoppath + " saved.", StatusTypes.success);
}
catch (Exception ex)
{
DisplayStatus(ex.Message, StatusTypes.error);
}
}
public void ExportToExcel(DataGridView dgView)
{
// 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 = "ExportedFromDatGrid";
int i = 0;
int j = 0;
for (i = 0; i <= dgView.RowCount - 1; i++)
{
for (j = 0; j <= dgView.ColumnCount - 1; j++)
{
DataGridViewCell cell = dgView[j, i];
worksheet.Cells[i + 1, j + 1] = cell.Value;
}
}
//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 = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
private void Button1_Click(object sender, EventArgs e)
{
// 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;
// see the excel sheet behind the program
app.Visible = true;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
SlaughterDate.CustomFormat = "ddMMMyyyy";
worksheet.Name = SlaughterDate.Text;
// storing header part in Excel
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
worksheet.Cells[i + 2, j + 1] = cell.Value;
//worksheet.EnableSelection = Microsoft.Office.Interop.Excel.XlEnableSelection.xlNoSelection;
}
}
// save the application
this.Close();
// MessageBox.Show("Data Exported Succesfully");
}
public string StateTaxExcel(List <string> list)
{
var indexs = MapStateTaxToExcel(list);
Microsoft.Office.Interop.Excel.Application oXL = null;
Microsoft.Office.Interop.Excel._Workbook oWB = null;
Microsoft.Office.Interop.Excel._Worksheet oSheet = null;
oXL = new Microsoft.Office.Interop.Excel.Application();
oWB = oXL.Workbooks.Open("D:\\Users\\staff\\Desktop\\Book1.xlsx");
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.Worksheets["Sheet2"];
for (var i = 1; i <= list.Count; i++)
{
var countParent = 1;
JObject obj = JObject.Parse(list[i - 1]);
oSheet.Cells[i + 1, countParent++] = ((string)obj["federalTaxNumber"] ?? "");
oSheet.Cells[i + 1, countParent++] = ((string)obj["createdOnn"] ?? "");
oSheet.Cells[i + 1, countParent++] = ((string)obj["name"] ?? "");
oSheet.Cells[i + 1, countParent++] = ((string)obj["tradeName"] ?? "");
if ((obj["taxPayer"]) != null)
{
var couPhone = countParent;
var x = 0;
for (x = 0; x < obj["taxPayer"].Count(); x++)
{
oSheet.Cells[i + 1, couPhone++] = ((string)obj["taxPayer"][x]["state"]["abbreviation"] ?? "");
oSheet.Cells[i + 1, couPhone++] = ((string)obj["taxPayer"][x]["stateTaxNumber"] ?? "");
oSheet.Cells[i + 1, couPhone++] = ((string)obj["taxPayer"][x]["statusStateTax"] ?? "");
}
}
countParent = indexs.Item2;
}
oWB.Save();
if (oWB != null)
{
oWB.Close();
}
return(null);
}
private bool XLSConvertToPDF(string sourcePath, string targetPath)
{
bool result = false;
Microsoft.Office.Interop.Excel.XlFixedFormatType targetType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.Application ExcelApp = null;
Microsoft.Office.Interop.Excel._Workbook ExcelBook = null;
try
{
object target = targetPath;
object type = targetType;
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
ExcelApp = new Microsoft.Office.Interop.Excel.Application();
System.Reflection.Missing missingValue = System.Reflection.Missing.Value;
ExcelBook = ExcelApp.Workbooks.Open(sourcePath, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
ExcelBook.ExportAsFixedFormat(targetType, target, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
catch
{
result = false;
}
finally
{
if (ExcelBook != null)
{
ExcelBook.Close(true, missing, missing);
ExcelBook = null;
}
if (ExcelApp != null)
{
ExcelApp.Quit();
ExcelApp = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
private void btn_xuatexcel_Click(object sender, EventArgs e)
{
try
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "excel";
for (int i = 1; i < data.Columns.Count + 1; i++)
{
if (data.Columns[i - 1].Visible == true)
{
worksheet.Cells[1, i] = data.Columns[i - 1].HeaderText;
}
}
for (int i = 0; i < data.RowCount; i++)
{
for (int j = 0; j < data.Columns.Count; j++)
{
if (data.Columns[j].Visible == true)
{
if (data.Rows[i].Cells[j].Value != null)
{
worksheet.Cells[i + 2, j + 1] = data.Rows[i].Cells[j].Value.ToString();
}
}
}
}
var SaveFileDialog = new SaveFileDialog();
SaveFileDialog.FileName = "output";
SaveFileDialog.DefaultExt = ".xlsx";
if (SaveFileDialog.ShowDialog() == DialogResult.OK)
{
workbook.SaveAs(SaveFileDialog.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
app.Quit();
}
catch
{
MessageBox.Show("Không thể xuất file excel", "THÔNG BÁO");
}
}
static void Main(string[] args)
{
string strPath =
System.AppDomain.CurrentDomain.BaseDirectory.Replace("\\", "/");
// Instantiate Object
APServer.Server server = new APServer.Server();
// Path and filename of output
server.NewDocumentName = "Server.ExcelPrinting.pdf";
server.OutputDirectory = strPath;
// Start the print job
ServerDK.Results.ServerResult result = server.BeginPrintToPDF();
if (result.ServerStatus == ServerDK.Results.ServerStatus.Success)
{
// Automate Excel to print a document to activePDF Server
// NOTE: You must add a reference to the
// Microsoft.Office.Interop.Excel library found in the
// reference manager under Assemblies -> Extensions
Microsoft.Office.Interop.Excel._Application oXLS =
new Microsoft.Office.Interop.Excel.Application();
oXLS.DisplayAlerts = false;
oXLS.Visible = false;
object m = System.Type.Missing;
Microsoft.Office.Interop.Excel._Workbook oWB =
oXLS.Workbooks.Open(
$"{strPath}Server.Excel.Input.xlsx", m, true, m, m, m,
true, m, m, false, false, m, false);
oWB.Activate();
oWB.PrintOut(1, 999, 1, false, server.NewPrinterName, false, false);
oWB.Close(0);
oXLS.Quit();
// Wait(seconds) for job to complete
result = server.EndPrintToPDF(waitTime: 30);
}
// Output result
WriteResult(result);
// Process Complete
Console.WriteLine("Done!");
Console.WriteLine("Press any key to exit.");
Console.ReadKey();
}
public static bool ExportbyExcel(string location)
{
try
{
DataTable dtb = getExcelListCustomer();
string name = (DateTime.Now.Hour * 3600 + DateTime.Now.Minute * 60 + DateTime.Now.Second).ToString() + ".xlsx";
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = true;
// 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 = "Exported from gridview";
// storing header part in Excel
for (int i = 1; i < dtb.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dtb.Columns[i - 1].ColumnName;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dtb.Rows.Count; i++)
{
for (int j = 0; j < dtb.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dtb.Rows[i][j].ToString();
}
}
workbook.SaveAs(location + @"\" + name, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Exit from the application
app.Visible = false;
app.Quit();
// save the application
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
