public Excel2(String titolo)
{
excelWorkBook = excelApplication.Workbooks.Add();
excelApplication.DisplayAlerts = false;
filename = @"C:\report\" + titolo + "_" + DateTime.Today.Month + "-" + DateTime.Today.Year + ".xls";
excelApplication.Cells[1, 1] = "Applicazione";
excelApplication.Cells[1, 2] = "Data";
excelApplication.Cells[1, 3] = "Linguaggio";
excelApplication.Cells[1, 4] = "Files";
excelApplication.Cells[1, 5] = "Righe";
excelApplication.Cells[1, 6] = "Utenti";
((Microsoft.Office.Interop.Excel.Range)excelApplication.Cells[1, 1]).EntireRow.Font.Bold = true;
((Microsoft.Office.Interop.Excel.Range)excelApplication.Cells[1, 1]).EntireColumn.AutoFit();
((Microsoft.Office.Interop.Excel.Range)excelApplication.Cells[1, 2]).ColumnWidth = 10;
((Microsoft.Office.Interop.Excel.Range)excelApplication.Cells[1, 3]).EntireColumn.AutoFit();
((Microsoft.Office.Interop.Excel.Range)excelApplication.Cells[1, 4]).EntireColumn.AutoFit();
((Microsoft.Office.Interop.Excel.Range)excelApplication.Cells[1, 5]).EntireColumn.AutoFit();
((Microsoft.Office.Interop.Excel.Range)excelApplication.Cells[1, 6]).EntireColumn.AutoFit();
excelWorkBook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, false, false, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value);
}
/// <summary>
/// Экспортирует тестовые случаи в Excel файл.
/// </summary>
public void ExportTestCases()
{
try
{
CreateExcelTableSettings();
//по тестовым ситуациям плана
try
{
IList <ITestCase> planCases = teamProject.TestCases.Query("Select * From TestCase").ToList();
if (planCases != null && planCases.Count > 0)
{
ByTestCases(planCases);
}
}
catch { }
//по тестовым случаям
ByTestSuites();
EndingExcelSettings();
xlWorkBook.SaveAs(filePath + "\\" + testFileName + ".xls", Excel.XlFileFormat.xlWorkbookNormal,
misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlShared, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlApp);
releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void ExportDTToExcel(System.Data.DataTable dt)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
// Headers.
for (int i = 0; i < dt.Columns.Count; i++)
{
ws.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
// Content.
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
ws.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
}
// Lots of options here. See the documentation.
wb.SaveAs("d:\\tsst.xlsx");
wb.Close();
app.Quit();
}
public void ExportToExcel()
{
xlApp.DisplayAlerts = false;
FolderBrowserDialog fbd = new FolderBrowserDialog();
fbd.ShowDialog();
if (fbd.SelectedPath != "")
{
String date = DateTime.Now.Day + "_" + DateTime.Now.Month + "_" + DateTime.Now.Year;
string filepath = fbd.SelectedPath + "\\حسابات العملاء.xlsx";
int i = 1;
filepath = fbd.SelectedPath + "\\حسابات العملاء" + "_" + date + ".xls";
list_export = xlApp.Workbooks.Add();
Worksheet_export = list_export.Worksheets.Add();
Worksheet_export.Name = "حسابات العملاء";
Worksheet_export.DisplayRightToLeft = true;
if (allClientsCheckBox.Checked == false)
{
Worksheet_export.Cells[1, 2] = "اسم العميل";
Worksheet_export.Cells[1, 3] = clientNameComboBox.Text;
}
Worksheet_export.Cells[2, 6] = "ملاحظات";
Worksheet_export.Cells[2, 5] = "تاريخ";
Worksheet_export.Cells[2, 4] = "البيان";
Worksheet_export.Cells[2, 3] = "دائن";
Worksheet_export.Cells[2, 2] = "مدين";
Worksheet_export.Cells[2, 1] = "إسم العميل";
int iii = 3;
for (int ii = 0; ii < dGridV_InvenLog.Rows.Count; ii++)
{
DataGridViewRow tempRow = dGridV_InvenLog.Rows[ii];
int cell = 1;
for (int j = 1; j < tempRow.Cells.Count; j++)
{
Worksheet_export.Cells[iii, cell].Value = tempRow.Cells[j].Value;
cell++;
}
iii = iii + 1;
}
Worksheet_export.Columns.AutoFit();
// Worksheet_export.Rows.AutoFit();
list_export.SaveAs(filepath);
xlApp.Quit();
MessageBox.Show("تم حفظ الملف بنجاح");
}
}
public void ExportToExcel()
{
xlApp.DisplayAlerts = false;
FolderBrowserDialog fbd = new FolderBrowserDialog();
fbd.ShowDialog();
if (fbd.SelectedPath != "")
{
String date = DateTime.Now.Day + "_" + DateTime.Now.Month + "_" + DateTime.Now.Year;
string filepath = fbd.SelectedPath + "\\حركة الأصناف.xlsx";
int i = 1;
filepath = fbd.SelectedPath + "\\حركة الأصناف" + "_" + date + ".xls";
list_export = xlApp.Workbooks.Add();
Worksheet_export = list_export.Worksheets.Add();
Worksheet_export.DisplayRightToLeft = true;
Worksheet_export.Name = "حركة الأصناف";
Worksheet_export.Cells[1, 12] = "ملاحظات";
Worksheet_export.Cells[1, 11] = "تاريخ";
Worksheet_export.Cells[1, 10] = "رقم الفاتورة";
Worksheet_export.Cells[1, 9] = "سعر الشراء";
Worksheet_export.Cells[1, 8] = "سعر البيع";
Worksheet_export.Cells[1, 7] = "الكمية بعد الحركة";
Worksheet_export.Cells[1, 6] = "الكمية قبل الحركة";
Worksheet_export.Cells[1, 5] = "وارد";
Worksheet_export.Cells[1, 4] = "صادر";
Worksheet_export.Cells[1, 3] = "اسم الصنف";
Worksheet_export.Cells[1, 2] = "نوع الصنف";
Worksheet_export.Cells[1, 1] = "كود الصنف";
int iii = 2;
for (int ii = 0; ii < dGridV_InvenLog.Rows.Count; ii++)
{
DataGridViewRow tempRow = dGridV_InvenLog.Rows[ii];
int cell = 12;
for (int j = tempRow.Cells.Count - 1; j >= 0; j--)
{
Worksheet_export.Cells[iii, cell].Value = tempRow.Cells[j].Value;
cell--;
}
iii = iii + 1;
}
Worksheet_export.Columns.AutoFit();
// Worksheet_export.Rows.AutoFit();
list_export.SaveAs(filepath);
xlApp.Quit();
MessageBox.Show("تم حفظ الملف بنجاح");
}
}
public void ExportToExcel()
{
xlApp.DisplayAlerts = false;
FolderBrowserDialog fbd = new FolderBrowserDialog();
fbd.ShowDialog();
if (fbd.SelectedPath != "")
{
String date = DateTime.Now.Day + "_" + DateTime.Now.Month + "_" + DateTime.Now.Year;
string filepath = fbd.SelectedPath + "\\رصيد المخزن.xlsx";
int i = 1;
filepath = fbd.SelectedPath + "\\رصيد المخزن" + "_" + date + ".xls";
list_export = xlApp.Workbooks.Add();
Worksheet_export = list_export.Worksheets.Add();
Worksheet_export.Name = "رصيد المخزن";
Worksheet_export.DisplayRightToLeft = true;
Worksheet_export.Cells[1, 8] = "سعر القطاعي";
Worksheet_export.Cells[1, 7] = "سعر الجملة";
Worksheet_export.Cells[1, 6] = "سعر الشراء";
Worksheet_export.Cells[1, 5] = "الرصيد";
Worksheet_export.Cells[1, 4] = "وصف الصنف";
Worksheet_export.Cells[1, 3] = "اسم الصنف";
Worksheet_export.Cells[1, 2] = "نوع الصنف";
Worksheet_export.Cells[1, 1] = "كود الصنف";
int iii = 2;
for (int ii = 0; ii < dGridV_prods.Rows.Count; ii++)
{
DataGridViewRow tempRow = dGridV_prods.Rows[ii];
int cell = 1;
for (int j = 0; j < tempRow.Cells.Count; j++)
{
Worksheet_export.Cells[iii, cell].Value = tempRow.Cells[j].Value;
cell++;
}
iii = iii + 1;
}
Worksheet_export.Columns.AutoFit();
// Worksheet_export.Rows.AutoFit();
list_export.SaveAs(filepath);
xlApp.Quit();
MessageBox.Show("تم حفظ الملف بنجاح");
}
}
public void output_excel(string strFileName)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
System.Windows.MessageBox.Show("无法创建excel对象，可能您的系统没有安装excel");
return;
}
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
//添加列表头
DataSet temp_dataset = MySqlHelper.GetDataSet("Database='" + ShuJuKu.ShuJuKu_Name + "';Data Source='localhost';User Id='root';Password='******';charset='utf8';pooling=true", CommandType.Text, "show columns from " + ShuJuKu.Table1_ShiJIna_JieDian + ";", null);
DataRowCollection temp_dataRow = temp_dataset.Tables[0].Rows;
for (int i = 0; i < temp_dataRow.Count; i++)
{
//listView_tt.Columns.Add(temp_dataRow[i][0].ToString(), 50);
xlApp.Cells[1, (i + 1)] = temp_dataRow[i][0].ToString();
}
//string dataSet_temp_str = "select * from " + ShuJuKu.Table1_ShiJIna_JieDian + " where `Date`>=\"" + date_begin.ToString() + "\" and `Date`<=\"" + date_end.ToString() + "\" order by `Date` desc";
//string dataSet_temp_str = "select * from test5 order by `Date` desc";
DataSet dataSet_temp = MySqlHelper.GetDataSet("Database='" + ShuJuKu.ShuJuKu_Name + "';Data Source='localhost';User Id='root';Password='******';charset='utf8';pooling=true", CommandType.Text, chaxun_command_str, null);
DataRowCollection temp_DataRow = dataSet_temp.Tables[0].Rows;//获取列
//将ListView中的数据导入Excel中
for (int i = 0; i < temp_DataRow.Count; i++)
{
for (int j = 0; j < temp_dataRow.Count; j++)
{
//注意这个在导出的时候加了“\t” 的目的就是避免导出的数据显示为科学计数法。可以放在每行的首尾。
xlApp.Cells[(i + 2), (j + 1)] = temp_DataRow[i][j].ToString() + "\t";
}
}
//例外需要说明的是用strFileName,Excel.XlFileFormat.xlExcel9795保存方式时 当你的Excel版本不是95、97 而是2003、2007 时导出的时候会报一个错误：异常来自 HRESULT:0x800A03EC。 解决办法就是换成strFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal。
xlBook.SaveAs(strFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, false, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlBook.Close();
xlApp = null;
xlBook = null;
System.Windows.MessageBox.Show("生成报表完成");
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="pSaveFileName">默认文件名</param>
/// <param name="pDGVQ">数据源，一个页面上的DataGridView控件</param>
static public bool ExportExcel(string pSaveFileName, DataGridView pDGVQ, bool pMessageBox = true, string pWookSheetName = "Sheet")
{
Microsoft.Office.Interop.Excel.Application xlApp;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
//设置禁止弹出保存和覆盖的询问提示框
xlApp.DisplayAlerts = false;
xlApp.AlertBeforeOverwriting = false;
}
catch (Exception)
{
MessageBox.Show("无法创建Excel对象,请确认已安装Excel。", "对象创建错误", MessageBoxButtons.OK);
return(false);
}
finally
{
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
workbook.Worksheets.Add();
Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet1.Name = pWookSheetName;
writeData(worksheet1, pDGVQ);
bool fileSaved = false;
if (pSaveFileName != "")
{
try
{
workbook.SaveAs(pSaveFileName, 56);
workbook.Saved = true;
fileSaved = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + " 文件可能正在使用。", "导出错误", MessageBoxButtons.OK);
return(false);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
if (fileSaved && pMessageBox)
{
MessageBox.Show(pSaveFileName + " 导出成功", "提示", MessageBoxButtons.OK);
}
return(true);
}
public void ExportToExcel()
{
xlApp.DisplayAlerts = false;
FolderBrowserDialog fbd = new FolderBrowserDialog();
fbd.ShowDialog();
if (fbd.SelectedPath != "")
{
String date = DateTime.Now.Day + "_" + DateTime.Now.Month + "_" + DateTime.Now.Year;
string filepath = fbd.SelectedPath + "\\تقارير الخزينة.xlsx";
int i = 1;
filepath = fbd.SelectedPath + "\\تقارير الخزينة" + "_" + date + ".xls";
list_export = xlApp.Workbooks.Add();
Worksheet_export = list_export.Worksheets.Add();
Worksheet_export.Name = "تقارير الخزينة";
Worksheet_export.DisplayRightToLeft = true;
Worksheet_export.Cells[1, 5] = "ملاحظات";
Worksheet_export.Cells[1, 4] = "نوع الحركة";
Worksheet_export.Cells[1, 3] = "وارد";
Worksheet_export.Cells[1, 2] = "صادر";
Worksheet_export.Cells[1, 1] = "تاريخ";
int iii = 2;
for (int ii = 0; ii < dGridV_InvenLog.Rows.Count; ii++)
{
DataGridViewRow tempRow = dGridV_InvenLog.Rows[ii];
int cell = 1;
for (int j = 0; j < tempRow.Cells.Count; j++)
{
Worksheet_export.Cells[iii, cell].Value = tempRow.Cells[j].Value;
cell++;
}
iii = iii + 1;
}
Worksheet_export.Columns.AutoFit();
// Worksheet_export.Rows.AutoFit();
list_export.SaveAs(filepath);
xlApp.Quit();
MessageBox.Show("تم حفظ الملف بنجاح");
}
}
/// <summary>
/// 具体导出的方法
/// </summary>
/// <param name="listView">ListView</param>
/// <param name="strFileName">导出到的文件名</param>
private void DoExport(string strFileName)
{
int rowNum = listView_Data.Items.Count;
int columnNum = listView_Data.Columns.Count;
//int columnNum = listView_Data.Items[0].SubItems.Count;
int rowIndex = 1;
int columnIndex = 0;
if (rowNum == 0 || string.IsNullOrEmpty(strFileName))
{
return;
}
if (rowNum > 0)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建excel对象，可能您的系统没有安装excel");
return;
}
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
//将ListView的列名导入Excel表第一行
foreach (ColumnHeader dc in listView_Data.Columns)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = dc.Text;
}
//将ListView中的数据导入Excel中
for (int i = 0; i < rowNum; i++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
columnIndex++;
//注意这个在导出的时候加了“\t” 的目的就是避免导出的数据显示为科学计数法。可以放在每行的首尾。
xlApp.Cells[rowIndex, columnIndex] = Convert.ToString(listView_Data.Items[i].SubItems[j].Text) + "\t";
}
}
//例外需要说明的是用strFileName,Excel.XlFileFormat.xlExcel9795保存方式时 当你的Excel版本不是95、97 而是2003、2007 时导出的时候会报一个错误：异常来自 HRESULT:0x800A03EC。 解决办法就是换成strFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal。
xlBook.SaveAs(strFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlApp = null;
xlBook = null;
MessageBox.Show("OK");
}
}
private static string WriteWorkBookToTempFile(Microsoft.Office.Interop.Excel.Workbook wb)
{
string TempPath = System.AppDomain.CurrentDomain.BaseDirectory + "Temp";
if (!Directory.Exists(TempPath))
{
Directory.CreateDirectory(TempPath);
}
string TempFileName = TempPath + "/" + System.Guid.NewGuid().ToString("N") + ".xls";
wb.SaveAs(TempFileName);
return(TempFileName);
}
public bool SaveAs(object FileName)
//文档另存为
{
try
{
wb.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);
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public void ConvertExcel(string savePath)
{
//将xml文件转换为标准的Excel格式
Object Nothing = Missing.Value; //由于yongCOM组件很多值需要用Missing.Value代替
Microsoft.Office.Interop.Excel.Application ExclApp = new Microsoft.Office.Interop.Excel.Application(); // 初始化
Microsoft.Office.Interop.Excel.Workbook ExclDoc = ExclApp.Workbooks.Open(savePath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing); //打开Excl工作薄
try
{
Object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal; //获取Excl 2007文件格式
ExclApp.DisplayAlerts = false;
ExclDoc.SaveAs(savePath, format, Nothing, Nothing, Nothing, Nothing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing); //保存为Excl 2007格式
}
catch (Exception ex) { }
ExclDoc.Close(Nothing, Nothing, Nothing);
ExclApp.Quit();
}
private void DoExport(string excelFileName)
{
int rowNum = listView_Data.Items.Count;
int columnNum = listView_Data.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;
if (rowNum == 0 || string.IsNullOrEmpty(excelFileName))
{
return;
}
if (rowNum > 0)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建excel对象，可能您的系统没有安装excel");
return;
}
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
//将ListView的列名导入Excel表第一行
foreach (ColumnHeader dc in listView_Data.Columns)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = dc.Text;
}
//将ListView中的数据导入Excel中
for (int i = 0; i < rowNum; i++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = Convert.ToString(listView_Data.Items[i].SubItems[j].Text) + "\t";
}
}
xlBook.SaveAs(excelFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlApp = null;
xlBook = null;
MessageBox.Show("OK");
}
}
private void SaveWorkbook()
{
string path = Path.GetDirectoryName(iFileName);
System.IO.DirectoryInfo dirInfo = new System.IO.DirectoryInfo(path);
if (!dirInfo.Exists)
{
dirInfo.Create();
}
System.IO.FileInfo fileInfo = new System.IO.FileInfo(iFileName);
if (fileInfo.Exists)
{
try
{
fileInfo.Delete();
}
catch (Exception)
{
}
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = iExcelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.get_Item(workbooks.Count);
try
{
workbook.SaveAs(iFileName,
Excel.XlFileFormat.xlExcel9795,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);
}
catch (System.IO.IOException)
{
}
workbook.Close(false, Type.Missing, Type.Missing);
}
//*********************************************
// 엑셀 파일 만들기
//*********************************************
private void CreateExcel()
{
try
{
object missingType = Type.Missing;
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Add(missingType);
// 워크 시트 생성
// Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelBook.Worksheets.Add(missingType, missingType, missingType, missingType);
// 워크 시트 첫번째 요소 가져옴
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.Worksheets.get_Item(1);
excelWorksheet.Cells[1, 1] = "학번(Ex:201887082)";
excelWorksheet.Cells[1, 2] = "주민등록번호 앞자리(Ex:990714)";
excelWorksheet.Cells[1, 3] = "주민등록번호 뒷자리(Ex:xxxxxxx)";
excelWorksheet.Cells[1, 4] = "성명(Ex:박재홍)";
excelWorksheet.Cells[1, 5] = "성별(M혹은F로 표기해주세요)";
excelWorksheet.Cells[1, 6] = "학과 및 부서(소프트웨어전공)";
excelWorksheet.Cells[1, 7] = "연락처(01041645367)";
excelWorksheet.Cells[1, 8] = "이메일 주소([email protected])";
excelWorksheet.Cells[1, 9] = "비밀번호(빈칸 등록시 주민번호 앞자리 등록)";
excelBook.SaveAs(
@ExcelFileName, // 유니코드 파일명 변환
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
missingType,
missingType,
missingType,
missingType,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
missingType,
missingType,
missingType,
missingType,
missingType);
excelBook.Close(true, missingType, missingType);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
Console.WriteLine("성공");
notifier.ShowSuccess("성공적으로 다운 받으셨습니다(문서폴더를 열어보세요)");
}
catch
{
notifier.ShowError("Excel 파일 저장 도중에 에러가 발생했습니다(이미 파일을 내려받으셨다면 문서폴더를 열어보십시오)");
}
}
protected bool SaveExcelFile(Microsoft.Office.Interop.Excel.Workbook xlWorkBook, string path)
{
try
{
object misValue = System.Reflection.Missing.Value;
xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public static string ExcelToHtml(string excelFileName)
{
//实例化Excel
Microsoft.Office.Interop.Excel.Application repExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = null;
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
//打开文件，n.FullPath是文件路径
workbook = repExcel.Application.Workbooks.Open(excelFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
string filesavefilename = excelFileName.ToString();
string strsavefilename = filesavefilename.Substring(0, filesavefilename.LastIndexOf('.')) + ".html";
object savefilename = (object)strsavefilename;
object ofmt = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
//进行另存为操作
workbook.SaveAs(savefilename, ofmt, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
object osave = false;
//逐步关闭所有使用的对象
workbook.Close(osave, Type.Missing, Type.Missing);
repExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
//垃圾回收
GC.Collect();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
GC.Collect();
System.Runtime.InteropServices.Marshal.ReleaseComObject(repExcel.Application.Workbooks);
GC.Collect();
System.Runtime.InteropServices.Marshal.ReleaseComObject(repExcel);
repExcel = null;
GC.Collect();
//依据时间杀灭进程
System.Diagnostics.Process[] process = System.Diagnostics.Process.GetProcessesByName("EXCEL");
foreach (System.Diagnostics.Process p in process)
{
if (DateTime.Now.Second - p.StartTime.Second > 0 && DateTime.Now.Second - p.StartTime.Second < 5)
{
p.Kill();
}
}
return(savefilename.ToString());
}
public static bool WriteExcel(string[,] Values, string FileName = "", int TableBegin = 0, int Col = 1, int StartRow = 1)
{
KKS.xls.CheckExcellProcesses();
Microsoft.Office.Interop.Excel.Application oExcel = null;
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;
try
{
oExcel = new Microsoft.Office.Interop.Excel.Application();
xlWorkbook = oExcel.Workbooks.Add();
xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range range = xlWorksheet.get_Range(KKS.Tools.IntToAA(Col) + StartRow, KKS.Tools.IntToAA(Values.GetLength(1) + Col - 1) + (Values.GetLength(0)));
//string Row = KKS.Tools.IntToAA(Col) + "2:" + KKS.Tools.IntToAA(Values.GetLength(1)+ Col-1) + Values.GetLength(0);
range.Value2 = Values;
#region FormatTableAndSheet
range = xlWorksheet.get_Range(KKS.Tools.IntToAA(Col) + (StartRow + TableBegin), KKS.Tools.IntToAA(Values.GetLength(1) + Col - 1) + (Values.GetLength(0)));
range.Columns.AutoFit();
#endregion
if (File.Exists(FileName))
{
File.Delete(FileName);
}
xlWorkbook.SaveAs(FileName);
//xlWorkbook.SaveAs(FileName);
xlWorkbook.Close(false);
}
catch (Exception ex)
{
KKS.KKS_Message.Show(ex.Source + ": " + ex.Message);
}
finally
{
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorksheet);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oExcel);
KKS.xls.KillExcel();
}
return(true);
}
/// <summary>
/// excel 转换为html
/// </summary>
/// <param name="path">要转换的文档的路径</param>
/// <param name="savePath">转换成的html的保存路径</param>
/// <param name="wordFileName">转换后html文件的名字</param>
public static void ExcelToHtml(string path, string savePath, string wordFileName)
{
string str = string.Empty;
Microsoft.Office.Interop.Excel.Application repExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = null;
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
workbook = repExcel.Application.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
object htmlFile = savePath + wordFileName + ".html";
object ofmt = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
workbook.SaveAs(htmlFile, ofmt, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
object osave = false;
workbook.Close(osave, Type.Missing, Type.Missing);
repExcel.Quit();
}
public static bool Excel2Pdf(string source, string target)
{
try
{
var ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook book = ExcelApp.Workbooks.Open(source);
Microsoft.Office.Interop.Excel.XlFileFormat xlFormatPDF = (Microsoft.Office.Interop.Excel.XlFileFormat) 57;
book.SaveAs(target, xlFormatPDF);
ExcelApp.Visible = false;
ExcelApp.Quit();
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(false);
}
//convert existing xls document to xlsx and returns path to file
public static string ConvertExcel(string FilePathRaw, string NewFileName, string DirectoryToSave,
string NewFileExtension, Excel.XlFileFormat NewFileFormat)
{
//calculate file name with exxtension
if (!NewFileName.EndsWith("." + NewFileExtension))
{
NewFileName = NewFileName + "." + NewFileExtension;
}
//get absolute filePaths for both the new directory and the old file path
string directoryToSave = String.IsNullOrEmpty(DirectoryToSave)? Directory.GetCurrentDirectory():Path.GetFullPath(DirectoryToSave);
string xlsFilePath = Path.GetFullPath(FilePathRaw);
//calculate new File Path
string newFilePath = Path.Combine(directoryToSave, NewFileName);
//perform conversion
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbook eWorkbook = excelApp.Workbooks.Open(xlsFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
eWorkbook.SaveAs(newFilePath, NewFileFormat, //Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
eWorkbook.Close(false, Type.Missing, Type.Missing);
excelApp.Quit();
GC.Collect();
/* Excel.Workbook openWorkBook = null;
*
* Excel.Application activeExcel = new Excel.Application();
* openWorkBook = activeExcel.Workbooks.Open(FilePathRaw);
* openWorkBook.SaveAs(newFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8);*/
return(newFilePath);
}
public void ExcelCreate()
{
Microsoft.Office.Interop.Excel._Application uygulama = new Microsoft.Office.Interop.Excel.Application();
uygulama.DisplayAlerts = false;
uygulama.Visible = true;
Microsoft.Office.Interop.Excel.Workbook kitap = uygulama.Workbooks.Add(System.Reflection.Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet sayfa = (Microsoft.Office.Interop.Excel.Worksheet)kitap.Sheets[1];
//Microsoft.Office.Interop.Excel.Range alan = (Microsoft.Office.Interop.Excel.Range)sayfa1.Cells[2, 5];
//alan.Value2 = txtboxName.Text;
// [SATIR , SUTUN]
sayfa.Cells[1, 1] = "Name";
sayfa.Cells[1, 2] = "Surname";
sayfa.Cells[1, 3] = "Tel Num";
sayfa.Cells[1, 4] = "Cost";
sayfa.Cells[1, 5] = "Durum";
kitap.SaveAs(path_excel, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
kitap.Close();
uygulama.Quit();
}
public void Read(string name)
{
Type officeType = Type.GetTypeFromProgID("Excel.Application");
if (officeType == null)
{
// Excel is not installed.
// Show message or alert that Excel is not installed.
return;
}
// Excel is installed.
// Let us continue our work on Excel file conversion.
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
try
{
// While saving, it asks for the user confirmation, whether we want to save or not.
// By setting DisplayAlerts to false, we just skip this alert.
app.DisplayAlerts = false;
// Now we open the upload file in Excel Workbook.
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = app.Workbooks.Open(name);
//string newFileName = System.IO.Directory.GetCurrentDirectory() + "\\DataMigration.csv";
string newFileName = name + ".csv";
// Now save this file as CSV file.
excelWorkbook.SaveAs(newFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV);
// Close the Workbook and Quit the Excel Application at the end.
excelWorkbook.Close();
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
app.Quit();
}
}
public void ExportExcel(string filename, DataTable data, System.Collections.Generic.IList <string> columnNames)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
return;
}
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
worksheet.Cells.NumberFormatLocal = "@";
for (int i = 0; i < columnNames.Count; i++)
{
worksheet.Cells[1, i + 1] = columnNames[i].ToString();
}
for (int j = 1; j <= data.Rows.Count; j++)
{
for (int n = 1; n <= data.Columns.Count; n++)
{
//worksheet.Cells[j + 1, n] = "'"+data.Rows[j - 1][n - 1].ToString();
worksheet.Cells[j + 1, n] = data.Rows[j - 1][n - 1].ToString();
}
}
object objOpt = System.Reflection.Missing.Value;
workbook.SaveAs(filename, objOpt, objOpt, objOpt, objOpt, objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, objOpt, objOpt, objOpt, objOpt, objOpt);
xlApp.DisplayAlerts = false;
xlApp.Visible = true;
xlApp.Quit();
xlApp = null;
workbook = null;
worksheet = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
private void btnExport_Click(object sender, EventArgs e) //자료 내보내기
{
//저장할 디렉토리를 물어보고, 거기에 저장
SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "Excel Files(*.xls)|*.xls";
dlg.Title = "엑셀 파일로 내보내기";
if (dlg.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlworkbook = xlApp.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlworkbook.Worksheets.get_Item(1);
//타이틀
for (int k = 0; k < dgvMember.Columns.Count; k++)
{
xlWorkSheet.Cells[1, k + 1] = dgvMember.Columns[k].Name;
//((Excel.Range)xlSheet.Cells[2, i + 1]).Interior.Color = Excel.XlRgbColor.rgbGhostWhite;
}
for (int i = 0; i < dgvMember.Rows.Count; i++)
{
for (int k = 0; k < dgvMember.Columns.Count; k++)
{
xlWorkSheet.Cells[i + 2, k + 1] = dgvMember[k, i].Value.ToString();
}
}
xlworkbook.SaveAs(dlg.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
xlworkbook.Close(true);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlworkbook);
releaseObject(xlApp);
MessageBox.Show("Excel Export가 완료되었습니다.");
}
}
void saveData()
{
ChooseFolder();
string sifre = sifreTxtBox.Text;
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["Sayfa1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Records";
for (int i = 0; i < sayac; i++)
{
worksheet.Cells[1, i + 1] = kolonlarDizi[i];
}
worksheet.Cells[1, sayac + 1] = "SicilNo (Ondalıksız)";
worksheet.Cells[1, sayac + 2] = "Tarih (Metin)";
//string encPass = encryptionName(sifreTxtBox.Text);
String dosyaAdı;
dosyaAdı = projeAdi.Text + "-" + projeSahibi.Text;
var saveFileDialoge = new SaveFileDialog();
saveFileDialoge.FileName = dosyaAdı;
saveFileDialoge.DefaultExt = ".xlsx";
string lastPath = path.Text;
workbook.SaveAs(lastPath, 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);
path.Text = path.Text + dosyaAdı;
app.Quit();
Application.Exit();
}
private void BotonEscaner_Click(object sender, EventArgs e)
{
string ruta = "C:\\ByMartin\\Clientes\\" + textBox5.Text;
/* string filename = "Excel.exe";
*
* Process proc = new Process();
* proc.EnableRaisingEvents = false;
* proc.StartInfo.FileName = filename;
* proc.StartInfo.Arguments = "";
* proc.Start();
*
*/
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook book = null;
book = excel.Workbooks.Add();
book.SaveAs(ruta);
book.Close();
excel.Quit();
}
private void cbBuaan_SelectedIndexChanged(object sender, EventArgs e)
{
if (cbBuaan.Text == "Trưa")
{
caan = " Trua";
}
else if (cbBuaan.Text == "Chiều")
{
caan = " Chieu";
}
else if (cbBuaan.Text == "Tối")
{
caan = " Toi";
}
else
{
caan = " Buaphu";
}
foreach (BuaAn ba in buaan)
{
if (ba.ten == cbBuaan.Text)
{
caanid = ba.id;
}
}
APICheckBaoCom = "http://192.84.100.207/MealOrdersAPI/api/DulieuBaoComs/" + dateTimePicker1.Value.ToString("MM-dd-yyyy") + "/" + caanid;
GetBaoCom();
bool Check = CheckData();
if (Check == true)
{
kiemtratrangthai();
GetDataClientChuaUpdateServer();
}
else
{
try
{
Task <string> callTask = Task.Run(() => GetAllCheckBaoCom());
callTask.Wait();
string astr = callTask.Result;
DataTable dt = (DataTable)JsonConvert.DeserializeObject(astr, typeof(DataTable));
if (dt.Rows.Count > 0)
{
string pathfile = Application.StartupPath + @"\CheckCom\" + dateTimePicker1.Value.ToString("MM-dd-yyyy") + caan + ".xls";
FileInfo filename = new FileInfo(pathfile);
Microsoft.Office.Interop.Excel.Application docExcel = new Microsoft.Office.Interop.Excel.Application {
Visible = false
};
Microsoft.Office.Interop.Excel.Workbook wb = docExcel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)docExcel.ActiveSheet;
ws.Cells[1, 1] = "id";
ws.Cells[1, 2] = "empid";
ws.Cells[1, 3] = "manhansu";
ws.Cells[1, 4] = "hoten";
ws.Cells[1, 5] = "phongid";
ws.Cells[1, 6] = "phong";
ws.Cells[1, 7] = "banid";
ws.Cells[1, 8] = "ban";
ws.Cells[1, 9] = "congdoanid";
ws.Cells[1, 10] = "congdoan";
ws.Cells[1, 11] = "khach";
ws.Cells[1, 12] = "ngay";
ws.Cells[1, 13] = "thang";
ws.Cells[1, 14] = "nam";
ws.Cells[1, 15] = "userid";
ws.Cells[1, 16] = "thoigiandat";
ws.Cells[1, 17] = "sudung";
ws.Cells[1, 18] = "dangky";
ws.Cells[1, 19] = "thoigiansudung";
ws.Cells[1, 20] = "soxuatandadung";
ws.Cells[1, 21] = "sotiendadung";
ws.Cells[1, 22] = "chot";
ws.Cells[1, 23] = "ghichu";
ws.Cells[1, 24] = "thucdontheobuaid";
ws.Cells[1, 25] = "thucdontheobua";
ws.Cells[1, 26] = "kieudoan";
ws.Cells[1, 27] = "buaanid";
ws.Cells[1, 28] = "buaan";
ws.Cells[1, 29] = "ca";
ws.Cells[1, 30] = "nhaanid";
ws.Cells[1, 31] = "nhaan";
ws.Cells[1, 32] = "loaidouong";
ws.Cells[1, 33] = "thanhtoan";
ws.Cells[1, 34] = "phongrieng";
ws.Cells[1, 35] = "dangkybosung";
ws.Cells[1, 36] = "trangthai1";
ws.Cells[1, 37] = "trangthai2";
var data = new object[dt.Rows.Count, dt.Columns.Count];
for (int row = 0; row < dt.Rows.Count; row++)
{
for (int column = 0; column <= dt.Columns.Count - 1; column++)
{
data[row, column] = dt.Rows[row][column].ToString();
}
}
var startCell = (Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1];
var endCell = (Microsoft.Office.Interop.Excel.Range)ws.Cells[dt.Rows.Count + 1, dt.Columns.Count];
var writeRange = ws.Range[startCell, endCell];
ws.Columns[3].NumberFormat = "@";
ws.Columns[19].NumberFormat = "@";
writeRange.Value2 = data;
wb.SaveAs(filename.FullName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges);
wb.Close();
docExcel.Application.Quit();
}
kiemtratrangthai();
}
catch (Exception)
{
MessageBox.Show("Chưa có dữ liệu!");
lvClient.Items.Clear();
lvDongbo.Items.Clear();
lbClient.Text = "Dữ liệu Client : 0";
lbChuadongbo.Text = "Dữ liệu chưa đồng bộ : 0";
}
}
}
private void button1_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excelApp = null;
try
{
// instantiating the excel application class
object misValue1 = System.Reflection.Missing.Value;
excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook currentWorkbook = excelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet currentWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)currentWorkbook.ActiveSheet;
currentWorksheet.Columns.ColumnWidth = 18;
if (dataGridView1.Rows.Count > 0)
{
currentWorksheet.Cells[1, 1] = DateTime.Now.ToLongDateString() + " " + DateTime.Now.ToShortTimeString();
int i = 1;
foreach (DataGridViewColumn dgviewColumn in dataGridView1.Columns)
{
// Excel work sheet indexing starts with 1
currentWorksheet.Cells[2, i] = dgviewColumn.Name;
++i;
}
Microsoft.Office.Interop.Excel.Range headerColumnRange = currentWorksheet.get_Range("A2", "AY2");
headerColumnRange.Font.Bold = true;
headerColumnRange.Font.Color = 0xFF0000;
int rowIndex = 0;
for (rowIndex = 0; rowIndex < dataGridView1.Rows.Count; rowIndex++)
{
DataGridViewRow dgRow = dataGridView1.Rows[rowIndex];
for (int cellIndex = 0; cellIndex < dgRow.Cells.Count; cellIndex++)
{
currentWorksheet.Cells[rowIndex + 3, cellIndex + 1] = "'" + dgRow.Cells[cellIndex].Value;
}
}
Microsoft.Office.Interop.Excel.Range fullTextRange = currentWorksheet.get_Range("A2", "AY" + (rowIndex + 1).ToString());
fullTextRange.WrapText = true;
fullTextRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
using (SaveFileDialog exportSaveFileDialog = new SaveFileDialog())
{
exportSaveFileDialog.Title = "Save as";
exportSaveFileDialog.Filter = "Microsoft Office Excel Workbook(*.xls)|*.xls";
if (DialogResult.OK == exportSaveFileDialog.ShowDialog())
{
string fullFileName = exportSaveFileDialog.FileName;
currentWorkbook.SaveAs(fullFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, System.Reflection.Missing.Value, misValue1, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, misValue1, misValue1, misValue1);
currentWorkbook.Saved = true;
MessageBox.Show("The export was successful", "Export to Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (excelApp != null)
{
excelApp.Quit();
excelApp = null;
}
}
//copyAlltoClipboard();
//UpdateFont();
//Excel.Application xlexcel;
//Excel.Workbook xlWorkBook;
//Excel.Worksheet xlWorkSheet;
//object misValue = System.Reflection.Missing.Value;
//xlexcel = new Excel.Application();
//xlexcel.Visible = true;
//xlWorkBook = xlexcel.Workbooks.Add(misValue);
//xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
//CR.Select();
//xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
}
public void append(string filePath, int index2, User user)
{
int index = 3;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = true;
xlWorkBook = xlApp.Workbooks.Open(filePath, 0,
false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"", true, false, 0, true, false, false);
xlBigSheet = xlWorkBook.Worksheets;
string x = "Sheet1";
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBigSheet.get_Item(1);
xlWorksheet.Cells[index, 1] = user.name;
xlWorksheet.Cells[index, 2] = user.tname;
xlWorksheet.Cells[index, 3] = user.login;
xlWorksheet.Cells[index, 4] = user.pass;
xlWorksheet.Cells[index, 5] = user.question;
xlWorksheet.Cells[index, 6] = user.answer;
xlWorksheet.Cells[index, 7] = user.date_reg;
xlWorksheet.Cells[index, 8] = user.prog_id;
xlWorksheet.Cells[index, 9] = user.prog_pass;
xlWorksheet.Cells[index, 10] = user.callback_url;
xlWorkBook.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
misValue, misValue, misValue,
misValue, misValue);
xlWorkBook.Close(misValue, misValue, misValue);
xlWorkBook = null;
xlApp.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
//Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
//if (xlApp == null)
//{
// throw new Exception("Excel is not properly installed!!");
//}
//xlApp = new Excel.Application();
//xlWorkbook = xlApp.Workbooks.Open(filePath);
//xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);
//xlRange = xlWorksheet.UsedRange;
//if (xlWorksheet.Cells[index, 3].Value2.ToString().Trim() != "")
// index++;
//xlWorksheet.Cells[index, 1] = user.name;
//xlWorksheet.Cells[index, 2] = user.tname;
//xlWorksheet.Cells[index, 3] = user.login;
//xlWorksheet.Cells[index, 4] = user.pass;
//xlWorksheet.Cells[index, 5] = user.question;
//xlWorksheet.Cells[index, 6] = user.answer;
//xlWorksheet.Cells[index, 7] = user.date_reg;
//xlWorksheet.Cells[index, 8] = user.prog_id;
//xlWorksheet.Cells[index, 9] = user.prog_pass;
//xlWorksheet.Cells[index, 10] = user.callback_url;
//xlWorkbook.Save();
//xlWorkbook.Close(true);
}
