public static void ConvertExcel(string openPath, string savePath, ref string errText)
{
errText = string.Empty;
try
{
//将xml文件转换为标准的Excel格式
Object Nothing = Missing.Value; //由于yongCOM组件很多值需要用Missing.Value代替
Excel.Application ExclApp = new Excel.ApplicationClass(); // 初始化
Excel.Workbook ExclDoc = ExclApp.Workbooks.Open(openPath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing); //打开Excl工作薄
try
{
Object format = Excel.XlFileFormat.xlWorkbookNormal; //获取Excl 2007文件格式
ExclApp.DisplayAlerts = false;
ExclDoc.SaveAs(savePath, format, Nothing, Nothing, Nothing, Nothing, Excel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing); //保存为Excl 2007格式
}
catch (Exception ex)
{
errText = ex.Message;
}
finally
{
ExclDoc.Close(Nothing, Nothing, Nothing);
ExclApp.Quit();
}
}
catch (Exception ex)
{
errText = ex.Message;
}
}
static void Main(string[] args)
{
Excel.Application excel = new Excel.Application();
string originalPath = @"H:\ExcelTestFolder\Book1_Test.xls";
Excel.Workbook workbook = excel.Workbooks.Open(originalPath);
Excel.Worksheet worksheet = workbook.Worksheets["Sheet1"];
Excel.Range usedRange = worksheet.UsedRange;
// Start test for looping thru each excel worksheet
Stopwatch sw = new Stopwatch();
Console.WriteLine("Start stopwatch to loop thru WORKSHEET...");
sw.Start();
ConventionalRemoveEmptyRowsCols(worksheet);
sw.Stop();
Console.WriteLine("It took a total of: " + sw.Elapsed.Milliseconds + " Miliseconds to remove empty rows and columns...");
string newPath = @"H:\ExcelTestFolder\Book1_Test_RemovedLoopThruWorksheet.xls";
workbook.SaveAs(newPath, Excel.XlSaveAsAccessMode.xlNoChange);
workbook.Close();
Console.WriteLine("");
// Start test for looping thru object array
workbook = excel.Workbooks.Open(originalPath);
worksheet = workbook.Worksheets["Sheet1"];
usedRange = worksheet.UsedRange;
Console.WriteLine("Start stopwatch to loop thru object array...");
sw = new Stopwatch();
sw.Start();
DeleteEmptyRowsCols(worksheet);
sw.Stop();
// display results from second test
Console.WriteLine("It took a total of: " + sw.Elapsed.Milliseconds + " Miliseconds to remove empty rows and columns...");
string newPath2 = @"H:\ExcelTestFolder\Book1_Test_RemovedLoopThruArray.xls";
workbook.SaveAs(newPath2, Excel.XlSaveAsAccessMode.xlNoChange);
workbook.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
Console.WriteLine("");
Console.WriteLine("Finished testing methods - Press any key to exit");
Console.ReadKey();
}
public void SaveAs(string FileName)
{
m_objBook.SaveAs(FileName, miss, miss, miss, miss,
miss, Excel.XlSaveAsAccessMode.xlNoChange,
Excel.XlSaveConflictResolution.xlLocalSessionChanges,
miss, miss, miss, miss);
//m_objBook.Close(false, miss, miss);
}
protected override void Execute(CodeActivityContext context)
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook Nuovo = xlApp.Workbooks.Add();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(PathWorkbookInput.Get(context));
var nomeFile = NomeFile.Get(context);
var nomeSheet = NomeSheet.Get(context);
Nuovo.SaveAs(System.IO.Directory.GetCurrentDirectory() + "\\" + NomeFile + NomeSheet, Excel.XlFileFormat.xlOpenXMLWorkbook);
public static void NativeExcel_CreateExcel(
DataTable Dt
, ClsExcel_Columns Columns
, string SaveFileName = ""
, Excel.XlFileFormat FileFormat = Excel.XlFileFormat.xlExcel5
, string Title = "")
{
Excel.Application Obj_Excel = new Excel.Application();
Excel.Workbook owbook = Obj_Excel.Workbooks.Add();
Excel.Worksheet owsheet = owbook.Worksheets.Add();
Int32 RowCt = 2;
Int32 ColCt = 1;
foreach (ClsExcel_Columns.Str_Columns?Obj in Columns.pObj)
{
owsheet.Cells[RowCt, ColCt].Value = Obj.Value.FieldDesc;
owsheet.Cells[RowCt, ColCt].Font.Bold = true;
Excel.Range Inner_ExRange =
owsheet.Range[
Do_Methods.GenerateChr(ColCt)
+ RowCt.ToString()
+ ":"
+ Do_Methods.GenerateChr(ColCt)
+ (RowCt + Dt.Rows.Count).ToString()];
Inner_ExRange.NumberFormat = Obj.Value.NumberFormat;
ColCt++;
}
RowCt++;
ColCt = 1;
Excel.Range ExRange =
owsheet.Range[
Do_Methods.GenerateChr(ColCt)
+ RowCt.ToString()
+ ":"
+ Do_Methods.GenerateChr(ColCt + Columns.pObj.Count)
+ (RowCt + Dt.Rows.Count - 1).ToString()];
ExRange.Value = Do_Methods.ConvertDataTo2DimArray(Dt, Columns.pFieldName);
owsheet.Range["A1;IV65536"].AutoFit();
if (SaveFileName == "")
{
SaveFileName = "Excel_File";
}
owsheet.Range["A1:A1"].Select();
owbook.SaveAs(SaveFileName, FileFormat);
}
/// <summary>
/// New excel file from the giving path
/// </summary>
/// <param name="strPath">strPath</param>
public void create(string strPath)
{
if(File.Exists(strPath))
{
File.Delete(strPath);
}
object Nothing = Missing.Value;
object format = Excel.XlFileFormat.xlWorkbookNormal;
xBook = excelMain.Workbooks.Add(Nothing);
xSheet = (Excel.Worksheet)xBook.Sheets[1];
//MessageBox.Show(strPath);
xBook.SaveAs(strPath,Nothing,Nothing,Nothing,Nothing,Nothing,
Excel.XlSaveAsAccessMode.xlExclusive,Nothing,Nothing,Nothing,Nothing);
}
//**********************************************************************
/// <summary>
/// New excel file from the giving path
/// </summary>
public void create(string strPath)
{
if (File.Exists(strPath))
{
File.Delete(strPath);
}
object Nothing = Missing.Value;
object format = Excel.XlFileFormat.xlWorkbookNormal;
xBook = excelMain.Workbooks.Add(Nothing);
xSheet = (Excel.Worksheet)xBook.Sheets[1];
//MessageBox.Show(strPath);
xBook.SaveAs(strPath, Nothing, Nothing, Nothing, Nothing, Nothing,
Excel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing);
}
private string ExportDataSetToExcel(DataSet ds, string location)
{
//Creae an Excel application instance
Excel.Application excelApp = new Excel.Application();
//string date = DateTime.Now.ToString();
string loc = location;
//Path.GetFullPath(loc);
// string path = date.Trim();
int randomnnumber = rnd.Next(1, 1000);
string date = DateTime.Now.ToString("yyyyMMddHHmmss");
string myPath = @loc + randomnnumber + date + ".xls";
//Create an Excel workbook instance and open it from the predefined location
// Excel.Workbook excel1 = excelApp.Workbooks.Add(myPath);
Excel.Workbook excelWorkBook = excelApp.Workbooks.Add();
foreach (DataTable table in ds.Tables)
{
//Add a new worksheet to workbook with the Datatable name
Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
excelWorkSheet.Name = table.TableName;
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
}
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
}
}
}
try
{
excelWorkBook.SaveAs(myPath);
excelWorkBook.Close();
excelApp.Quit();
}
catch (Exception e)
{
return("0");
}
return("1");
}
public void closeExcel()
{
try
{
myExcelWorkbook.SaveAs(excelFilePath, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value); // Save data in excel
myExcelWorkbook.Close(true, excelFilePath, System.Reflection.Missing.Value); // close the worksheet
}
finally
{
if (myExcelApplication != null)
{
myExcelApplication.Quit(); // close the excel application
}
}
}
static void Main(string[] args)
{
Excel.Application excel = new Excel.Application();
string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
string originalPath = desktopPath + @"\ExcelRemove\Book1_Test.xls";
Excel.Workbook workbook = excel.Workbooks.Open(originalPath);
Excel.Worksheet worksheet = workbook.Worksheets["Sheet1"];
DeleteEmptyRowsCols(worksheet);
string newPath = desktopPath + @"\ExcelRemove\Book1_Test_Removed.xls";
workbook.SaveAs(newPath, Excel.XlSaveAsAccessMode.xlNoChange);
workbook.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
Console.WriteLine("Finished removing empty rows and columns - Press any key to exit");
Console.ReadKey();
}
public void ExcelClose(string szPath, Excel.Application excel, Excel.Workbook wb)
{
Process[] localByNameApp = Process.GetProcessesByName(szPath);//获取程序名的所有进程
if (localByNameApp.Length > 0)
{
foreach (var app in localByNameApp)
{
// if (!app.HasExited)
// {
#region
////设置禁止弹出保存和覆盖的询问提示框
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
excel.Visible = false;
//wb.Saved = true;
////保存工作簿
excel.Application.Workbooks.Add(true).Save();
//保存excel文件
///excel.Save("E:\\c#_test\\winFormTest\\winFormTest\\hahaha.xls");
//确保Excel进程关闭
excel.Quit();
excel = null;
#endregion
app.Kill();//关闭进程
/*}*/
}
}
if (wb != null)
{
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
excel.Visible = false;
excel.Application.Workbooks.Add(true).Save();
//保存结果
wb.SaveAs(szPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
//wb.Save();
wb.Close(false, szPath, szPath);
}
excel.Quit();
// 安全回收进程
System.GC.GetGeneration(excel);
}
// 创建空白工作薄
public bool Creat(string file_name)
{
if (!file_name.Contains(".xlsx") && !file_name.Contains(".xls"))
return false;
if (ExcelApp == null)
return false;
try
{
Book = ExcelApp.Workbooks.Add(Type.Missing);
Excel.XlFileFormat file_format = file_name.Contains(".xlsx") ? Excel.XlFileFormat.xlOpenXMLWorkbook : Excel.XlFileFormat.xlXMLSpreadsheet;
Book.SaveAs(file_name, FileFormat: file_format, AccessMode: Excel.XlSaveAsAccessMode.xlNoChange);
return true;
}
catch (System.Exception)
{
return false;
}
}
static void Main(string[] args)
{
Excel.Application excel = new Excel.Application();
string originalPath = @"H:\ExcelTestFolder\Book1_Test.xls";
Excel.Workbook workbook = excel.Workbooks.Open(originalPath);
Excel.Worksheet worksheet = workbook.Worksheets["Sheet1"];
Excel.Range usedRange = worksheet.UsedRange;
RemoveEmptyTopRowsAndLeftCols(worksheet, usedRange);
DeleteEmptyRowsCols(worksheet);
string newPath = @"H:\ExcelTestFolder\Book1_Test_Removed.xls";
workbook.SaveAs(newPath, Excel.XlSaveAsAccessMode.xlNoChange);
workbook.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
Console.WriteLine("Finished removing empty rows and columns - Press any key to exit");
Console.ReadKey();
}
private void ExportDataSetToExcel(DataSet ds)
{
//Creae an Excel application instance
Excel.Application excelApp = new Excel.Application();
//string date = DateTime.Now.ToString();
string date = DateTime.Now.ToString("yyyy-mm-dd");
string loc = ConfigurationManager.AppSettings["location"];
string path = date.Trim();
string myPath = @loc + date + ".xls";
//Create an Excel workbook instance and open it from the predefined location
// Excel.Workbook excel1 = excelApp.Workbooks.Add(myPath);
Excel.Workbook excelWorkBook = excelApp.Workbooks.Add();
foreach (DataTable table in ds.Tables)
{
//Add a new worksheet to workbook with the Datatable name
Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
excelWorkSheet.Name = table.TableName;
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
}
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
}
}
}
excelWorkBook.SaveAs(myPath);
excelWorkBook.Close();
excelApp.Quit();
}
public Tyburn1()
{
Excel.Application oXL = new Excel.Application();
oXL.Visible = false;
Excel.Workbook oWB = oXL.Workbooks.Add(missing);
Excel.Worksheet oSheet = oWB.ActiveSheet as Excel.Worksheet;
oSheet.Name = "The first sheet";
oSheet.Cells[1, 1] = "Something";
Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing)
as Excel.Worksheet;
oSheet2.Name = "The second sheet";
oSheet2.Cells[1, 1] = "Something completely different";
string fileName = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)
+ "\\SoSample.xlsx";
oWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,
missing, missing, missing, missing,
Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
oWB.Close(missing, missing, missing);
oXL.UserControl = true;
oXL.Quit();
}
public static bool didExport(DataTable dt, string strSheetName, string filename, string Address)
{
try
{
Excel.Application excel = new Excel.Application();
Excel.Workbook bookDest = (Excel.Workbook)excel.Workbooks.Add(Missing.Value);
Excel.Worksheet sheetDest = (Excel.Worksheet)bookDest.Worksheets[1];
int rowIndex = 1;
int colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
sheetDest.Cells[1, colIndex] = col.ColumnName;
}
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
sheetDest.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
bookDest.Saved = true;
bookDest.SaveAs(Address + filename + ".xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//方式一
excel.Quit();
excel = null;
GC.Collect();
return(true);
}
catch (Exception)
{
return(false);
}
}
private void CompareButton_MouseUp(object sender, MouseButtonEventArgs e)
{
CompareButton.Margin = new Thickness(0);
if (file1.Text == "" || file2.Text == "" || NewFileName.Text == "")
{
errorMessage.Content = "Kindly Fill All the Feilds...";
AnimateErrorGrid();
return;
}
if (row == 0)
{
errorMessage.Content = "Kindly Select a File Type...";
AnimateErrorGrid();
return;
}
// creating folder on desktop
if (!Directory.Exists(Desktop_path + "\\Bhaiya"))
{
Directory.CreateDirectory(Desktop_path + "\\Bhaiya");
}
string str = Desktop_path + "\\Bhaiya\\" + NewFileName.Text + ".xls";
if (File.Exists(str))
{
errorMessage.Content = "File name already exist";
AnimateErrorGrid();
return;
}
//Opening first File
workbook1 = excelApp1.Workbooks.Open(file1_path);
worksheet1 = (Excel.Worksheet)workbook1.Sheets[1];
range1 = worksheet1.UsedRange;
//openinng Second File
workbook2 = excelApp2.Workbooks.Open(file2_path);
worksheet2 = (Excel.Worksheet)workbook2.Sheets[1];
range2 = worksheet2.UsedRange;
// Creating New File
newapp = new Excel.Application();
newapp.Visible = true;
new_workbook = newapp.Workbooks.Add(1);
new_worksheet = (Excel.Worksheet)new_workbook.Sheets[1];
new_workSheet_range = worksheet2.UsedRange;
object misValue = System.Reflection.Missing.Value;
TotalColumn = range2.Columns.Count;
// saving the excel file in Bhaiya folder
new_workbook.SaveAs(Desktop_path + "\\Bhaiya\\" + NewFileName.Text, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
DoHashingofSecondFile();
WriteDatainResultFile();
RearrangeNewSheet();
AddDuplicates();
CloseAllSheet();
}
public void ExcelOut(List <MatchedData> lst, string sFileName)
{
System.Reflection.Missing miss = System.Reflection.Missing.Value;
MatchedData matchecdData = new MatchedData();
Excel.Application excelApplication = new Excel.Application();
Excel.Workbook excelWorkbook = excelApplication.Workbooks.Add();
excelApplication.UserControl = true;
excelApplication.Application.DisplayAlerts = false;
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets.Add(miss, miss, miss, miss);
excelWorksheet.Name = "Protein";
int iRow = 0;
try
{
for (int i = excelWorkbook.Sheets.Count; i >= 1; i--)
{
Excel.Worksheet excelWorksheetTemp = (Excel.Worksheet)excelWorkbook.Sheets[i];
if (excelWorksheetTemp.Name != "Protein")
{
excelWorksheetTemp.Delete();
}
}
//Header
iRow = 1;
excelWorksheet.Cells[iRow, 1] = "Index";
excelWorksheet.Cells[iRow, 2] = "ID";
excelWorksheet.Cells[iRow, 3] = "MOD_RES";
excelWorksheet.Cells[iRow, 4] = "PTM_Score";
excelWorksheet.Cells[iRow, 5] = "TotalPTM_Score";
excelWorksheet.Cells[iRow, 6] = "Z";
excelWorksheet.Cells[iRow, 7] = "DatasetFile";
excelWorksheet.Cells[iRow, 8] = "Database";
excelWorksheet.Cells[iRow, 9] = "Repository";
excelWorksheet.Cells[iRow, 10] = "ScreeningApproach";
excelWorksheet.Cells[iRow, 11] = "ExpMZ";
excelWorksheet.Cells[iRow, 12] = "TheoMZ";
excelWorksheet.Cells[iRow, 13] = "Error_PPM";
excelWorksheet.Cells[iRow, 14] = "AccessionNumber";
excelWorksheet.Cells[iRow, 15] = "ProteinName";
excelWorksheet.Cells[iRow, 16] = "Length";
excelWorksheet.Cells[iRow, 17] = "Function";
excelWorksheet.Cells[iRow, 18] = "Sequence";
excelWorksheet.Cells[iRow, 19] = "NMFs";
excelWorksheet.Cells[iRow, 20] = "Actual_PMFs";
//Value
iRow = 2;
for (int i = 0; i < lst.Count; i++)
{
matchecdData = lst[i];
excelWorksheet.Cells[iRow, 1] = matchecdData.Index;
excelWorksheet.Cells[iRow, 2] = matchecdData.ID;
excelWorksheet.Cells[iRow, 3] = matchecdData.MOD_RES.Replace(";", "").Replace(",", "");
excelWorksheet.Cells[iRow, 4] = matchecdData.DataBase_PTM_SCORE;
excelWorksheet.Cells[iRow, 5] = matchecdData.Total_PTM_Score;
excelWorksheet.Cells[iRow, 6] = matchecdData.Z;
excelWorksheet.Cells[iRow, 7] = matchecdData.DatasetFile;
excelWorksheet.Cells[iRow, 8] = matchecdData.Database;
excelWorksheet.Cells[iRow, 9] = matchecdData.Repository;
excelWorksheet.Cells[iRow, 10] = matchecdData.ScreeningApproach;
excelWorksheet.Cells[iRow, 11] = matchecdData.ExpMZ;
excelWorksheet.Cells[iRow, 12] = matchecdData.TheoMZ;
excelWorksheet.Cells[iRow, 13] = matchecdData.Error_PPM;
excelWorksheet.Cells[iRow, 14] = matchecdData.AccessionNumber;
excelWorksheet.Cells[iRow, 15] = matchecdData.ProteinName;
excelWorksheet.Cells[iRow, 16] = matchecdData.Length;
excelWorksheet.Cells[iRow, 17] = matchecdData.Function;
excelWorksheet.Cells[iRow, 18] = matchecdData.Sequence;
excelWorksheet.Cells[iRow, 19] = matchecdData.NMFS;
excelWorksheet.Cells[iRow, 20] = matchecdData.Actualpmfs;
iRow++;
}
excelWorkbook.SaveAs(sFileName, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
excelWorkbook.Close(false, miss, miss);
}
catch (Exception ex)
{
throw ex;
}
finally
{
matchecdData = null;
if (excelApplication != null)
{
excelApplication.Workbooks.Close();
excelApplication.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication);
System.GC.Collect();
}
if (excelApplication != null)
{
IntPtr t = new IntPtr(excelApplication.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
excelApplication = null;
GC.Collect();
}
}
/// <summary>
/// 将 DataTable 导出到 Excel
/// </summary>
/// <param name="fOptions"></param>
public static void ExportToExcel(List <DataTableExportOptions> fOptions, String fFileName)
{
if (fOptions == null || fOptions.Count == 0)
{
return;
}
#region 判定指定的文件路径下是否存在相同的文件名，如果存在就删除之前的文件
try
{
if (File.Exists(fFileName))
{
File.Delete(fFileName);
}
}
catch
{
return;
}
#endregion
Excel.Application application = new Excel.Application();
application.Visible = false;
application.UserControl = false;
Excel.Workbook workBook = (Excel.Workbook)(application.Workbooks.Add(missing));
try
{
#region " 根据需要导出的 DataTable 数量，预先增加不足的工作表或多余的工作表 "
// 添除多余的工作表
while (application.ActiveWorkbook.Sheets.Count > fOptions.Count)
{
((Excel.Worksheet)application.ActiveWorkbook.Sheets[1]).Delete();
}
// 添加工作表
while (application.ActiveWorkbook.Sheets.Count < fOptions.Count)
{
application.Worksheets.Add(missing, missing, missing, missing);
}
#endregion
int sheetIndex = 1;
List <String> sheetNames = new List <string>();
foreach (DataTableExportOptions option in fOptions)
{
#region " 处理在多个 DataTable 设置为相同的工作表名称的问题 "
if (sheetNames.Contains(option.WorkSheetName))
{
int i = 1;
while (true)
{
string newSheetName = option.WorkSheetName + i.ToString();
if (!sheetNames.Contains(newSheetName))
{
sheetNames.Add(newSheetName);
option.WorkSheetName = newSheetName;
break;
}
i++;
}
}
else
{
sheetNames.Add(option.WorkSheetName);
}
#endregion
ExportToExcel(application, workBook, (Excel.Worksheet)application.ActiveWorkbook.Sheets[sheetIndex], option);
sheetIndex++;
}
workBook.SaveAs(fFileName, missing, missing, missing, missing, missing
, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
}
finally
{
application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
GC.Collect();
}
}
private void bgW_luuexel_DoWork(object sender, DoWorkEventArgs e)
{
string filename = e.Argument.ToString();
System.Globalization.CultureInfo oldcul = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Application exapp = new Excel.Application();
ThamsoKetqua kq = new ThamsoKetqua();
bgW_luuexel.ReportProgress(0);
try
{
exapp.Workbooks.Add(Missing.Value);
Excel.Workbook wkb = exapp.Workbooks.get_Item(1);
//wkb. = Excel.XlFileFormat.xlExcel9795;
Excel.Worksheet wks = (Excel.Worksheet)wkb.Worksheets.get_Item(1);
int offsetrow = 3;
//int offsetcol = 1;
int colnum = 2;
((Excel.Range)wks.Cells[offsetrow - 1, 1]).Value2 = "Stt";
string colname;
foreach (DataColumn dc in datasetDanhsachtontamung1.CHITIETTAMUNG.Columns)
{
if (dc.ColumnName == "maql" || dc.ColumnName == "mavaovien")
{
continue;
}
switch (dc.ColumnName)
{
case "mabn":
colname = "Mã BN";
break;
case "hoten":
colname = "Họ Tên";
break;
case "quyenso":
colname = "Quyển sổ";
break;
case "sobienlai":
colname = "Số biên lai";
break;
case "tiendong":
colname = "Tạm ứng";
break;
case "ngaydong":
colname = "Ngày đóng";
break;
case "hoantra":
colname = "Hoàn trả";
break;
case "ngaytra":
colname = "Ngày trả";
break;
case "done":
colname = "Hoàn tất";
break;
case "tongvienphi":
colname = "Viện phí";
break;
case "ngayravien":
colname = "Ngày ra viện";
break;
case "nguoithu":
colname = "Người thu";
break;
case "tenkp":
colname = "Khoa";
break;
default:
colname = dc.ColumnName;
break;
}
((Excel.Range)wks.Cells[offsetrow - 1, colnum]).Value2 = colname;
colnum++;
}
long numrow = 1;
foreach (DataRow dr in datasetDanhsachtontamung1.CHITIETTAMUNG.Rows)
{
colnum = 2;
foreach (DataColumn dc in datasetDanhsachtontamung1.CHITIETTAMUNG.Columns)
{
numrow = datasetDanhsachtontamung1.CHITIETTAMUNG.Rows.IndexOf(dr) + offsetrow;
((Excel.Range)wks.Cells[numrow, 1]).Value2 = numrow - offsetrow + 1;
if (dc.ColumnName == "maql" || dc.ColumnName == "mavaovien")
{
continue;
}
if (dr[dc].GetType() == typeof(DateTime))
{
((Excel.Range)wks.Cells[numrow, colnum]).Value2 = ((DateTime)dr[dc]).ToString("dd/MM/yyyy");
}
else
if (dr[dc].GetType() == typeof(decimal) && ((decimal)dr[dc]) > 1000000000000)
{
((Excel.Range)wks.Cells[numrow, colnum]).Value2 = "'" + dr[dc].ToString();
}
else
{
if (dc.ColumnName == "done")
{
if ((decimal)dr[dc] == 0)
{
((Excel.Range)wks.Cells[numrow, colnum]).Value2 = "NO";
}
else
{
((Excel.Range)wks.Cells[numrow, colnum]).Value2 = "YES";
}
}
else
{
((Excel.Range)wks.Cells[numrow, colnum]).Value2 = dr[dc];
}
}
colnum++;
}
int per = (int)((float)(numrow - offsetrow) / (float)datasetDanhsachtontamung1.CHITIETTAMUNG.Rows.Count * 90);
if (per % 5 == 0)
{
bgW_luuexel.ReportProgress(per);
}
}
((Excel.Range)wks.Cells[numrow + 2, 1]).Value2 = "Tổng tạm ứng:"; ((Excel.Range)wks.Cells[numrow + 2, 2]).Value2 = lb_tongcong.Text;
((Excel.Range)wks.Cells[numrow + 2, 4]).Value2 = "Tổng tồn:"; ((Excel.Range)wks.Cells[numrow + 2, 5]).Value2 = lb_tongtu.Text;
((Excel.Range)wks.Cells[numrow + 2, 7]).Value2 = "Tổng hoàn:"; ((Excel.Range)wks.Cells[numrow + 2, 8]).Value2 = lb_tonghoan.Text;
wkb.SaveAs(filename.Substring(0, filename.Length - 4), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
bgW_luuexel.ReportProgress(100);
exapp.Quit();
kq.err = true;
kq.filename = wkb.Path;
}
catch
{
kq.err = false;
}
finally
{
//if(exapp)
exapp.Quit();
Thread.CurrentThread.CurrentCulture = oldcul;
e.Result = kq;
}
}