/// <summary>
/// Закрыть документ
/// </summary>
/// <param name="save">C сохранением</param>
public void CloseDocument(bool save)
{
if (_xlApp.ActiveWorkbook != null)
{
_xlWorkBook.Close(save, _misValue, _misValue);
}
_xlApp.Quit();
}
private void button16_Click(object sender, EventArgs e)
{
try
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
if (dgfess.Visible == true)
{
for (i = 0; i <= dgfess.RowCount - 1; i++)
{
for (j = 0; j <= dgfess.ColumnCount - 1; j++)
{
DataGridViewCell cell = dgfess[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
xlWorkBook.SaveAs("StudentFees.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
MessageBox.Show("Excel file created , you can find the file 'StudentFees.xls' in My Document", " Meera Academy");
}
else if (dgstudent.Visible == true)
{
for (i = 0; i <= dgstudent.RowCount - 1; i++)
{
for (j = 0; j <= dgstudent.ColumnCount - 1; j++)
{
DataGridViewCell cell = dgstudent[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
xlWorkBook.SaveAs("studentDetail.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
MessageBox.Show("Excel file created , you can find the file 'StudentDetail.xls' in My Document", " Meera Academy");
}
// releaseObject(xlWorkSheet);
// releaseObject(xlWorkBook);
//releaseObject(xlApp);
}
catch (Exception a)
{
MessageBox.Show(a.Message.ToString(), "Error !!");
}
}
/// <summary>
/// 把Excel文件转换成PDF格式文件
/// </summary>
/// <param name="sourcePath">源文件路径</param>
/// <param name="targetPath">目标文件路径</param>
/// <returns>true=转换成功</returns>
public bool XLSConvertToPDF(string sourcePath, string targetPath)
{
bool result = false;
Excel.XlFixedFormatType targetType = Excel.XlFixedFormatType.xlTypePDF;
object missing = Type.Missing;
Excel.ApplicationClass application = null;
Excel.Workbook workBook = null;
try
{
application = new Excel.ApplicationClass();
object target = targetPath;
object type = targetType;
workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
workBook.ExportAsFixedFormat(targetType, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
catch
{
result = false;
}
finally
{
if (workBook != null)
{
workBook.Close(true, missing, missing);
workBook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
if (workBook != null)
{
workBook.Close();
}
if (application != null)
{
application.Quit();
}
return(result);
}
public void CreateFiles(bool state)
{
String[] sheets = GetExcelSheetNames(this.BookName);
String str;
Directory.CreateDirectory(this.IndexDirectoryPath + "/" + BookName);
String divfilePath = this.IndexDirectoryPath + "/" + this.BookName + "/" + this.BookName + ".index";
CreateDivFileSheets(divfilePath);
Excel.Application app = new Excel.ApplicationClass();
app.Visible = true;
app.DisplayAlerts = false;
Excel.Workbook xlWorkBook;
if (state)
{
xlWorkBook = app.Workbooks.Open("D:\\ptw\\prefaceNPS.xlsx", 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
}
else
{
xlWorkBook = app.Workbooks.Open("D:\\ptw\\prefaceNP.xlsx", 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
}
for (int i = 0; i < sheets.Length; ++i)
{
str = sheets[i].Replace("#", ".");
if (str.Remove(str.Length - 1, 1) == "Historique" || str.Remove(str.Length - 1, 1) == "Comptes annuels")
{
createindexFiles(str.Remove(str.Length - 1, 1), xlWorkBook, state);
}
}
xlWorkBook.Close();
app.Quit();
}
/// <summary>
/// 写Excel
/// </summary>
public void WriteExcel()
{
string currentDir = System.Environment.CurrentDirectory;
string fileName = $"{currentDir}\\TestCaseTemplate.xlsx";
Excel.Application excelApp = new Excel.ApplicationClass();
Excel.Workbook workbook;
if (System.IO.File.Exists(fileName))
{
workbook = excelApp.Workbooks.Open(fileName, 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
}
else
{
workbook = excelApp.Workbooks.Add(true);
}
this.WriteInWorkSheet(workbook);
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
string saveDir = fileName.Replace("TestCaseTemplate.xlsx", $"TestCase_{DateTime.Now:yyyyMMddHHmmss}.xlsx");
OutputDisplay.ShowMessage($"文件保存路勁：{saveDir}\n", Color.Azure);
workbook.SaveAs(saveDir);
workbook.Close(false, Missing.Value, Missing.Value);
excelApp.Quit();
workbook = null;
excelApp = null;
}
public void Should_read_value_repeatedly()
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
//xlApp.Visible = true;
//xlApp.UserControl = true;
string excelPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ratesource.xls");
xlWorkBook = xlApp.Workbooks.Open(excelPath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, true, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//xlWorkSheet.Change += new Excel.DocEvents_ChangeEventHandler(xlWorkSheet_Change);
for (int i = 0; i < 100; i++)
{
Console.WriteLine(xlWorkSheet.get_Range("B23", "B23").Value2.ToString());
//xlWorkSheet.
Thread.Sleep(1000);
}
xlWorkBook.Close(false, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
private bool ExcelToPDF(string sourcePath, string targetPath)
{
bool result;
object missing = Type.Missing;
Excel.ApplicationClass application = null;
Excel.Workbook workBook = null;
try
{
application = new Excel.ApplicationClass();
object target = targetPath;
object type = Excel.XlFixedFormatType.xlTypePDF;
workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
workBook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
catch
{
result = false;
}
finally
{
workBook.Close(true, missing, missing);
application.Quit();
}
return(result);
}
// Read from the Excel workbook.
private void btnRead_Click(object sender, EventArgs e)
{
// Get the Excel application object.
Excel.Application excel_app = new Excel.ApplicationClass();
// Make Excel visible (optional).
excel_app.Visible = true;
// Open the workbook read-only.
Excel.Workbook workbook = excel_app.Workbooks.Open(txtFile.Text,
Type.Missing, true, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
// Get the first worksheet.
Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets[1];
// Get the titles and values.
SetTitleAndListValues(sheet, 1, 1, lblTitle1, lstItems1);
SetTitleAndListValues(sheet, 1, 2, lblTitle2, lstItems2);
// Close the workbook without saving changes.
workbook.Close(false, Type.Missing, Type.Missing);
// Close the Excel server.
excel_app.Quit();
}
/// <summary>
/// 释放COM组件所占用的资源
/// </summary>
public void ReleaseRescorce()
{
try
{
if (work != null || app != null)
{
work.Close(null, null, null);
app.Workbooks.Close();
app.Quit();
if (dataSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(dataSheet);
}
if (work != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(work);
}
if (app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
}
app = null;
work = null;
dataSheet = null;
GC.Collect();
}
}
catch
{
}
}
public static void CreateExcel(DataSet ds, string excelPath)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
try
{
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
xlWorkSheet.Cells[i + 1, j + 1] = ds.Tables[0].Rows[i].ItemArray[j].ToString();
}
}
xlWorkBook.SaveAs(excelPath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlApp);
releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取获得当前你选择的Excel Sheet的所有名字
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static string[] GetExcelSheetNames(string filePath)
{
Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Workbooks wbs = excelApp.Workbooks;
Workbook wb = wbs.Open(filePath, 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);
int count = wb.Worksheets.Count;
string[] names = new string[count];
for (int i = 1; i <= count; i++)
{
names[i - 1] = ((Worksheet)wb.Worksheets[i]).Name;
}
wb.Close(null, null, null);
excelApp.Quit();
wbs.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
excelApp = null;
wbs = null;
wb = null;
return names;
}
private void SaveFile_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string str;
int rCnt = 0;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(this.fileName, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
//xlApp.Visible = true;
for (rCnt = 2; rCnt <= Program.tweets.Count + 1; rCnt++)
{
Tweet t = (Tweet)Program.tweets[rCnt - 2];
(range.Cells[rCnt, 1] as Excel.Range).Value2 = t.ID;
(range.Cells[rCnt, 2] as Excel.Range).Value2 = t.text;
(range.Cells[rCnt, 3] as Excel.Range).Value2 = t.relevanceLabel;
(range.Cells[rCnt, 4] as Excel.Range).Value2 = t.sentimentLabel;
}
xlWorkBook.Save();
xlWorkBook.Close(true, null, null);
xlApp.Quit();
}
public void Should_detect_row_and_value_changed()
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
string excelPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ratesource.xls");
xlWorkBook = xlApp.Workbooks.Open(excelPath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, true, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//xlWorkSheet.Activate();
xlWorkSheet.Change += new Excel.DocEvents_ChangeEventHandler(xlWorkSheet_Change);
lock (this)
{
Monitor.Wait(this);
}
xlWorkBook.Close(false, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
Int16 i, j;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (i = 0; i <= grdTransakcii.RowCount - 2; i++)
{
for (j = 0; j <= grdTransakcii.ColumnCount - 1; j++)
{
xlWorkSheet.Cells[i + 1, j + 1] = grdTransakcii[j, i].Value.ToString();
}
}
xlWorkBook.SaveAs(@"E:\Fakturii.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Направен е export", "Export", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string str;
int rCnt = 0;
int cCnt = 0;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open("csharp.net-informations.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
{
str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
MessageBox.Show(str);
}
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
public static void CloseExcelApplicationClass(Excel.ApplicationClass _ExcelApplication)
{
try
{
if (_ExcelApplication != null)
{
_ExcelApplication.Workbooks.Close();
_ExcelApplication.Quit();
}
}
finally { }
try
{
//Close ComObject:
if (_ExcelApplication.ActiveWorkbook != null)
{
Marshal.ReleaseComObject(_ExcelApplication.ActiveWorkbook);
}
}
finally { }
try
{
if (_ExcelApplication != null)
{
Marshal.ReleaseComObject(_ExcelApplication);
}
}
finally { }
}
public static void ExportToExcel(this DataTable dt, String fileName)
{
var xlApp = new Excel.ApplicationClass();
var xlWorkBook = xlApp.Workbooks.Open(
fileName,
0,
false, // for read/write
5, "", "", true, Excel.XlPlatform.xlWindows,
"\t", false, false, 0, true, 1, 0
);
var xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets[1];
var NumColumns = dt.Columns.Count;
var rowCount = dt.Rows.Count;
try {
foreach (DataRow dr in dt.Rows)
{
for (int i = 1; i < NumColumns + 1; i++)
{
xlWorkSheet.Cells[rowCount, i] = dr[i - 1].ToString();
}
rowCount += 1;
}
xlWorkBook.Save();
}
catch (Exception) {
throw;
}
finally {
xlApp.Quit();
}
}
// Read from the Excel workbook.
private void btnRead_Click(object sender, EventArgs e)
{
// Get the Excel application object.
Excel.Application excel_app = new Excel.ApplicationClass();
// Make Excel visible (optional).
excel_app.Visible = true;
// Open the workbook read-only.
Excel.Workbook workbook = excel_app.Workbooks.Open(txtFile.Text,
Type.Missing, true, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
// Get the first worksheet.
Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets[1];
// Get the titles and values.
SetTitleAndListValues(sheet, 1, 1, lblTitle1, lstItems1);
SetTitleAndListValues(sheet, 1, 2, lblTitle2, lstItems2);
// Close the workbook without saving changes.
workbook.Close(false, Type.Missing, Type.Missing);
// Close the Excel server.
excel_app.Quit();
}
private void button2_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
for (i = 0; i <= dataGridView1.RowCount - 1; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
xlWorkBook.SaveAs("csharp.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");
}
private void button2_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
Int16 i, j;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (i = 0; i <= dataGridView1.RowCount - 2; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
xlWorkSheet.Cells[i + 1, j + 1] = dataGridView1[j, i].Value.ToString();
}
}
xlWorkBook.SaveAs(@"c:\csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
private void button3_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
Int16 i, j;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (i = 0; i <= dataGridView1.RowCount - 2; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
xlWorkSheet.Cells[i + 1, j + 1] = dataGridView1[j, i].Value.ToString();
}
}
xlWorkBook.SaveAs(@"C:\Users\Soroush\Desktop\HospitalDatabaseOutputDrug.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
//
//// Supprimer typologie dans "D:\\ptw\\Histo.ptw"
//
private void renommer()
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
object misValue = System.Reflection.Missing.Value;
//missing values, for example, when you invoke methods that have default parameter values.
//remplace les paramètres par default des fonctions utilisés
xlApp = new Excel.ApplicationClass();
xlApp.Visible = true;
xlWorkBook = xlApp.Workbooks.Open(fichierprepare, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, true, false);
//Afficher pas les Alerts !!non utiliser avant assurer!!!
xlApp.DisplayAlerts = false;
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item("Historique");
//Excel.Worksheet sheetTypologie = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item("Typologie IFRS");
//sheetTypologie.Delete();
xlWorkSheet.SaveAs(prefaceNP, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet); //kill WorkSheet
releaseObject(xlWorkBook); //Kill WorkBook
releaseObject(xlApp); //Kill Application Application
}
/// <summary>
/// this functio create a excel file with the values from a table
/// </summary>
public static string Export(string path, System.Data.DataTable dt)
{
string output = "OK";
try
{
//create a new instance of excel file
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
//first let's extract the columns and create a little header
Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[65535, 6]).EntireColumn.NumberFormat = "@";
//worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[65535, 6]).EntireColumn.NumberFormat = "#";
worksheet.Activate();
int horizontal = 1;
int vertical = 1;
foreach (DataColumn dc in dt.Columns)
{
excel.Cells[vertical, horizontal] = dc.ColumnName;
horizontal++;
}
//go to next row
vertical++;
foreach (DataRow row in dt.Rows)
{
horizontal = 1;
foreach (DataColumn column in dt.Columns)
{
excel.Cells[vertical, horizontal] = row[column];
horizontal++;
}
vertical++;
}
//save the excel file
worksheet.Name = "PROROGHE Non Lavorate";
//DirectoryInfo dr = new DirectoryInfo(path);
//dr.Create();
FileInfo fi = new FileInfo(path);
if (fi.Exists == true)
fi.Delete();
worksheet.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excel.Workbooks.Close();
excel.Quit();
}
catch (Exception ex)
{
output = ex.Message + "\n" + ex.StackTrace;
}
return output;
}
static void Main(string[] args)
{
object path;
MSExcel.Application excelApp; // Excel 应用程序变量
MSExcel.Workbook excelDoc; // Excel 文档变量
path = @"F:\xianfeng\MyExcel.xlsx";
excelApp = new MSExcel.ApplicationClass();
if (File.Exists((string)path))
{
File.Delete((string)path);
}
//由于使用的是COM库，因此有许多变量需要用Nothing 代替
Object Nothing = Missing.Value;
excelDoc = excelApp.Workbooks.Add(Nothing);
//WdSaveFormat 为Excel 文档的保存格式
Object format = MSExcel.XlFileFormat.xlWorkbookDefault;
//将excelDoc 文档对象的内容保存为xlsx文档
excelDoc.SaveAs(path, Nothing, Nothing, Nothing, Nothing, Nothing, MSExcel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing);
//关闭excelDoc 文档对象
excelDoc.Close(Nothing, Nothing, Nothing);
//关闭excelApp 组件对象
excelApp.Quit();
Console.WriteLine(path + " 创建完毕");
}
private void supprimercol_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlApp.Visible = true;
xlApp.DisplayAlerts = false;
xlWorkBook = xlApp.Workbooks.Open(prefaceNP, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, true, false);
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item("Historique-s");
Excel.Range range = xlWorkSheet.UsedRange;
object[,] values = (object[, ])range.Value2;
int time1 = System.Environment.TickCount;
////////////////////////////////////////400000//////////////////////
int rCnt = 0;
int cCnt = 0;
int row400000 = 0;
cCnt = range.Columns.Count;
for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
string valuecellabs = Convert.ToString(values[rCnt, cCnt]);
if (Regex.Equals(valuecellabs, "400000"))
{
row400000 = rCnt;
break;
}
}
for (int col = 1; col <= xlWorkSheet.UsedRange.Columns.Count; col++)
{
string value = Convert.ToString(values[row400000, col]);
if (Regex.Equals(value, "-1"))
{
Excel.Range rangeDelx = xlWorkSheet.Cells[row400000, col] as Excel.Range;
rangeDelx.EntireColumn.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
range = xlWorkSheet.UsedRange;
values = (object[, ])range.Value2;
col--;
}
}
xlWorkSheet.SaveAs(prefaceNP, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
xlApp.DisplayAlerts = true;
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
int time2 = System.Environment.TickCount;
int times = time2 - time1;
string tim = Convert.ToString(Convert.ToDecimal(times) / 1000);
//MessageBox.Show("jobs done " + tim + " seconds used");
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
/// <summary>
/// 获取Sheet1表的名称。
/// </summary>
public string getSheetNameOfSheet1()
{
if (!File.Exists(_path))
{
MessageBox.Show("此Excel不存在！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
return("");
}
App = new Microsoft.Office.Interop.Excel.ApplicationClass();
App.Visible = false;
try
{
WBook = App.Workbooks.Open(this._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);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
if (WBook != null)
{
WBook.Close(true, _path, Type.Missing);
}
//退出excel
App.Quit();
//释放资源
if (WBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(WBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(App);
//调用GC的垃圾回收方法
GC.Collect();
GC.WaitForPendingFinalizers();
return("");
}
string name = ((Worksheet)WBook.Sheets[1]).Name;
WBook.Close(true, _path, Type.Missing);
//退出excel
App.Quit();
//释放资源
System.Runtime.InteropServices.Marshal.ReleaseComObject(WBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(App);
//调用GC的垃圾回收方法
GC.Collect();
GC.WaitForPendingFinalizers();
return(name);
}
void button1_Click(object sender, EventArgs e)
{
SaveFileDialog openDlg = new SaveFileDialog();
openDlg.Filter = "Execl files (*.xls)|*.xls";
string path = openDlg.FileName;
if (openDlg.ShowDialog() == DialogResult.OK)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Int16 i, j;
xlApp = new Excel.ApplicationClass();
xlApp.DisplayAlerts = false;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (i = 0; i <= dataGridView1.RowCount - 2; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
xlWorkSheet.Cells[i + 1, j + 1] = dataGridView1[j, i].Value.ToString();
}
}
try
{
xlWorkBook.SaveAs(path.ToString(), Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
}
catch (Exception ex)
{
MessageBox.Show("Сталася помилка під час збереження " + ex.ToString());
}
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
public void WireInfomationCIM(string path, int index, string name, string sdt, string Address)
{
try
{
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Range chartRange;
xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(path, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//xlWorkSheet.Cells[1, 1] = "http://csharp.net-informations.com";
//xlWorkSheet.Rows.Insert(0, 0);
//xlWorkSheet.Rows.Insert(0, 0);
//xlWorkSheet.Rows.Insert(0, 0);
//thong tin KH
//Ten KH
xlWorkSheet.get_Range("A" + index + 1, "H" + index + 1).Merge(false);
chartRange = xlWorkSheet.get_Range("A" + index + 1, "H" + index + 1);
chartRange.FormulaR1C1 = name;
//So DT
xlWorkSheet.get_Range("A" + index + 2, "H" + index + 2).Merge(false);
chartRange = xlWorkSheet.get_Range("A" + index + 2, "H" + index + 2);
chartRange.FormulaR1C1 = sdt;
//Dia Chi
xlWorkSheet.get_Range("A" + index + 3, "H" + index + 3).Merge(false);
chartRange = xlWorkSheet.get_Range("A" + index + 3, "H" + index + 3);
chartRange.FormulaR1C1 = Address;
chartRange.Font.Bold = true;
chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
chartRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
chartRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
//xlWorkSheet.get_Range("A" + index, "H" + index+2).Formula = value;
xlWorkBook.Save();
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
catch (Exception exception)
{
CTLError.WriteError("CTLImportExcel WireInfomationCIM", exception.Message);
return;
}
}
public static void SavePierToDB(string fileName, List <CRailwayProject> bridgeList)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
app.Visible = false;
//Workbook wBook = app.Workbooks.Add(true);
Workbook wBook = app.Workbooks.Open(fileName);
Worksheet wSheet = wBook.Worksheets["Pier"] as Worksheet;
wSheet.Cells[1, 1] = "BridgeName";
wSheet.Cells[1, 2] = "PierName";
wSheet.Cells[1, 3] = "Longitude";
wSheet.Cells[1, 4] = "Latitude";
wSheet.Cells[1, 5] = "Altitude";
wSheet.Cells[1, 6] = "YawOffset";
wSheet.Cells[1, 7] = "DKCode";
wSheet.Cells[1, 8] = "Meter";
int count = 1;
foreach (CRailwayProject p in bridgeList)
{
foreach (CRailwayDWProj dwp in p.mDWProjList)
{
wSheet.Cells[1 + count, 1] = p.ProjectName;
wSheet.Cells[1 + count, 2] = dwp.DWName;
wSheet.Cells[1 + count, 3] = dwp.mLongitude_Mid;
wSheet.Cells[1 + count, 4] = dwp.mLatitude_Mid;
wSheet.Cells[1 + count, 5] = dwp.mAltitude_Mid;
wSheet.Cells[1 + count, 6] = dwp.mHeading_Mid;
wSheet.Cells[1 + count, 7] = dwp.DKCode_Start;
wSheet.Cells[1 + count, 8] = dwp.Mileage_Start;
count++;
}
}
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//保存工作簿
//wBook.SaveAs(@"D:\result.xlsx");
wBook.Save();
//_Excel.Workbook book = app.Workbooks.Open(savePath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
//_Excel.Worksheet sheet = (_Excel.Worksheet)book.ActiveSheet;
//保存excel文件
//app.Save(@"D:\test1.xlsx");
//app.SaveWorkspace(@"D:\test1.xlsx");
app.Quit();
app = null;
return;
}
catch (Exception err)
{
Console.WriteLine("导出Excel出错！错误原因：" + err.Message);
return;
}
}
private static void saveCacuResultAsExcel(string path, double[] bavg, double[] bvar, double[] oavg, double[] ovar, double[] oBavg, double[] oBvar)
{
MSExcel.Application excelApp; //Excel 应用程序变量
MSExcel.Workbook excelDoc; //Excel文档变量
excelApp = new MSExcel.ApplicationClass(); //初始化
if (File.Exists(path))
{
File.Delete(path);
}
//由于使用的是COM库，因此许多变量需要用Nothing代替
Object misspara = Missing.Value;
excelDoc = excelApp.Workbooks.Add(misspara);
//使用第一个工作表作为插入数据的工作表
MSExcel.Worksheet ws = (MSExcel.Worksheet)excelDoc.Sheets[1];
//声明一个MSExcel.Range类型的变量r
MSExcel.Range r1, r2, r3, r4, r5, r6, r7;
r1 = ws.get_Range("A2", "A2"); //获取A2处的表格，并赋值
r2 = ws.get_Range("B2", "B2"); //获取A2处的表格，并赋值
r3 = ws.get_Range("C2", "C2"); //获取A2处的表格，并赋值
r4 = ws.get_Range("D2", "D2"); //获取A2处的表格，并赋值
r5 = ws.get_Range("E2", "E2"); //获取A2处的表格，并赋值
r6 = ws.get_Range("F2", "F2"); //获取A2处的表格，并赋值
r7 = ws.get_Range("G2", "G2"); //获取A2处的表格，并赋值
r1.Value2 = "行号";
r2.Value2 = "背景均值";
r3.Value2 = "背景方差";
r4.Value2 = "地物均值";
r5.Value2 = "地物方差";
r6.Value2 = "混合均值";
r7.Value2 = "混合方差 ";
Random r = new Random();
for (int i = 3; i < bavg.Length + 3; i++) //行
{
ws.Cells[i, 1] = String.Format("{0,12:F3}", bavg[i - 3]);
// ws.Cells[i,1] = Convert.ToString(i);
}
//WdSaveFormat为Excel文档的保存格式
object format = MSExcel.XlFileFormat.xlWorkbookDefault;
//将excelDoc文档对象的内容保存为XLSX
excelDoc.SaveAs(path, format, misspara, misspara, misspara, misspara,
MSExcel.XlSaveAsAccessMode.xlExclusive, misspara, misspara, misspara, misspara, misspara);
//关闭excelDoc文档对象
excelDoc.Close(misspara, misspara, misspara);
//关闭excelApp组件对象
excelApp.Quit();
Console.WriteLine(path + "创建完毕");
}
private void ReadRecipeFile(string strFileName)
{
object missing = System.Reflection.Missing.Value;
Excel.Application excel = new Excel.ApplicationClass(); //lauch excel application
if (excel == null)
{
this.label1.Text = "Can't access excel";
}
else
{
excel.Visible = false; excel.UserControl = true;
// 以只读的形式打开EXCEL文件
Excel.Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
missing, missing, missing, true, missing, missing, missing, missing, missing);
//取得第一个工作薄
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.get_Item(1);
//取得总记录行数 (包括标题列)
int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数
int columnsint = ws.UsedRange.Cells.Columns.Count; //得到列数
//取得数据范围区域 (不包括标题列)
Excel.Range rng1 = ws.Cells.get_Range("A2", "A" + rowsint);
Excel.Range rng2 = ws.Cells.get_Range("B2", "B" + rowsint);
Excel.Range rng3 = ws.Cells.get_Range("C2", "C" + rowsint);
object[,] arry1 = (object[, ])rng1.Value2; //get range's value
object[,] arry2 = (object[, ])rng2.Value2;
object[,] arry3 = (object[, ])rng3.Value2; //get range's value
//将新值赋给一个数组
string[,] arry = new string[rowsint - 1, columnsint];
if (mRecipeList == null)
{
mRecipeList = new List <Recipe>();
}
mRecipeList.Add(new Recipe());
int currentIndex = mRecipeList.Count - 1;
mRecipeList[currentIndex].num = (double)ws.Cells.get_Range("B1", "B1").Value2;
//for (int i = 1; i <= rowsint - 1; i++)
for (int i = 1; i <= rowsint - 1; i++)
{
mRecipeList[currentIndex].mFoodList.Add(new Food());
mRecipeList[currentIndex].mFoodList[i - 1].name = arry1[i, 1].ToString();
mRecipeList[currentIndex].mFoodList[i - 1].weight = (double)arry2[i, 1];
//mReferenceList[i - 1].element2 = (double)arry3[i, 1];
}
mRecipeList[currentIndex].calcElement(mReferenceList);
}
excel.Quit(); excel = null;
Process[] procs = Process.GetProcessesByName("excel");
foreach (Process pro in procs)
{
pro.Kill(); //没有更好的方法,只有杀掉进程
}
GC.Collect();
}
static void Main(string[] args)
{
Microsoft.Office.Interop.Excel.Workbook wb;
Microsoft.Office.Interop.Excel.ApplicationClass xl;
xl = new Microsoft.Office.Interop.Excel.ApplicationClass();
xl.Visible = true;
//Microsoft.Office.Core.MsoAutomationSecurity secAuto = (MsoAutomationSecurity)MsoAutomationSecurity.msoAutomationSecurityLow;
//xl.Application.AutomationSecurity = secAuto;
wb = xl.Workbooks.Open(
@"e:\\ccidev\testcall2.xlsm"
, 0
, true,
5,
"",
"",
true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
xl.Visible = true;
xl.Interactive = true;
object[] mytest = new object[] { 5, 0 };
object mytest2 = new object();
mytest2 = mytest;
object[] oParms = new object[] { "Sheet1.Test"
, ""
, ""
, mytest
, ""
, ""
, null
, ""
, 0
, "1"
, new object[] { "test", 0 }
, "" };
try
{
RunMacro(xl, oParms);
}
catch (Exception ex) { Console.WriteLine(ex.Message); };
xl.Quit();
}
public cXLWorker(string sFileName)
{
try
{
xl = new Microsoft.Office.Interop.Excel.ApplicationClass();
xl.Visible = true;
wb = xl.Workbooks.Open(
// "e:\\ccidev\\testCall1.xlsm",
sFileName, 0,
true,
5,
"",
"",
true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
RunMacro(this.xl, new Object[] { sFileName, true });
//true,
// "5",
// "",
// "",
// "true",
// "Microsoft.Office.Interop.Excel.XlPlatform.xlWindows",
// "\t",
// "false",
// "false",
// "0",
// "true",
// "1",
// "0"});
// xl.Run("Sheet1.Test",Type.Missing)
//xl.Run("Sheet1.Test","Stest",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,
// 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,Type.Missing);
}
catch (Exception ex)
{
xl.Quit();
throw ex;
}
finally
{ }
}
public List<ResultRowCell> SearchByFile(string[] listOfWords, SupplierType supplierType, string priceFileName)
{
Application xlApp = new Excel.ApplicationClass();
xlApp.ReferenceStyle = XlReferenceStyle.xlA1;
Workbook xlWorkBook = xlApp.Workbooks.Open(priceFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
var xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(supplierType.SheetNumber);
int lastUsedRow = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
//начало поиска
var searchResult = new List<ResultRowCell>();
for (int currentRow = supplierType.ListCell[0].Row; currentRow <= lastUsedRow; currentRow++)
{
var resultRowCell = new ResultRowCell();
resultRowCell.Clear();
for (int iCell = 0; iCell < supplierType.ListCell.Count; iCell++)
{
//для всех ячеек получаем их значения
int currentColumn = supplierType.ListCell[iCell].Col;
//проверка ячейки на существование.
var value = ((Range) xlWorkSheet.Cells[currentRow, currentColumn]).Value;
string result = "";
if (value != null)
result = value.ToString();
else
result = "";
resultRowCell.VSupplierName = supplierType.Name;
resultRowCell.Add(new ResultCell(currentRow, currentColumn, result,
supplierType.ListCell[iCell].Name));
}
//ищем по всем словам, по всем ячейкам
int qty = 0;
if ((qty = ParseRow(resultRowCell, listOfWords)) > 0)
{
//MessageBox.Show(@"Current row: " + currentRow.ToString());
resultRowCell.qty = qty;
searchResult.Add(resultRowCell);
}
}
//конец поиска
xlWorkBook.Close(false, null, null);
xlApp.Quit();
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
return searchResult;
}
private void brnPrint_Click(object sender, EventArgs e)
{
object path;
path = Application.StartupPath + @"\Pram\OperationOfDay.dd";
MSExcel.Application excelApp; //Excel应用程序变量
MSExcel.Workbook excelDoc; //Excel文档变量
excelApp = new MSExcel.ApplicationClass(); //初始化
//由于使用的是COM库，因此有许多变量需要用Nothing代替
Object Nothing = Missing.Value;
excelDoc = excelApp.Workbooks._Open(Application.StartupPath + @"\Pram\OperationOfDay.dd", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//使用第一个工作表作为插入数据的工作表
MSExcel.Worksheet ws = (MSExcel.Worksheet)excelDoc.Sheets[1];
//声明一个MSExcel.Range 类型的变量r
MSExcel.Range r;
r = ws.get_Range(("C" + 2), ("C" + 2)); r.Value2 = this.tbxOperationDate.Text;
r = ws.get_Range(("C" + 4), ("C" + 4)); r.Value2 = this.lblTotalAmount.Text;
r = ws.get_Range(("C" + 6), ("C" + 6)); r.Value2 = this.lblNewPawnAmount.Text;
r = ws.get_Range(("F" + 6), ("F" + 6)); r.Value2 = this.lblRenewPawnAmount.Text;
r = ws.get_Range(("I" + 6), ("I" + 6)); r.Value2 = this.lblRedeemPawnAmount.Text;
r = ws.get_Range(("C" + 7), ("C" + 7)); r.Value2 = this.lblClosePawnAmount.Text;
r = ws.get_Range(("F" + 7), ("F" + 7)); r.Value2 = this.lblClearPawnAmount.Text;
r = ws.get_Range(("I" + 7), ("I" + 7)); r.Value2 = this.lblMoneyRL.Text;
object format = MSExcel.XlFileFormat.xlWorkbookNormal;
//打印文档
excelDoc.PrintOut(Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
//将excelDoc文档对象的内容保存为dd文档
//excelDoc.Save();
if (File.Exists(Application.StartupPath + @"\Pram\dump.dd"))
{
try
{
File.Delete(Application.StartupPath + @"\Pram\dump.dd");
}
catch
{
MessageBox.Show("记录文件正在被使用，请退出Excel\n" + e.ToString());
}
}
excelDoc.SaveAs(Application.StartupPath + @"\Pram\dump.dd", format, Nothing, Nothing, Nothing, Nothing, MSExcel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing);
//关闭excelDoc文档对象
excelDoc.Close(Nothing, Nothing, Nothing);
//关闭excelApp组件对象
excelApp.Quit();
//MessageBox.Show("生成成功");
}
private void btnExportToExcel_Click(object sender, EventArgs e)
{
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
dialog.OverwritePrompt = true;
dialog.RestoreDirectory = true;
if (dialog.ShowDialog() != DialogResult.OK)
{
return;
}
Excel.Application app = new Excel.ApplicationClass();
try
{
Object missing = System.Reflection.Missing.Value;
app.Visible = false;
Excel.Workbook wBook = app.Workbooks.Add(missing);
Excel.Worksheet wSheet = wBook.Worksheets[1] as Excel.Worksheet;
Excel.Range DataCell = wSheet.get_Range("A1", "A1");
DataCell.Value2 = "参数名称";
DataCell.Next.Value2 = "参数单位";
DataCell.Next.Next.Value2 = "参数数值";
for (int i = 0; i < curWaParas.Count; ++i)
{
WeightParameter wp = curWaParas[i];
string cellid = "A" + (i + 2).ToString();
DataCell = wSheet.get_Range(cellid, cellid);
DataCell.Value2 = wp.ParaName;
DataCell.Next.Value2 = wp.ParaUnit;
DataCell.Next.Next.Value2 = wp.ParaValue;
}
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//保存工作簿
wBook.SaveAs(dialog.FileName, Excel.XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
wBook.Close(false, missing, missing);
app.Quit();
app = null;
XLog.Write("成功导出参数值到文件\"" + dialog.FileName + "\"！");
}
catch (Exception err)
{
MessageBox.Show("导出Excel出错！错误原因：" + err.Message, "提示信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public List <string[]> parseExcel(string path)
{
List <string[]> parsedData = new List <string[]>();
try
{
// string[] row =new string[5];
string singlerow = string.Empty;
//Excel.FillFormat xlfile = new Excel.FillFormat();
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string str;
int rCnt = 0;
int cCnt = 0;
xlApp = new Excel.ApplicationClass();
// xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
//string[] row = new string[9];
string[] row = new string[range.Columns.Count];
for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
{
try
{
str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2.ToString();
row[cCnt - 1] = str;
}
catch { }
}
parsedData.Add(row);
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
return(parsedData);
}
catch
{
return(parsedData);
}
}
public bool SaveDataTableToExcel(string tableName)
{
Microsoft.Office.Interop.Excel.Application app =
new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
app.Visible = false;
Workbook wBook = app.Workbooks.Open(@"D:\GISData\xlsx\ProjectInfo.xlsx");
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (ds != null)
{
System.Data.DataTable excelTable = ds.Tables[tableName];
if (excelTable.Rows.Count > 0)
{
int row = 0;
row = excelTable.Rows.Count;
int col = excelTable.Columns.Count;
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
string str = excelTable.Rows[i][j].ToString();
wSheet.Cells[i + 2, j + 1] = str;
}
}
}
int size = excelTable.Columns.Count;
for (int i = 0; i < size; i++)
{
wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;
}
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//保存工作簿
wBook.Save();
//保存excel文件
//app.Save(filePath);
//app.SaveWorkspace(filePath);
app.Quit();
}
app = null;
return(true);
}
catch (Exception err)
{
MessageBox.Show("导出Excel出错！错误原因：" + err.Message, "提示信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
finally
{
}
}
public void draw_charts()
{
Excel.Application xlApp;
Excel.Workbook xlWorkbook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkbook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "";
xlWorkSheet.Cells[1, 2] = "Student1";
xlWorkSheet.Cells[1, 3] = "Student2";
xlWorkSheet.Cells[1, 4] = "Student3";
xlWorkSheet.Cells[2, 1] = "Term1";
xlWorkSheet.Cells[2, 2] = "80";
xlWorkSheet.Cells[2, 3] = "65";
xlWorkSheet.Cells[2, 4] = "45";
xlWorkSheet.Cells[3, 1] = "Term2";
xlWorkSheet.Cells[3, 2] = "78";
xlWorkSheet.Cells[3, 3] = "72";
xlWorkSheet.Cells[3, 4] = "60";
xlWorkSheet.Cells[4, 1] = "Term3";
xlWorkSheet.Cells[4, 2] = "82";
xlWorkSheet.Cells[4, 3] = "80";
xlWorkSheet.Cells[4, 4] = "65";
xlWorkSheet.Cells[5, 1] = "Term4";
xlWorkSheet.Cells[5, 2] = "75";
xlWorkSheet.Cells[5, 3] = "82";
xlWorkSheet.Cells[5, 4] = "68";
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "d5");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
xlWorkbook.SaveAs("testgraph.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkbook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkbook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");
}
public static bool writeExcel(System.Data.DataTable excelTable, string filePath)
{
filePath = Command.Process.root + filePath;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
app.Visible = false;
Workbook wBook = app.Workbooks.Add();
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (excelTable.Rows.Count > 0)
{
int row = 0;
row = excelTable.Rows.Count;
int col = excelTable.Columns.Count;
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
string str = excelTable.Rows[i][j].ToString();
wSheet.Cells[i + 2, j + 1] = str;
}
}
}
int size = excelTable.Columns.Count;
for (int i = 0; i < size; i++)
{
wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;
}
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//保存工作簿
wSheet.SaveAs(filePath);
app.Quit();
//清理内存
System.Runtime.InteropServices.Marshal.ReleaseComObject(wSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook);
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(app) > 0) ;
wSheet = null;
wBook = null;
app = null;
GC.Collect();
return true;
}
catch (Exception err)
{
Console.WriteLine(err.Message);
return false;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
String sFileImage = "C:\\ImgAppImages\\SAM_5022.jpg";
String sFilePath = "C:\\ImgAppImages\\sam.xls";
if (File.Exists(sFilePath)) { File.Delete(sFilePath); }
Microsoft.Office.Interop.Excel.ApplicationClass objApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Worksheet objSheet = new Microsoft.Office.Interop.Excel.Worksheet();
Microsoft.Office.Interop.Excel.Workbook objWorkBook = null;
//object missing = System.Reflection.Missing.Value;
try
{
objWorkBook = objApp.Workbooks.Add(Type.Missing);
objSheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkBook.ActiveSheet;
//Add picture to single sheet1
objSheet = (Worksheet)objWorkBook.Sheets[1];
objSheet.Name = "Graph with Report";
//////////////
//Or multiple sheets
for (int iSheet = 0; iSheet < objWorkBook.Sheets.Count - 1; iSheet++)
{
//objSheet = objWorkBook.Sheets[iSheet] as Worksheet;
///(objSheet as Microsoft.Office.Interop.Excel._Worksheet).Activate();
}
/////////////////
objSheet.Shapes.AddPicture(sFileImage, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 10, 10, 700, 350);
objWorkBook.SaveAs(sFilePath, 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);
}
catch (Exception ex)
{
//Error Alert
}
finally
{
objApp.Quit();
objWorkBook = null;
objApp = null;
}
}
// create excel file
private void CreateExcelFile(string sName)
{
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[2, 2] = "Olap Mining Report";
xlWorkBook.SaveAs(sName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
static void Main(string[] args)
{
var xlApp = new MSExcel.ApplicationClass();
var xlWorkbooks = xlApp.Workbooks;
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
for (int i = 0; i < 500; i++)
{
var workbook = Path.Combine(Environment.CurrentDirectory, "us_foreign_assistance.xls");
//var workbook = "https://explore.data.gov/download/5gah-bvex/XLS";
var xlWorkBook = xlWorkbooks._Open(workbook, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value);
var xlWorkSheet = (MSExcel._Worksheet)xlWorkBook.Sheets["Notes"];
xlWorkSheet.Delete();
xlApp.Visible = false;
workbook = Path.Combine(Environment.CurrentDirectory, string.Format("Book-{0}.xls", i));
xlWorkBook.SaveAs(workbook, MSExcel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value,
false, false, MSExcel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
// Commenting this out will cause memory usage to spike.
xlWorkBook.Close(false, Missing.Value, false);
log.InfoFormat("Wrote \"{0}\".", workbook);
// Commenting this out does not seem to affect memory usage.
Marshal.ReleaseComObject(xlWorkBook);
}
xlApp.DisplayAlerts = true;
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkbooks);
Marshal.ReleaseComObject(xlApp);
/*
Console.Write("Press any key to continue . . . ");
Console.ReadKey(false);
Console.WriteLine();
*/
}
/// <summary>
/// 创建Excel工作薄
/// </summary>
public void CreateExcel(string path)
{
if (File.Exists(path))
{
return;
}
else
{
Excel.Application excelApp; //Excel应用程序
Excel.Workbook excelDoc; //Excel文档
excelApp = new Excel.ApplicationClass();
Object nothing = Missing.Value;
excelDoc = excelApp.Workbooks.Add(nothing);
Object format = Excel.XlFileFormat.xlWorkbookDefault;
excelDoc.SaveAs(path, nothing, nothing, nothing, nothing, nothing,
Excel.XlSaveAsAccessMode.xlExclusive, nothing, nothing, nothing, nothing, nothing);
excelDoc.Close(nothing, nothing, nothing);
excelApp.Quit();
}
}
private void btnCreate_Click(object sender, EventArgs e)
{
Excel.Application xlApp ;
Excel.Workbook xlWorkBook ;
Excel.Worksheet xlWorkSheet ;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "id";
xlWorkSheet.Cells[1, 2] = "name";
xlWorkBook.SaveAs("csharp.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file csharp.xls");
}
private void btnImportFromXml_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "Xml files (*.xml)|*.xml|Excel files (*.xls)|*.xls|All files (*.*)|*.*";
dialog.RestoreDirectory = true;
if (dialog.ShowDialog() != DialogResult.OK)
{
return;
}
if (dialog.FileName.EndsWith(".xml"))
{
XmlDocument xmldoc = new XmlDocument();
try
{
xmldoc.Load(dialog.FileName);
}
catch
{
MessageBox.Show("打开文件错误!");
return;
}
XmlNode rootnode = xmldoc.SelectSingleNode("参数列表");
if (rootnode == null)
{
MessageBox.Show("错误的参数文件!");
return;
}
foreach (XmlNode node in rootnode.ChildNodes)
{
string ParaName = node.ChildNodes[0].InnerText;
ParaName = ParaName.Trim();
ParaData wp = curWaParas.Find(p => p.paraName == ParaName);
if (wp == null)
{
continue;
}
if (wp.paraUnit != node.ChildNodes[1].InnerText.Trim())
{
continue;
}
double value = 0;
double.TryParse(node.ChildNodes[3].InnerText, out value);
wp.paraValue = value;
}
for (int i = 0; i < dataGridViewParaInput.Rows.Count; ++i)
{
dataGridViewParaInput.Rows[i].Cells[1].Value = curWaParas[i].paraValue.ToString();
}
}
if (dialog.FileName.EndsWith(".xls"))
{
Excel.Application app = new Excel.ApplicationClass();
try
{
Object missing = System.Reflection.Missing.Value;
app.Visible = false;
Excel.Workbook wBook = app.Workbooks.Open(dialog.FileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Excel.Worksheet wSheet = wBook.Worksheets[1] as Excel.Worksheet;
if (wSheet.Rows.Count > 1)
{
for (int i = 2; i <= wSheet.Rows.Count; ++i)
{
string cellid = "A" + i.ToString();
Excel.Range DataCell = wSheet.get_Range(cellid, cellid);
string ParaName = (string)DataCell.Text;
ParaName = ParaName.Trim();
if (ParaName == "")
{
break;
}
ParaData wp = curWaParas.Find(p => p.paraName == ParaName);
if (wp == null)
{
continue;
}
if (wp.paraUnit != (string)DataCell.Next.Text)
{
continue;
}
wp.paraValue = (double)DataCell.Next.Next.Value2;
}
}
for (int i = 0; i < dataGridViewParaInput.Rows.Count; ++i)
{
dataGridViewParaInput.Rows[i].Cells[1].Value = curWaParas[i].paraValue.ToString();
}
app.Quit();
app = null;
}
catch (Exception err)
{
MessageBox.Show("导入Excel出错！错误原因：" + err.Message, "提示信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
XLog.Write("从文件\"" + dialog.FileName + "\"导入参数值成功！");
}
private void btnImportFromExcel_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
if (dialog.ShowDialog() != DialogResult.OK)
{
return;
}
Excel.Application app = new Excel.ApplicationClass();
try
{
Object missing = System.Reflection.Missing.Value;
app.Visible = false;
Excel.Workbook wBook = app.Workbooks.Open(dialog.FileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Excel.Worksheet wSheet = wBook.Worksheets[1] as Excel.Worksheet;
if (wSheet.Rows.Count > 1)
{
for (int i = 2; i <= wSheet.Rows.Count; ++i)
{
string cellid = "A" + i.ToString();
Excel.Range DataCell = wSheet.get_Range(cellid, cellid);
string ParaName = (string)DataCell.Text;
ParaName = ParaName.Trim();
if (ParaName == "")
{
break;
}
ParaData wp = curWaParas.Find(p => p.paraName == ParaName);
if (wp == null)
{
continue;
}
if (wp.paraUnit != (string)DataCell.Next.Text)
{
continue;
}
wp.paraValue = (double)DataCell.Next.Next.Value2;
}
}
for (int i = 0; i < dataGridViewParaInput.Rows.Count; ++i)
{
dataGridViewParaInput.Rows[i].Cells[1].Value = curWaParas[i].paraValue.ToString();
}
app.Quit();
app = null;
XLog.Write("从文件\"" + dialog.FileName + "\"导入参数值成功！");
}
catch (Exception err)
{
MessageBox.Show("导入Excel出错！错误原因：" + err.Message, "提示信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void btnExportToXml_Click(object sender, EventArgs e)
{
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "Xml files (*.xml)|*.xml|Excel files (*.xls)|*.xls";
dialog.OverwritePrompt = true;
dialog.RestoreDirectory = true;
if (dialog.ShowDialog() != DialogResult.OK)
{
return;
}
if (dialog.FileName.EndsWith(".xml"))
{
XmlDocument xmldoc = new XmlDocument();
XmlTextWriter writeXml = null;
try
{
writeXml = new XmlTextWriter(dialog.FileName, Encoding.GetEncoding("gb2312"));
}
catch
{
MessageBox.Show("创建或写入文件失败！");
return;
}
writeXml.Formatting = Formatting.Indented;
writeXml.Indentation = 5;
writeXml.WriteStartDocument();
writeXml.WriteStartElement("参数列表");
{
foreach (ParaData wp in curWaParas)
{
writeXml.WriteStartElement("参数");
{
writeXml.WriteStartElement("参数名称");
writeXml.WriteString(wp.paraName);
writeXml.WriteEndElement();
}
{
writeXml.WriteStartElement("参数单位");
writeXml.WriteString(wp.paraUnit);
writeXml.WriteEndElement();
}
{
writeXml.WriteStartElement("参数类型");
writeXml.WriteValue(wp.paraType);
writeXml.WriteEndElement();
}
{
writeXml.WriteStartElement("参数数值");
writeXml.WriteValue(wp.paraValue);
writeXml.WriteEndElement();
}
{
writeXml.WriteStartElement("参数备注");
writeXml.WriteString(wp.strRemark);
writeXml.WriteEndElement();
}
writeXml.WriteEndElement();
}
}
writeXml.WriteEndElement();
writeXml.Close();
}
if (dialog.FileName.EndsWith(".xls"))
{
Excel.Application app = new Excel.ApplicationClass();
try
{
Object missing = System.Reflection.Missing.Value;
app.Visible = false;
Excel.Workbook wBook = app.Workbooks.Add(missing);
Excel.Worksheet wSheet = wBook.Worksheets[1] as Excel.Worksheet;
Excel.Range DataCell = wSheet.get_Range("A1", "A1");
DataCell.Value2 = "参数名称";
DataCell.Next.Value2 = "参数单位";
DataCell.Next.Next.Value2 = "参数数值";
for (int i = 0; i < curWaParas.Count; ++i)
{
ParaData wp = curWaParas[i];
string cellid = "A" + (i + 2).ToString();
DataCell = wSheet.get_Range(cellid, cellid);
DataCell.Value2 = wp.paraName;
DataCell.Next.Value2 = wp.paraUnit;
DataCell.Next.Next.Value2 = wp.paraValue;
}
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//保存工作簿
wBook.SaveAs(dialog.FileName, Excel.XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
wBook.Close(false, missing, missing);
app.Quit();
app = null;
}
catch (Exception err)
{
MessageBox.Show("导出Excel出错！错误原因：" + err.Message, "提示信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
XLog.Write("成功导出参数值到文件\"" + dialog.FileName + "\"！");
}
private void btnExportToExcel_Click(object sender, EventArgs e)
{
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
dialog.OverwritePrompt = true;
dialog.RestoreDirectory = true;
if (dialog.ShowDialog() != DialogResult.OK)
{
return;
}
Excel.Application app = new Excel.ApplicationClass();
try
{
Object missing = System.Reflection.Missing.Value;
app.Visible = false;
Excel.Workbook wBook = app.Workbooks.Add(missing);
Excel.Worksheet wSheet = wBook.Worksheets[1] as Excel.Worksheet;
Excel.Range DataCell = wSheet.get_Range("A1", "A1");
DataCell.Value2 = "参数名称";
DataCell.Next.Value2 = "参数单位";
DataCell.Next.Next.Value2 = "参数数值";
for (int i = 0; i < curWaParas.Count; ++i)
{
ParaData wp = curWaParas[i];
string cellid = "A" + (i + 2).ToString();
DataCell = wSheet.get_Range(cellid, cellid);
DataCell.Value2 = wp.paraName;
DataCell.Next.Value2 = wp.paraUnit;
DataCell.Next.Next.Value2 = wp.paraValue;
}
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//保存工作簿
wBook.SaveAs(dialog.FileName, Excel.XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
wBook.Close(false, missing, missing);
app.Quit();
app = null;
XLog.Write("成功导出参数值到文件\"" + dialog.FileName + "\"！");
}
catch (Exception err)
{
MessageBox.Show("导出Excel出错！错误原因：" + err.Message, "提示信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void exportExcel2()
{
Excel.Application xlApp = new Excel.ApplicationClass();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp.Workbooks);
if (xlApp == null)
{
WebTools.Alert("Excel无法启动");
return;
}
// 创建Excel工作薄
Dictionary<string, List<FpStudentObject>> schoolStudents = QueryStudentGroupbySchool(null, qDateStart.Value, qDateEnd.Value);
String[] sheetTitles = { "缺受理号", "未收费", "已收费" };
String[] sheetColumns = { "受理号", "姓名", "身份证号码", "准驾车型", "导入时间" };
String dir = MapPath(string.Format("~/temp/{0}/",Session.SessionID));
Directory.CreateDirectory(dir);
String excelName=string.Format("指纹记录[{0}][{1}].xls",this.qDateStart.Value,this.qDateEnd.Value);
String excelPath = dir +excelName ;
if (File.Exists(excelPath)) {
File.Delete(excelPath);
}
xlApp.SheetsInNewWorkbook = schoolStudents.Keys.Count ;
xlApp.Workbooks.Add(Missing.Value);
Excel.Workbook xlBook = xlApp.ActiveWorkbook;
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook.Worksheets);
int sheetIndex = 0;
// try
// {
//xlBook.Worksheets.Add(Missing.Value, Missing.Value, schoolStudents.Keys.Count - 1, Missing.Value);
//}
// catch (Exception ex) {
// WebTools.Alert(ex.Message);
// return;
// }
foreach (string schoolCode in schoolStudents.Keys)
{
WebTools.Alert(schoolCode);
sheetIndex++;
List<FpStudentObject> students = schoolStudents[schoolCode];
string schoolName =schoolCode;
ArrayList deps= SimpleOrmOperator.QueryConditionList<DepartMent>(string.Format("where c_depcode='{0}'", schoolCode));
if (deps.Count > 0) {
schoolName = (deps[0] as DepartMent).DepNickName;
}
int rowIndex = 1;
foreach (FpStudentObject student in students)
{
int color = 0;
string result = "";
if (string.IsNullOrEmpty(student.LSH))
{
color = 3;
result = "缺流水号";
}
else if (student.FEE_STATUE != "Y")
{
color = 6;
result = "收费审核未通过";
}
else {
result = "考勤进行中";
}
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[sheetIndex];
xlSheet.Name = string.Format("{0}({1})", schoolName, schoolStudents[schoolCode].Count);
int colIndex = 1;
Excel.Range rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
//rang.Select();
rang.NumberFormatLocal = "@";
rang.ColumnWidth = 20;
rang.Interior.ColorIndex = color;
rang.Formula = student.LSH;
rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
//rang.Select();
rang.NumberFormatLocal = "@";
rang.ColumnWidth = 15;
rang.Formula = student.NAME;
rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
//rang.Select();
rang.NumberFormatLocal = "@";
rang.ColumnWidth = 25;
rang.Formula = student.IDCARD;
rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
rang.Formula = student.CAR_TYPE;
rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
//rang.Select();
rang.ColumnWidth = 15;
rang.Value2 = student.CREATE_TIME.ToLongDateString();
rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
//rang.Select();
rang.ColumnWidth = 30;
rang.Value2 = result;
rowIndex++;
}
}
try{
xlBook.Save();
xlBook.Saved = true;
xlBook.SaveCopyAs(excelPath);
WebTools.Alert("保存成功");
}
finally
{
xlBook.Close(true, Missing.Value, Missing.Value);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook.Worksheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
}
xlApp.Workbooks.Close();
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(excelName, Encoding.UTF8));
Response.WriteFile(excelPath);
Response.End();
Response.Close();
}
private void lnkexporttoexcel_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
Excel.Range cells = xlWorkSheet.Cells;
for (i = 1; i <= gdvmeapresult.ColumnCount; i++)
{
string column = gdvmeapresult.Columns[i - 1].Name;
xlWorkSheet.Cells[2, i + 1] = column;
cells = (Excel.Range)xlWorkSheet.Cells[2, i + 1];
cells.Font.Bold = true;
cells.Borders.Weight = Excel.XlBorderWeight.xlMedium;
cells.ColumnWidth = 15;
}
for (i = 2; i <= gdvmeapresult.RowCount + 1; i++)
{
for (j = 2; j <= gdvmeapresult.ColumnCount + 1; j++)
{
DataGridViewCell cell = gdvmeapresult[j - 2, i - 2];
xlWorkSheet.Cells[i + 1, j] = Convert.ToString(cell.Value);
////format cells
if (j > 2)
{
Color bgc = Color.Pink;
Color fgc = Color.Black;
if (cell.Value.ToString() == "-")//data not available
{
bgc = Color.Gray;
}
else
{
decimal mapevalue = decimal.Parse(cell.Value.ToString());
if (mapevalue > 25)
bgc = Color.Red; //Color.Black;//(204, 51, 51);
if (mapevalue < -25)
bgc = Color.Blue;//(114, 154, 210);
if (mapevalue <= 25 && mapevalue >= -25)
bgc = Color.White;
}
((Excel.Range)xlWorkSheet.Cells[i + 1, j]).Interior.Color = System.Drawing.ColorTranslator.ToOle(bgc);
((Excel.Range)xlWorkSheet.Cells[i + 1, j]).Font.Color = System.Drawing.ColorTranslator.ToOle(fgc);
}
((Excel.Range)xlWorkSheet.Cells[i + 1, j]).Borders.Weight = Excel.XlBorderWeight.xlThin;
//end cell format
}
}
//last row range
Excel.Range range = xlWorkSheet.get_Range(xlWorkSheet.Cells[i + 1, 2], xlWorkSheet.Cells[i + 1, gdvmeapresult.ColumnCount + 1]);
range.Merge(true);
range.RowHeight = 40;
range.WrapText = true;
xlWorkSheet.Cells[i + 1, 2] = "Note: Red represents underforecasts (>25%), Blue represents overforecasts (<-25%)," +
"Gray represents insufficient data to complete the forecast, and no field color represents an accurate forecast, within 25%.";
//header
Excel.Range header = xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]);
header = header.EntireRow;
for (int no = 0; no < 5; no++)
{
header.Insert(Excel.XlInsertShiftDirection.xlShiftDown, misValue);
}
header = xlWorkSheet.get_Range(xlWorkSheet.Cells[2, 2], xlWorkSheet.Cells[2, gdvmeapresult.ColumnCount + 1]);
header.Value = "Forecast MAPE Result";
header.Merge(true);
header.Font.Underline = true;
header.Font.Bold = true;
header.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
header.RowHeight = 40;
header.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.Cells[3, 2] = "Forecast Id : ";
xlWorkSheet.Cells[3, 3] = forecastInfo.ForecastNo;
xlWorkSheet.Cells[3, 4] = "Methodology : ";
xlWorkSheet.Cells[3, 5] = forecastInfo.Methodology;
xlWorkSheet.Cells[3, 6] = "Wastage % : ";
xlWorkSheet.Cells[3, 7] = forecastInfo.Westage.ToString();
xlWorkSheet.Cells[4, 2] = "Start Date : ";
xlWorkSheet.Cells[4, 3] = forecastInfo.StartDate.ToShortDateString();
xlWorkSheet.Cells[4, 4] = "Data Usage : ";
xlWorkSheet.Cells[4, 5] = forecastInfo.DataUsage;
xlWorkSheet.Cells[4, 6] = "Add By % : ";
xlWorkSheet.Cells[4, 7] = forecastInfo.Scaleup.ToString();
xlWorkSheet.Cells[5, 2] = "Period : ";
xlWorkSheet.Cells[5, 3] = forecastInfo.Period;
xlWorkSheet.Cells[5, 4] = "Regression Type : ";
xlWorkSheet.Cells[5, 5] = forecastInfo.Method;
xlWorkSheet.Cells[5, 6] = "Extension Period : ";
xlWorkSheet.Cells[5, 7] = forecastInfo.Extension.ToString();
//header end
saveFileDialog1.FileName = forecastInfo.ForecastNo + ".xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
xlWorkBook.SaveAs(saveFileDialog1.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
LqtUtil.releaseObject(xlWorkSheet);
LqtUtil.releaseObject(xlWorkBook);
LqtUtil.releaseObject(xlApp);
MessageBox.Show("Exported Successfully!");
}
}
private void backgroundWorker2_DoWork(object sender, DoWorkEventArgs e)
{
statusClose = 1;
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
Thread.CurrentThread.CurrentUICulture = new CultureInfo("en-US");
Excel.Application xlApp = null;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
Excel.Sheets xlSheets = null;
object misValue = System.Reflection.Missing.Value;
try
{
xlApp = new Excel.ApplicationClass();
//xlWorkBook = xlApp.Workbooks.Add(misValue); if there is no existing excel file
xlWorkBook = xlApp.Workbooks.Open(textBox1.Text, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); if there is no existing excel file
xlSheets = xlWorkBook.Worksheets;
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.get_Item("Sheet1");
Microsoft.Office.Interop.Excel.Range range = xlWorkSheet.UsedRange;
//xlWorkSheet.Cells[2, 2] = "http://csharp.net-informations.com"; if there is no existing excel file
//int colCount = range.Columns.Count;
//int rowCount = range.Rows.Count;
//for (int index = 1; index < 20; index++)
//{
// xlWorkSheet.Cells[rowCount + index, 1] = rowCount + index;
// xlWorkSheet.Cells[rowCount + index, 2] = "New Item" + index;
//}
int baris = 1; //cari cara menghitung jumlah baris yg telah ditulis di excel
if (cekBaris(xlWorkSheet) > 1)
baris = cekBaris(xlWorkSheet);
progressBar1.SafeControlInvoke(ProgressBar => progressBar1.Maximum = listView1.SelectedItems.Count + 1);
progressBar1.SafeControlInvoke(ProgressBar => progressBar1.Minimum = 0);
listView1.SafeControlInvoke(listView =>
{
foreach (ListViewItem items in listView1.SelectedItems)
{
baris++;
progressBar1.SafeControlInvoke(ProgressBar => progressBar1.Value++);
xlWorkSheet.Cells[baris, 1] = " ";
xlWorkSheet.Cells[baris, 2] = items.SubItems[0].Text;
xlWorkSheet.Cells[baris, 3] = items.SubItems[1].Text;
xlWorkSheet.Cells[baris, 4] = items.SubItems[2].Text;
xlWorkSheet.Cells[baris, 5] = items.SubItems[3].Text;
xlWorkSheet.Cells[baris, 6] = items.SubItems[4].Text;
xlWorkSheet.Cells[baris, 7] = items.SubItems[5].Text;
xlWorkSheet.Cells[baris, 8] = items.SubItems[6].Text;
xlWorkSheet.Cells[baris, 9] = items.SubItems[7].Text;
xlWorkSheet.Cells[baris, 10] = items.SubItems[8].Text;
xlWorkSheet.Cells[baris, 11] = items.SubItems[9].Text;
xlWorkSheet.Cells[baris, 12] = items.SubItems[10].Text;
xlWorkSheet.Cells[baris, 13] = items.SubItems[11].Text;
xlWorkSheet.Cells[baris, 14] = items.SubItems[12].Text;
xlWorkSheet.Cells[baris, 15] = items.SubItems[13].Text;
}
});
xlApp.AlertBeforeOverwriting = false;
xlApp.DisplayAlerts = false;
xlWorkBook.SaveAs(textBox1.Text, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
catch (Exception ex)
{
MessageBox.Show("Terjadi kesalahan penulisan excel\nPesan kesalahan : " + ex.Message, "PERHATIAN");
}
finally
{
releaseObject(xlSheets);
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
private void button3_Click(object sender, EventArgs e)
{
disableButton(0);
string filePath = "";
if (this.folderBrowserDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
filePath = folderBrowserDialog1.SelectedPath + "\\Laporan_" + string.Format("{0:dd_MM_yyyy}", DateTime.Now) + ".xls";
filePath = filePath.Replace(@"\\", @"\");
textBox1.Text = filePath;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
Excel.Range chartRange;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add data
xlWorkSheet.Cells[1, 1] = "No Urut";
xlWorkSheet.Cells[1, 2] = "Tgl Bayar";
xlWorkSheet.Cells[1, 3] = "No Bukti";
xlWorkSheet.Cells[1, 4] = "KPA";
xlWorkSheet.Cells[1, 5] = "Ket bayar";
xlWorkSheet.Cells[1, 6] = "No rekening";
xlWorkSheet.Cells[1, 7] = "penerimaan";
xlWorkSheet.Cells[1, 8] = "pengeluaran";
xlWorkSheet.Cells[1, 9] = "sisa";
xlWorkSheet.Cells[1, 10] = "ppn";
xlWorkSheet.Cells[1, 11] = "pph";
xlWorkSheet.Cells[1, 12] = "f_df";
xlWorkSheet.Cells[1, 13] = "no ppn";
xlWorkSheet.Cells[1, 14] = "NTPNPPn";
xlWorkSheet.Cells[1, 15] = "NTPNPPh";
#region Beberapa contoh manipulasi excel
//xlWorkSheet.Cells[4, 2] = "";
//xlWorkSheet.Cells[4, 3] = "Student1";
//xlWorkSheet.Cells[4, 4] = "Student2";
//xlWorkSheet.Cells[4, 5] = "Student3";
//xlWorkSheet.Cells[5, 2] = "Term1";
//xlWorkSheet.Cells[5, 3] = "80";
//xlWorkSheet.Cells[5, 4] = "65";
//xlWorkSheet.Cells[5, 5] = "45";
//xlWorkSheet.Cells[6, 2] = "Term2";
//xlWorkSheet.Cells[6, 3] = "78";
//xlWorkSheet.Cells[6, 4] = "72";
//xlWorkSheet.Cells[6, 5] = "60";
//xlWorkSheet.Cells[7, 2] = "Term3";
//xlWorkSheet.Cells[7, 3] = "82";
//xlWorkSheet.Cells[7, 4] = "80";
//xlWorkSheet.Cells[7, 5] = "65";
//xlWorkSheet.Cells[8, 2] = "Term4";
//xlWorkSheet.Cells[8, 3] = "75";
//xlWorkSheet.Cells[8, 4] = "82";
//xlWorkSheet.Cells[8, 5] = "68";
//xlWorkSheet.Cells[9, 2] = "Total";
//xlWorkSheet.Cells[9, 3] = "315";
//xlWorkSheet.Cells[9, 4] = "299";
//xlWorkSheet.Cells[9, 5] = "238";
//xlWorkSheet.get_Range("b2", "e3").Merge(false);
//chartRange = xlWorkSheet.get_Range("b2", "e3");
//chartRange.FormulaR1C1 = "MARK LIST";
//chartRange.HorizontalAlignment = 3;
//chartRange.VerticalAlignment = 3;
//chartRange = xlWorkSheet.get_Range("a1", "r1");
//chartRange.Font.Bold = true;
//chartRange = xlWorkSheet.get_Range("b9", "e9");
//chartRange.Font.Bold = true;
//chartRange = xlWorkSheet.get_Range("b2", "e9");
//chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
#endregion
chartRange = xlWorkSheet.get_Range("a1", "o1");
chartRange.Font.Bold = true;
chartRange = xlWorkSheet.get_Range("a1", "o1");
chartRange.Font.Size = 12;
xlApp.AlertBeforeOverwriting = false;
xlApp.DisplayAlerts = false;
xlWorkBook.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlApp);
releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);
MessageBox.Show("File Tercetak", "KONFIRMASI");
}
disableButton(1);
}
private void exportExcel()
{
this.btnExport.Enabled = false;
string btnText = this.btnExport.Text;
this.btnExport.Text = "报表生成中....";
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBoxHelper.Show("Microsoft Excel 无法启动");
return;
}
// 创建Excel工作薄
Dictionary<string, DepartMent> cacheDeps = DepartMent.queryDict(oraclDataAccess);
Dictionary<string, List<FpStudentObject>> schoolStudents = FpStudentObject.QueryGroupbySchool(oraclDataAccess,null, dtpStart.Value.ToShortDateString(), dtpEnd.Value.ToShortDateString());
String[] sheetTitles = { "缺受理号", "未收费", "已收费" };
String[] sheetColumns = { "受理号", "姓名", "身份证号码", "准驾车型", "导入时间" };
//String dir = MapPath("~/temp/");
//Directory.CreateDirectory(dir);
//String excelPath = this.openFileDialog1.FileName;
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp.Workbooks);
if (schoolStudents.Keys.Count < 1) {
MessageBoxHelper.Show("没有查询到记录");
return;
}
xlApp.SheetsInNewWorkbook = schoolStudents.Keys.Count;
Excel.Workbook xlBook = xlApp.Workbooks.Add(Missing.Value);
int sheetIndex = 0;
//xlBook.Sheets.Add(Missing.Value, Missing.Value, schoolStudents.Keys.Count - 1, Missing.Value);
foreach (string schoolCode in schoolStudents.Keys)
{
sheetIndex++;
List<FpStudentObject> students = schoolStudents[schoolCode];
string schoolName = schoolCode;
try
{
schoolName = cacheDeps[schoolCode].DepNickName;
}
catch (Exception ex) { }
tssInfo.Text = string.Format("({2}/{3}).{0}:{1}...", schoolName, students.Count, sheetIndex, schoolStudents.Keys.Count);
int rowIndex = 1;
foreach (FpStudentObject student in students)
{
int color = 0;
string result = "";
if (string.IsNullOrEmpty(student.LSH))
{
color = 3;
result = "缺流水号";
}
else if (student.FEE_STATUE != "Y")
{
color = 6;
result = "收费审核未通过";
}
else
{
result = "考勤进行中";
}
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[sheetIndex];
xlSheet.Name = string.Format("{0}({1})", schoolName, schoolStudents[schoolCode].Count);
int colIndex = 1;
Excel.Range rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
//rang.Select();
rang.NumberFormatLocal = "@";
rang.ColumnWidth = 20;
rang.Interior.ColorIndex = color;
rang.Formula = student.LSH;
rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
//rang.Select();
rang.NumberFormatLocal = "@";
rang.ColumnWidth = 15;
rang.Formula = student.NAME;
rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
//rang.Select();
rang.NumberFormatLocal = "@";
rang.ColumnWidth = 25;
rang.Formula = student.IDCARD;
rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
rang.Formula = student.CAR_TYPE;
rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
//rang.Select();
rang.ColumnWidth = 15;
rang.Value2 = student.CREATE_TIME.ToLongDateString();
rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
//rang.Select();
rang.ColumnWidth = 30;
rang.Value2 = result;
rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
//rang.Select();
rang.ColumnWidth = 30;
rang.Value2 = student.SCHOOL_NAME;
rowIndex++;
}
}
try
{
xlBook.Save();
xlBook.Saved = true;
xlBook.SaveCopyAs(target);
}
finally
{
xlBook.Close(true, Missing.Value, Missing.Value);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook.Worksheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
}
xlApp.Workbooks.Close();
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
this.btnExport.Text = btnText;
this.btnExport.Enabled = true;
this.target = string.Empty;
this.tssInfo.Text = string.Empty;
}
public static void ExportForDriverToExcel(DateTime date)
{
FolderBrowserDialog sfd = new FolderBrowserDialog();
sfd.Description = "Şöförler için excel";
if (sfd.ShowDialog() == DialogResult.OK)
{
Excel.Application ExcelApp;
Excel.Workbook objBook;
object missing = System.Reflection.Missing.Value;
object fileName = "normal.dot";
object newTemplate = false;
object docType = 0;
ExcelApp = new Excel.ApplicationClass();
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
ExcelApp.Visible = false;
objBook = ExcelApp.Workbooks.Add(missing);
#region sheet1
objBook.Sheets.Add();
Excel.Worksheet objSheet = (Excel.Worksheet)objBook.Sheets[1];
objSheet.Name = "Anadolu";
#region Header for Anadolu
objSheet.Cells[1, 1] = "NO";
objSheet.get_Range("A1", "A1").Font.Bold = true;
objSheet.get_Range("A1", "A1").Font.Color = -16776961;
objSheet.get_Range("A1", "A1").Font.Size = 10;
objSheet.get_Range("A1", "A1").ColumnWidth = 5.29;
objSheet.get_Range("A1", "A1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
objSheet.get_Range("A1", "A1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.Cells[1, 2] = "AD SOYAD";
objSheet.get_Range("B1", "B1").Font.Bold = true;
objSheet.get_Range("B1", "B1").Font.Color = -16776961;
objSheet.get_Range("B1", "B1").Font.Size = 10;
objSheet.get_Range("B1", "B1").ColumnWidth = 14.86;
objSheet.get_Range("B1", "B1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
objSheet.get_Range("B1", "B1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.Cells[1, 3] = "TELEFON NO";
objSheet.get_Range("C1", "C1").Font.Bold = true;
objSheet.get_Range("C1", "C1").Font.Color = -16776961;
objSheet.get_Range("C1", "C1").Font.Size = 10;
objSheet.get_Range("C1", "C1").ColumnWidth = 18.43;
objSheet.get_Range("C1", "C1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
objSheet.get_Range("C1", "C1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
objSheet.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.Cells[1, 4] = "ADRES";
objSheet.get_Range("D1", "D1").Font.Bold = true;
objSheet.get_Range("D1", "D1").Font.Color = -16776961;
objSheet.get_Range("D1", "D1").Font.Size = 10;
objSheet.get_Range("D1", "D1").ColumnWidth = 18;
objSheet.get_Range("D1", "D1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
objSheet.get_Range("D1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
objSheet.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
Dictionary<int, int> mypairs = new Dictionary<int, int>();
string[] productCols = Order.getUsedProductTypes(date);
int colRow = 5;
for (int j = 0; j < productCols.Length; j++)
{
objSheet.Cells[1, colRow] = productCols[j];
(objSheet.Cells[1, colRow] as Excel.Range).ColumnWidth = 4.20;
(objSheet.Cells[1, colRow] as Excel.Range).Font.Bold = true;
(objSheet.Cells[1, colRow] as Excel.Range).Font.Color = -16776961;
(objSheet.Cells[1, colRow] as Excel.Range).Font.Size = 10;
(objSheet.Cells[1, colRow] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[1, colRow] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
mypairs.Add(colRow, 0);
colRow++;
}
objSheet.Cells[1, colRow] = "AÇIKLAMA";
(objSheet.Cells[1, colRow] as Excel.Range).Font.Bold = true;
(objSheet.Cells[1, colRow] as Excel.Range).Font.Color = -16776961;
(objSheet.Cells[1, colRow] as Excel.Range).Font.Size = 10;
(objSheet.Cells[1, colRow] as Excel.Range).ColumnWidth = 17.14;
(objSheet.Cells[1, colRow] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[1, colRow] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
colRow++;
#endregion
int row = 2;
//this gets the order list
List<Order> ords = Order.Orders_GetByDate(date);
for (int k = 0; k < ords.Count; k++)
{
bool goon = true;
Addresses[] addListTop = Addresses.Addresses_GetByCustomerID(ords[k].CustomerID).ToArray();
for (int i = 0; i < addListTop.Length; i++)
{
if (addListTop[i].Type == ords[k].AddressType)
{
if (addListTop[i].Area == 0)
{
goon = false;
continue;
}
}
}
if (!goon)
{
continue;
}
objSheet.Cells[row, 1] = Customer.Customer.Customer_GetByID(ords[k].CustomerID).PreFix + " " + Customer.Customer.Customer_GetByID(ords[k].CustomerID).No.ToString().PadLeft(3, '0');
(objSheet.Cells[row, 1] as Excel.Range).WrapText = true;
(objSheet.Cells[row, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[row, 1] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
string strName = (Customer.Customer.Customer_GetByID(ords[k].CustomerID).AutorizedName != "") ? Customer.Customer.Customer_GetByID(ords[k].CustomerID).AutorizedName : Customer.Customer.Customer_GetByID(ords[k].CustomerID).Name;
//ad
objSheet.Cells[row, 2] = strName;
(objSheet.Cells[row, 2] as Excel.Range).WrapText = true;
(objSheet.Cells[row, 2] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[row, 2] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
//telefon
objSheet.Cells[row, 3] = ords[k].Telephone;
(objSheet.Cells[row, 3] as Excel.Range).WrapText = true;
(objSheet.Cells[row, 3] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[row, 3] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[row, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
//adres.
objSheet.Cells[row, 4] = ords[k].Address;
(objSheet.Cells[row, 4] as Excel.Range).WrapText = true;
(objSheet.Cells[row, 4] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[row, 4] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[row, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
OrderDetail[] odtmp = ords[k].getDetails().ToArray();
int colIndex = 5;
for (int i = 0; i < odtmp.Length; i++)
{
for (int j = 0; j < productCols.Length; j++)
{
if (Product.Product.Products_GetByID(odtmp[i].ProductID).Name == productCols[j])
{
mypairs[colIndex + j] += odtmp[i].Unit;
objSheet.Cells[row, colIndex + j] = odtmp[i].Unit;
(objSheet.Cells[row, colIndex + j] as Excel.Range).WrapText = true;
(objSheet.Cells[row, colIndex + j] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[row, colIndex + j] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
}
}
}
objSheet.Cells[row, colIndex + productCols.Length] = ords[k].Description;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).WrapText = true;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
row++;
}
objSheet.Cells[3 + row, 4] = "TOPLAM";
Dictionary<int, int>.KeyCollection keys = mypairs.Keys;
foreach (int i in keys)
{
objSheet.Cells[3 + row, i] = mypairs[i];
(objSheet.Cells[3 + row, i] as Excel.Range).Font.Bold = true;
}
//ExcelApp.ActiveWindow = Excel.XlWindowView.xlPageBreakPreview;
//objSheet.VPageBreaks[1].DragOff(Excel.XlDirection.xlToRight, 1);
//ExcelApp.ActiveWindow = Excel.XlWindowView.xlNormalView;
#endregion
#region sheet2
objBook.Sheets.Add();
Excel.Worksheet objSheet2 = (Excel.Worksheet)objBook.Sheets[2];
objSheet2.Name = "Avrupa";
#region Header for Avrupa
objSheet2.Cells[1, 1] = "NO";
objSheet2.get_Range("A1", "A1").Font.Bold = true;
objSheet2.get_Range("A1", "A1").Font.Color = -16776961;
objSheet2.get_Range("A1", "A1").Font.Size = 10;
objSheet2.get_Range("A1", "A1").ColumnWidth = 5.29;
objSheet2.get_Range("A1", "A1").WrapText = true;
objSheet2.get_Range("A1", "A1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
objSheet2.get_Range("A1", "A1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
objSheet2.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.Cells[1, 2] = "AD SOYAD";
objSheet2.get_Range("B1", "B1").Font.Bold = true;
objSheet2.get_Range("B1", "B1").Font.Color = -16776961;
objSheet2.get_Range("B1", "B1").Font.Size = 10;
objSheet2.get_Range("B1", "B1").ColumnWidth = 14.86;
objSheet2.get_Range("B1", "B1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
objSheet2.get_Range("B1", "B1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
objSheet2.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.Cells[1, 3] = "TELEFON NO";
objSheet2.get_Range("C1", "C1").Font.Bold = true;
objSheet2.get_Range("C1", "C1").Font.Color = -16776961;
objSheet2.get_Range("C1", "C1").Font.Size = 10;
objSheet2.get_Range("C1", "C1").ColumnWidth = 18.43;
objSheet2.get_Range("C1", "C1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
objSheet2.get_Range("C1", "C1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
objSheet2.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.Cells[1, 4] = "ADRES";
objSheet2.get_Range("D1", "D1").Font.Bold = true;
objSheet2.get_Range("D1", "D1").Font.Color = -16776961;
objSheet2.get_Range("D1", "D1").Font.Size = 10;
objSheet2.get_Range("D1", "D1").ColumnWidth = 18;
objSheet2.get_Range("D1", "D1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
objSheet2.get_Range("D1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
objSheet2.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet2.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
Dictionary<int, int> mypairs2 = new Dictionary<int, int>();
string[] productCols2 = Order.getUsedProductTypes(date);
int colRow2 = 5;
for (int j = 0; j < productCols2.Length; j++)
{
objSheet2.Cells[1, colRow2] = productCols2[j];
(objSheet2.Cells[1, colRow2] as Excel.Range).ColumnWidth = 4.20;
(objSheet2.Cells[1, colRow2] as Excel.Range).Font.Bold = true;
(objSheet2.Cells[1, colRow2] as Excel.Range).Font.Color = -16776961;
(objSheet2.Cells[1, colRow2] as Excel.Range).Font.Size = 10;
(objSheet2.Cells[1, colRow2] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet2.Cells[1, colRow2] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
mypairs2.Add(colRow2, 0);
colRow2++;
}
objSheet2.Cells[1, colRow2] = "AÇIKLAMA";
(objSheet2.Cells[1, colRow2] as Excel.Range).Font.Bold = true;
(objSheet2.Cells[1, colRow2] as Excel.Range).Font.Color = -16776961;
(objSheet2.Cells[1, colRow2] as Excel.Range).Font.Size = 10;
(objSheet2.Cells[1, colRow2] as Excel.Range).ColumnWidth = 17.14;
(objSheet2.Cells[1, colRow2] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet2.Cells[1, colRow2] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
colRow2++;
#endregion
int row2 = 2;
//this gets the order list
List<Order> ords2 = Order.Orders_GetByDate(date);
for (int k = 0; k < ords2.Count; k++)
{
bool goon = true;
Addresses[] addListTop = Addresses.Addresses_GetByCustomerID(ords2[k].CustomerID).ToArray();
for (int i = 0; i < addListTop.Length; i++)
{
if (addListTop[i].Type == ords2[k].AddressType)
{
if (addListTop[i].Area == 1)
{
goon = false;
continue;
}
}
}
if (!goon)
{
continue;
}
objSheet2.Cells[row2, 1] = Customer.Customer.Customer_GetByID(ords2[k].CustomerID).PreFix + " " + Customer.Customer.Customer_GetByID(ords2[k].CustomerID).No.ToString().PadLeft(3, '0');
(objSheet2.Cells[row2, 1] as Excel.Range).WrapText = true;
(objSheet2.Cells[row2, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet2.Cells[row2, 1] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet2.Cells[row2, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
string strName = (Customer.Customer.Customer_GetByID(ords2[k].CustomerID).AutorizedName != "") ? Customer.Customer.Customer_GetByID(ords2[k].CustomerID).AutorizedName : Customer.Customer.Customer_GetByID(ords2[k].CustomerID).Name;
objSheet2.Cells[row2, 2] = strName;
(objSheet2.Cells[row2, 2] as Excel.Range).WrapText = true;
(objSheet2.Cells[row2, 2] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet2.Cells[row2, 2] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet2.Cells[row2, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
objSheet2.Cells[row2, 3] = ords2[k].Telephone;
(objSheet2.Cells[row2, 3] as Excel.Range).WrapText = true;
(objSheet2.Cells[row2, 3] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet2.Cells[row2, 3] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet2.Cells[row2, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
objSheet2.Cells[row2, 4] = ords2[k].Address;
(objSheet2.Cells[row2, 4] as Excel.Range).WrapText = true;
(objSheet2.Cells[row2, 4] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet2.Cells[row2, 4] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet2.Cells[row2, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
OrderDetail[] odtmp = ords2[k].getDetails().ToArray();
int colIndex = 5;
for (int i = 0; i < odtmp.Length; i++)
{
for (int j = 0; j < productCols2.Length; j++)
{
if (Product.Product.Products_GetByID(odtmp[i].ProductID).Name == productCols2[j])
{
mypairs2[colIndex + j] += odtmp[i].Unit;
objSheet2.Cells[row2, colIndex + j] = odtmp[i].Unit;
(objSheet2.Cells[row2, colIndex + j] as Excel.Range).WrapText = true;
(objSheet2.Cells[row2, colIndex + j] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet2.Cells[row2, colIndex + j] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet2.Cells[row2, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
}
}
}
objSheet2.Cells[row2, colIndex + productCols2.Length] = ords2[k].Description;
(objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).WrapText = true;
(objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
row2++;
}
objSheet2.Cells[3 + row2, 4] = "TOPLAM";
Dictionary<int, int>.KeyCollection keys2 = mypairs2.Keys;
foreach (int i in keys2)
{
objSheet2.Cells[3 + row2, i] = mypairs2[i];
(objSheet2.Cells[3 + row2, i] as Excel.Range).Font.Bold = true;
}
Dictionary<int, int> totalPair = new Dictionary<int, int>();
Dictionary<int, int>.KeyCollection keeys = mypairs.Keys;
foreach (int i in keeys)
{
if (!totalPair.ContainsKey(i))
{
totalPair.Add(i, mypairs[i]);
}
else
{
totalPair[i] += mypairs[i];
}
}
Dictionary<int, int>.KeyCollection keeys2 = mypairs2.Keys;
foreach (int i in keeys2)
{
if (!totalPair.ContainsKey(i))
{
totalPair.Add(i, mypairs2[i]);
}
else
{
totalPair[i] += mypairs2[i];
}
}
Dictionary<int, int>.KeyCollection totalKeys = totalPair.Keys;
objSheet2.Cells[5 + row2, 4] = "GENEL TOPLAM";
objSheet.Cells[5 + row, 4] = "GENEL TOPLAM";
foreach (int item in totalKeys)
{
objSheet.Cells[5 + row, item] = totalPair[item];
(objSheet.Cells[5 + row, item] as Excel.Range).Font.Bold = true;
objSheet2.Cells[5 + row2, item] = totalPair[item];
(objSheet2.Cells[5 + row2, item] as Excel.Range).Font.Bold = true;
}
#endregion
string fileNameXLS = date.Day.ToString("00") + "." + date.Month.ToString("00") + "." + date.Year + "-Şöför.xls";
objBook.SaveAs(sfd.SelectedPath + "\\" + fileNameXLS, Excel.XlFileFormat.xlWorkbookNormal, null, null, true, false, Excel.XlSaveAsAccessMode.xlNoChange, false, false, null, null, null);
objBook.Close(false, Type.Missing, System.Type.Missing);
ExcelApp.Quit();
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("tr-TR");
}
}
/// <summary>
/// Converts an Excel sheet to PDF
/// </summary>
/// <param name="inputFile">The Excel input file</param>
/// <param name="outputFile">The PDF output file</param>
/// <returns></returns>
/// <exception cref="OCCsvFileLimitExceeded">Raised when a CSV <paramref name="inputFile"/> has to many rows</exception>
internal static void Convert(string inputFile, string outputFile)
{
// We only need to perform this check if we are running on a server
if (NativeMethods.IsWindowsServer())
CheckIfSystemProfileDesktopDirectoryExists();
CheckIfPrinterIsInstalled();
DeleteAutoRecoveryFiles();
ExcelInterop.Application excel = null;
ExcelInterop.Workbook workbook = null;
string tempFileName = null;
try
{
excel = new ExcelInterop.ApplicationClass
{
ScreenUpdating = false,
DisplayAlerts = false,
DisplayDocumentInformationPanel = false,
DisplayRecentFiles = false,
DisplayScrollBars = false,
AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityForceDisable,
PrintCommunication = true, // DO NOT REMOVE THIS LINE, NO NEVER EVER ... DON'T EVEN TRY IT
Visible = false
};
var extension = Path.GetExtension(inputFile);
if (string.IsNullOrWhiteSpace(extension))
extension = string.Empty;
if (extension.ToUpperInvariant() == ".CSV")
{
// Yes this look somewhat weird but we have to change the extension if we want to handle
// CSV files with different kind of separators. Otherwhise Excel will always overrule whatever
// setting we make to open a file
tempFileName = Path.GetTempFileName() + Guid.NewGuid() + ".txt";
File.Copy(inputFile, tempFileName);
inputFile = tempFileName;
}
workbook = Open(excel, inputFile, extension, false);
// We cannot determine a print area when the document is marked as final so we remove this
workbook.Final = false;
var usedSheets = 0;
foreach (var sheetObject in workbook.Sheets)
{
var sheet = sheetObject as ExcelInterop.Worksheet;
if (sheet != null)
{
var protection = sheet.Protection;
var activeWindow = excel.ActiveWindow;
try
{
// ReSharper disable once RedundantCast
((Microsoft.Office.Interop.Excel._Worksheet)sheet).Activate();
if (!sheet.ProtectContents || protection.AllowFormattingColumns)
if (activeWindow.View != ExcelInterop.XlWindowView.xlPageLayoutView)
sheet.Columns.AutoFit();
}
catch (COMException)
{
// Do nothing, this sometimes failes and there is nothing we can do about it
}
finally
{
Marshal.ReleaseComObject(activeWindow);
Marshal.ReleaseComObject(protection);
}
var printArea = GetWorksheetPrintArea(sheet);
switch (printArea)
{
case "shapes":
SetWorkSheetPaperSize(sheet, string.Empty);
usedSheets += 1;
break;
case "":
break;
default:
SetWorkSheetPaperSize(sheet, printArea);
usedSheets += 1;
break;
}
Marshal.ReleaseComObject(sheet);
continue;
}
var chart = sheetObject as ExcelInterop.Chart;
if (chart != null)
{
SetChartPaperSize(chart);
Marshal.ReleaseComObject(chart);
}
}
// It is not possible in Excel to export an empty workbook
if (usedSheets != 0)
workbook.ExportAsFixedFormat(ExcelInterop.XlFixedFormatType.xlTypePDF, outputFile);
else
throw new OCFileContainsNoData("The file '" + Path.GetFileName(inputFile) + "' contains no data");
}
finally
{
if (workbook != null)
{
workbook.Saved = true;
workbook.Close(false);
Marshal.ReleaseComObject(workbook);
}
if (excel != null)
{
excel.Quit();
Marshal.ReleaseComObject(excel);
}
if (!string.IsNullOrEmpty(tempFileName) && File.Exists(tempFileName))
File.Delete(tempFileName);
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
public static string ExportToExcel(DateTime date)
{
FolderBrowserDialog sfd = new FolderBrowserDialog();
sfd.Description = "Excel Rapor";
//sfd.Filter = "Excel|*.xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
Excel.Application ExcelApp;
Excel.Workbook objBook;
object missing = System.Reflection.Missing.Value;
object fileName = "normal.dot";
object newTemplate = false;
object docType = 0;
ExcelApp = new Excel.ApplicationClass();
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
ExcelApp.Visible = false;
objBook = ExcelApp.Workbooks.Add(missing);
objBook.Sheets.Add();
Excel.Worksheet objSheet = (Excel.Worksheet)objBook.Sheets[1];
objSheet.Name = "Rapor";
#region Anadolu
#region baslık
//objSheet.get_Range("A1", "A2").Merge();
objSheet.Cells[1, 1] = "SIRA NO";
objSheet.get_Range("A1", "A1").Font.Bold = true;
objSheet.get_Range("A1", "A1").Font.Size = 11;
objSheet.get_Range("A1", "A1").ColumnWidth = 7.57;
objSheet.get_Range("A1", "A1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
objSheet.get_Range("A1", "A1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
//objSheet.get_Range("B1", "B2").Merge();
objSheet.Cells[1, 2] = "MÜŞTERİ NO";
objSheet.get_Range("B1", "B1").Font.Bold = true;
objSheet.get_Range("B1", "B1").Font.Size = 11;
objSheet.get_Range("B1", "B1").ColumnWidth = 11.57;
objSheet.get_Range("B1", "B1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
objSheet.get_Range("B1", "B1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
Dictionary<int, int> mypairs = new Dictionary<int, int>();
string[] productCols = Order.getUsedProductTypes(date);
int colRow = 3;
int colStartRow = 3;
for (int j = 0; j < productCols.Length; j++)
{
objSheet.Cells[1, colRow] = productCols[j];
(objSheet.Cells[1, colRow] as Excel.Range).Font.Bold = true;
(objSheet.Cells[1, colRow] as Excel.Range).Font.Size = 11;
(objSheet.Cells[1, colRow] as Excel.Range).ColumnWidth = 4.71;
(objSheet.Cells[1, colRow] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[1, colRow] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
mypairs.Add(colRow, 0);
colRow++;
}
objSheet.Cells[1, colRow] = "BÖLGE";
(objSheet.Cells[1, colRow] as Excel.Range).ColumnWidth = 15;
(objSheet.Cells[1, colRow] as Excel.Range).Font.Bold = true;
(objSheet.Cells[1, colRow] as Excel.Range).Font.Size = 11;
(objSheet.Cells[1, colRow] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[1, colRow] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
colRow++;
objSheet.Cells[1, colRow] = "ÖDEME ŞEKLİ";
(objSheet.Cells[1, colRow] as Excel.Range).ColumnWidth = 11.86;
(objSheet.Cells[1, colRow] as Excel.Range).Font.Size = 11;
(objSheet.Cells[1, colRow] as Excel.Range).Font.Bold = true;
(objSheet.Cells[1, colRow] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[1, colRow] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
colRow++;
objSheet.Cells[1, colRow] = "AÇIKLAMA";
//(objSheet.Cells[1, colRow] as Excel.Range).ColumnWidth = 21;
//(objSheet.Cells[1, colRow] as Excel.Range).RowHeight = 21;
(objSheet.Cells[1, colRow] as Excel.Range).Font.Bold = true;
(objSheet.Cells[1, colRow] as Excel.Range).Font.Size = 11;
(objSheet.Cells[1, colRow] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[1, colRow] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;
(objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;
colRow++;
#endregion
int row = 3;
List<Order> ords = Order.Orders_GetByDate(date);
for (int k = 0; k < ords.Count; k++)
{
objSheet.Cells[row, 1] = k + 1;
(objSheet.Cells[row, 1] as Excel.Range).Font.Bold = true;
(objSheet.Cells[row, 1] as Excel.Range).Font.Color = -16776961;
(objSheet.Cells[row, 1] as Excel.Range).Font.Size = 11;
(objSheet.Cells[row, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[row, 1] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
objSheet.Cells[row, 2] = Customer.Customer.Customer_GetByID(ords[k].CustomerID).PreFix + " " + Customer.Customer.Customer_GetByID(ords[k].CustomerID).No.ToString().PadLeft(3, '0');
//(objSheet.Cells[row, 2] as Excel.Range).ColumnWidth = 10;
(objSheet.Cells[row, 2] as Excel.Range).Font.Bold = true;
(objSheet.Cells[row, 2] as Excel.Range).Font.Color = -16776961;
(objSheet.Cells[row, 2] as Excel.Range).Font.Size = 11;
(objSheet.Cells[row, 2] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[row, 2] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
OrderDetail[] odtmp = ords[k].getDetails().ToArray();
int colIndex = 3;
for (int i = 0; i < odtmp.Length; i++)
{
for (int j = 0; j < productCols.Length; j++)
{
if (Product.Product.Products_GetByID(odtmp[i].ProductID).Name == productCols[j])
{
mypairs[colIndex + j] += odtmp[i].Unit;
objSheet.Cells[row, colIndex + j] = odtmp[i].Unit;
(objSheet.Cells[row, colIndex + j] as Excel.Range).Font.Bold = true;
(objSheet.Cells[row, colIndex + j] as Excel.Range).Font.Color = -16776961;
(objSheet.Cells[row, colIndex + j] as Excel.Range).Font.Size = 11;
}
(objSheet.Cells[row, colIndex + j] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[row, colIndex + j] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
}
}
Addresses[] addList = Addresses.Addresses_GetByCustomerID(ords[k].CustomerID).ToArray();
for (int i = 0; i < addList.Length; i++)
{
if (addList[i].Type == ords[k].AddressType)
{
objSheet.Cells[row, colIndex + productCols.Length] = addList[i].District;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Font.Bold = true;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Font.Color = -16776961;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Font.Size = 11;
}
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
}
objSheet.Cells[row, colIndex + productCols.Length + 1] = PayingTypes.PayingTypes_GetByID(ords[k].PayingType).PreFix;
(objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Font.Bold = true;
(objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Font.Color = -16776961;
(objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Font.Size = 11;
(objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
(objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
(objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
//objSheet.Cells[row , colIndex + productCols.Length + 2] = ords[k].Description;
//(objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Font.Bold = true;
//(objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Font.Color = -16776961;
//(objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Font.Size = 11;
(objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
(objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
row++;
}
Dictionary<int, int>.KeyCollection keys = mypairs.Keys;
foreach (int i in keys)
{
objSheet.Cells[3 + ords.Count, i] = mypairs[i];
}
#endregion
string fileNameXLS = date.Day.ToString("00") + "." + date.Month.ToString("00") + "." + date.Year + ".xls";
objBook.SaveAs(sfd.SelectedPath + "\\" + fileNameXLS, Excel.XlFileFormat.xlWorkbookNormal, null, null, true, false, Excel.XlSaveAsAccessMode.xlNoChange, false, false, null, null, null);
objBook.Close(false, Type.Missing, System.Type.Missing);
ExcelApp.Quit();
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("tr-TR");
return sfd.SelectedPath + "\\" + fileNameXLS;
}
return "";
}
private void btn_XCLOpen_Click(object sender, EventArgs e)
{
XCLFileDialog.ShowDialog();
if (XCLFileDialog.FileName != "")
{
Microsoft.Office.Interop.Excel.ApplicationClass appEXCEL = new Microsoft.Office.Interop.Excel.ApplicationClass();
// create the workbook object by opening the excel file.
//Workbook workBook = appEXCEL.Workbooks.Open(XCLFileDialog.FileName, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
this.workBook = appEXCEL.Workbooks.Open(XCLFileDialog.FileName, 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);
// Get The Active Worksheet Using Sheet Name Or Active Sheet
foreach (Worksheet mySheet in workBook.Sheets)
{
//Worksheet mySheet = (Worksheet)workBook.Sheets.get_Item(i);
comboBox2.Items.Add(mySheet.Name);
}
Worksheet workSheet = (Worksheet)workBook.ActiveSheet;
comboBox2.SelectedItem = workSheet.Name;
lbl_messages.Text = ConfigurationSettings.AppSettings["sheetText1"] + workSheet.Name + ConfigurationSettings.AppSettings["sheetText2"];
toolStripStatusLabel2.Text = ConfigurationSettings.AppSettings["changeSheetText"];
appEXCEL.Quit();
button1.Enabled = true;
button2.Enabled = false;
btCreateXMLFlux.Enabled = true;
button3.Enabled = true;
webBrowser1.DocumentText = ConfigurationSettings.AppSettings["excelReadyText"];
webBrowser2.DocumentText = ConfigurationSettings.AppSettings["excelReadyText"];
}
}
private void exportExcel()
{
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
WebTools.Alert("Excel无法启动");
return;
}
// 创建Excel工作薄
Dictionary<string, List<FpStudentObject>> schoolStudents = QueryStudentGroupbySchool(null,qDateStart.Value,qDateEnd.Value);
String[] sheetTitles={"缺受理号","未收费","已收费"};
String[] sheetColumns={"受理号","姓名","身份证号码","准驾车型","导入时间"};
String dir = MapPath(string.Format("~/temp/{0}/", Session.SessionID));
if (Directory.Exists(dir))
{
Directory.Delete(dir,true);
}
Directory.CreateDirectory(dir);
String pathPattern = dir+"{0}.xls";
foreach (string schoolCode in schoolStudents.Keys) {
Excel.Workbook xlBook = xlApp.Workbooks.Add(Missing.Value);
int[] rowIndexs = { 0, 0, 0 };
List<FpStudentObject> students = schoolStudents[schoolCode];
string schoolName=null;
xlBook.Sheets.Add(Missing.Value, Missing.Value, sheetTitles.Length-1,Missing.Value);
foreach(FpStudentObject student in students){
int sheetIndex = -1;
if (string.IsNullOrEmpty(student.LSH)) {
sheetIndex = 0;
}
else if (student.FEE_STATUE != "Y") {
sheetIndex = 1;
}
else if (student.FEE_STATUE == "Y") {
sheetIndex = 2;
}
if (sheetIndex >= 0) {
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[sheetIndex];
xlSheet.Name = string.Format("{0}({1})", sheetTitles[sheetIndex],rowIndexs[sheetIndex]+1);
int colIndex = 1;
int rowIndex = rowIndexs[sheetIndex]+1;
Excel.Range rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
rang.Select();
rang.ColumnWidth = 200;
rang.Formula = student.LSH;
rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
rang.Select();
rang.ColumnWidth = 120;
rang.Formula = student.NAME;
rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
rang.Select();
rang.ColumnWidth = 250;
rang.Formula = student.IDCARD;
rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
rang.Formula = student.CAR_TYPE;
rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++];
rang.Select();
rang.ColumnWidth = 120;
rang.Value2 = student.CREATE_TIME.ToLongDateString();
rowIndexs[sheetIndex]++;
}
schoolName=student.SCHOOL_NAME;
}
if(string.IsNullOrEmpty(schoolName))continue;
try
{
xlBook.Save();
xlBook.Saved = true;
String path = string.Format(pathPattern, schoolName);
if (File.Exists(path)) {
File.Delete(path);
}
xlBook.SaveCopyAs(path);
}
finally
{
xlBook.Close(true, Missing.Value, Missing.Value);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook.Worksheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
}
}
xlApp.Workbooks.Close();
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
String zipFileName=string.Format("指纹记录[{0}][{1}].zip",qDateStart.Value,qDateEnd.Value);
if (File.Exists(zipFileName)) {
File.Delete(zipFileName);
}
string zipPath = dir + zipFileName;
FileHelper.ZipDir(dir,zipPath);
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(zipFileName, Encoding.UTF8));
Response.WriteFile(zipPath);
Response.End();
Response.Close();
}
private void button4_Click(object sender, EventArgs e)
{
/*
Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook wb = xla.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet ws = (Excel.Worksheet)xla.ActiveSheet;
xla.Visible = true;*/
Excel.Application xlApp ;
Excel.Workbook xlWorkBook ;
Excel.Worksheet ws ;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
ws = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlApp.Visible = true;
//Sayfa ayarlari
ws.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
ws.get_Range("A1", "H8").Font.Bold = true;
ws.get_Range("A1", "H8").Font.Size=14;
//
//ResourceManager resManager = new ResourceManager("PersonalManagementSystem.Resource1",System.Reflection.Assembly.GetExecutingAssembly());
// resmi kopyalayip yapistiran kod
//System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(adminTab));
//Image image = ((System.Drawing.Image)(resources.GetObject("1")));
//System.Windows.Forms.Clipboard.SetDataObject(image, true);
//ws.Paste(ws.get_Range("A2", "A2"), image);
ws.Shapes.AddPicture(Application.StartupPath+"\\1.JPG", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 70, 70);
ws.Cells[3, 3] = "T.C. MALTEPE UNIVERSITY";
ws.Cells[4, 3] = FacultyComboBox.Text.ToUpper();
ws.Cells[7, 1] = "Personnel Information";
ws.Cells[9, 1] = "Type";
ws.Cells[10, 1] = "Title";
ws.Cells[11, 1] = "Name";
ws.Cells[12, 1] = "SurName";
ws.Cells[13, 1] = "TC No";
ws.Cells[14, 1] = "Gender";
ws.Cells[15, 1] = "Blood Type";
ws.Cells[16, 1] = "Home Phone";
ws.Cells[17, 1] = "Mobile Phone";
ws.Cells[18, 1] = "Address";
ws.Cells[19, 1] = "Institution Reg. No.";
ws.Cells[20, 1] = "Faculty";
ws.Cells[21, 1] = "Departmant";
ws.Cells[22, 1] = "Courses";
ws.Cells[23, 1] = "PMS User Type";
ws.Cells[10, 3] = ": " + TitleComboBox.Text.ToUpper();
ws.Cells[9, 3] = ": " + TypeComboBox.Text.ToUpper();
ws.Cells[11, 3] =": "+ NameTextBox.Text.ToUpper();
ws.Cells[12, 3] = ": " + SurnameTextBox.Text.ToUpper();
ws.Cells[13, 3] = ": " + TcNoTextBox.Text;
ws.Cells[14, 3] = ": " + ((maleRadio.Checked) ? "MALE" : "FEMALE");
ws.Cells[15, 3] = ": " + BloodComboBox.Text;
ws.Cells[16, 3] = ": " + homePhone.Text;
ws.Cells[17, 3] = ": " + mobilePhone.Text;
ws.Cells[18, 3] = ": " + address.Text.ToUpper();
ws.Cells[19, 3] = ": " + InsRegisterNo.Text;
ws.Cells[20, 3] = ": " + FacultyComboBox.Text.ToUpper();
ws.Cells[21, 3] = ": " + DeptComboBox.Text.ToUpper();
ws.Cells[22, 3] = ": " + CourseComboBox.Text.ToUpper();
ws.Cells[23, 3] = ": " + ((radioAdmin.Checked) ? "ADMIN" : "PERSONNEL");
xlWorkBook.Close(true,misValue,misValue);
xlApp.Quit();
ExcelKill();
}
