protected void BusiIncomeExportToExcel(string tabname, string tabdate, DataTable dtIncome)
{
try
{
Excel.Application xapp = new Excel.ApplicationClass();
Excel.Workbook xbook = xapp.Workbooks.Open(Application.StartupPath + @"\BusiIncomeModel.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Worksheet xSheet = (Excel.Worksheet)xbook.Sheets["业务量"]; //得到Sheet
xSheet.get_Range("A1", Missing.Value).Value2 = tabname;
xSheet.get_Range("A2", Missing.Value).Value2 = tabdate;
for (int i = 1; i < dtIncome.Rows.Count - 2; i++)
{
for (int j = 1; j < 8; j++)
{
xSheet.Cells[i + 3, j + 1] = dtIncome.Rows[i][j].ToString();
}
}
for (int i = 1; i < 8; i++)
{
xSheet.Cells[21, i + 1] = dtIncome.Rows[18][i].ToString();
}
SaveFileDialog SaveFileDialog1 = new SaveFileDialog();
SaveFileDialog1.Filter = "Excel文件（*.xls）|*.xls";
SaveFileDialog1.FileName = SysInitial.CP + "业务量报表" + DateTime.Now.ToShortDateString() + ".xls";
if (SaveFileDialog1.ShowDialog() == DialogResult.OK)
{
xbook.SaveCopyAs(SaveFileDialog1.FileName); //另存
xbook.Close(false, Application.StartupPath + @"\BusiIncomeModel.xls", Missing.Value); //关闭
xSheet = null;
xbook = null;
xapp.Quit();
xapp = null;
}
else
{
xbook.Close(false, Missing.Value, Missing.Value); //关闭
xSheet = null;
xbook = null;
xapp.Quit();
xapp = null;
}
}
catch (Exception err)
{
MessageBox.Show("导出时出错，请重试！", "系统提示", MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
clog.WriteLine(err);
}
finally
{
}
}
private void CloseAllSheet()
{
try
{
workbook1.Close(true, Missing.Value, Missing.Value);
excelApp1.Quit();
workbook2.Close(true, Missing.Value, Missing.Value);
excelApp2.Quit();
releaseObject(excelApp1);
releaseObject(excelApp2);
releaseObject(workbook1);
releaseObject(workbook2);
new_workbook.Save();
new_workbook.Close(true, Missing.Value, Missing.Value);
newapp.Quit();
releaseObject(newapp);
releaseObject(new_workbook);
Application.Current.Shutdown();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
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;
}
}
public static DataTable DataTableFromXlsx(string Path, string password = "")
{
Excel.Application objXL = null;
Excel.Workbook objWB = null;
objXL = new Excel.Application();
objWB = objXL.Workbooks.Open(Path);
Excel.Worksheet objSHT = objWB.Worksheets[1];
int rows = objSHT.UsedRange.Rows.Count;
int cols = objSHT.UsedRange.Columns.Count;
DataTable dt = new DataTable();
int noofrow = 1;
for (int c = 1; c <= cols; c++)
{
string colname = objSHT.Cells[1, c].Text;
dt.Columns.Add(colname);
noofrow = 2;
}
for (int r = noofrow; r <= rows; r++)
{
DataRow dr = dt.NewRow();
for (int c = 1; c <= cols; c++)
{
dr[c - 1] = objSHT.Cells[r, c].Text;
}
dt.Rows.Add(dr);
}
objWB.Close();
objXL.Quit();
return(dt);
}
public void GenerateReport(IEnumerable <StudentDto> students)
{
Excel.Application app = new Excel.Application();
app.DisplayAlerts = false;
Excel.Workbook book_template = app.Workbooks.Open(@"шаблон_отчета.xlsx");
Excel._Worksheet sheet_template = book_template.Sheets["отчет"];
foreach (var ob in students)
{
//1. Создаем объкт LearnerReport из БД
LearnerReport report = new LearnerReport
{
SNS = $"{ob.surname} {ob.name} {ob.SecondName}",
SchoolName = ob.SchoolName,
ClassName = ob.ClassName,
TestResult5 = ob.TestResult5
};
//2. Экспорт объкта LearnerReport в шаблон xlsx
sheet_template.Range["C4"].Value2 = report.SNS;
sheet_template.Range["C5"].Value2 = report.SchoolName;
sheet_template.Range["C6"].Value2 = report.ClassName;
sheet_template.Range["C9"].Value2 = report.TestResult5;
//3. Сохраняем полученный файл в .pdf на рабочем столе
string file_name = $@"{Environment.GetFolderPath(Environment.SpecialFolder.Desktop)}\{report.SNS}.pdf";
sheet_template.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, file_name);
}
book_template.Close(0);
book_template = null;
app.Quit();
app = null;
}
public void checkin(Excel.Workbook activeWorkbook)
{
try
{
if (MessageBox.Show(resources.GetString("sure_check_in"), resources.GetString("checkin"), MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation) == DialogResult.OK)
{
object saveChanges = true;
object missing = Type.Missing;
String localFileName = activeWorkbook.FullName;
activeWorkbook.Close(saveChanges, missing, missing); // Always we save document
docXML.refresh(); // Refresh document list
if (docXML.isOpenKMDocument(localFileName))
{
OKMDocument oKMDocument = docXML.getOpenKMDocument(localFileName);
docXML.remove(oKMDocument);
DocumentLogic.checkin(oKMDocument, configXML.getHost(), configXML.getUser(), configXML.getPassword());
if (File.Exists(localFileName))
{
File.Delete(localFileName);
}
}
}
}
catch (Exception e)
{
String errorMsg = "OpenKMExcelAddIn - (checkinButton_Click)\n" + e.Message + "\n\n" + e.StackTrace;
MessageBox.Show(errorMsg, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
//把Excel文档转换为Pdf文档
#region
private static void ExcelToPdf(string ExcelFileName, string PdfFileName)
{
//Object missing = System.Reflection.Missing.Value;
//Object ReadOnly = (object)true;
//ET.Application app = new ET.Application();
//app.Visible = false;
//ET.workbook doc = (ET.workbook)app.Workbooks.Open(ExcelFileName, missing, ReadOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//doc.ExportPdf(PdfFileName, "", "");
//doc.Close(missing, missing, missing);
//app.Quit();
//System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
//app = null;
Object missing = System.Reflection.Missing.Value;
Object ReadOnly = (object)true;
Excel.Application app = new Excel.Application();
app.Visible = false;
Excel.Workbook doc = (Excel.Workbook)app.Workbooks.Open(ExcelFileName, missing, ReadOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
doc.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, PdfFileName);
doc.Close(missing, missing, missing);
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
public void ReadSample()
{
Excel.Application excelApp = new Excel.Application();
if (excelApp != null)
{
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(@"C:\test.xls", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[1];
Excel.Range excelRange = excelWorksheet.UsedRange;
int rowCount = excelRange.Rows.Count;
int colCount = excelRange.Columns.Count;
for (int i = 1; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
Excel.Range range = (excelWorksheet.Cells[i, 1] as Excel.Range);
string cellValue = range.Value.ToString();
//do anything
}
}
excelWorkbook.Close();
excelApp.Quit();
}
}
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 static void PrintPreview(string fileName, PageSetup p)
{
Excel.Application excelApp = null;
Excel.Workbook workBook = null;
try
{
object objOpt = System.Reflection.Missing.Value;
excelApp = ExcelInit();
workBook = excelApp.Workbooks.Open(fileName, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt,
objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt);
if (p != null)
{
//if (p.TopMargin != 0 || p.LeftMargin != 0 || p.RightMargin != 0 || p.BottomMargin != 0)
{
int sheetCount = workBook.Sheets.Count;
Excel.Worksheet workSheet = null;
int i = 0;
while (i <= sheetCount)
{
try
{
workSheet = workBook.Sheets[i];
break;
}
catch
{
i++;
}
}
workSheet.PageSetup.LeftMargin = p.GetLeftMargin();
workSheet.PageSetup.RightMargin = p.GetRightMargin();
workSheet.PageSetup.TopMargin = p.GetTopMargin();
workSheet.PageSetup.BottomMargin = p.GetBottomMargin();
//workSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
}
}
excelApp.Visible = true;
workBook.PrintPreview(objOpt);
excelApp.Visible = false;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (workBook != null)
{
workBook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
ExcelTerminal(excelApp);
}
}
public static void getExcelFile()
{
//Create COM Objects. Create a COM object for everything that is referenced
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\E56626\Desktop\Teddy\VS2012\Sandbox\sandbox_test - Copy - Copy.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
//IMPORTANT SECTION
var dictionary = new Dictionary <string, List <string> >();
//iterate over the rows and columns as it appears in the file
//excel is not zero based!!
for (int i = 1; i <= rowCount; i++)
{
//it would be nice if we add some null checking to this variables. Check the article again
var col1 = xlRange.Cells[i, 1].Value2.ToString();
var col2 = xlRange.Cells[i, 2].Value2.ToString();
if (dictionary.ContainsKey(col1))
{
var existingList = dictionary[col1];
existingList.Add(col2);
}
else
{
var newList = new List <string>();
newList.Add(col2);
dictionary.Add(col1, newList);
}
}
//Do whatever you'd like with the dictionary
//END OF IMPORTANT SECTION
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
private void ReadExcelFile()
{
MyBook = MyApp.Workbooks.Open(Environment.CurrentDirectory + "\\" + name + ".csv");
MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here
int lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
int close = 7, date = 1;
int offset = DAYS_OFFSET - 2;
System.Array HistoricalData = (System.Array)MySheet.get_Range("A2", "G" + lastRow.ToString()).Cells.Value;
/*Console.WriteLine("contenu du fichier : " + MyBook.Name + " : ");
* for (int i = 1; i <= HistoricalData.Length; i++)
* {
* Console.Write(HistoricalData.GetValue(i, 1));
* Console.Write(" : ");
* Console.WriteLine(HistoricalData.GetValue(i, 7));
* }*/
DateTime theoriticalDate = datesAndValuation.ElementAt(0).Key, currentDate;
datesAndValuation.Remove(theoriticalDate);
double value = 0, Pi = 0, PiMinusOne = 100;
// List<double> realValues = new List<double>();
int i = lastRow - 1;
do
{
currentDate = DateTime.Parse(HistoricalData.GetValue(i, date).ToString());
if (i == lastRow - 1)
{
theoriticalDate = currentDate;
}
while (theoriticalDate < currentDate)
{
i++;
currentDate = DateTime.Parse(HistoricalData.GetValue(i, date).ToString());
}
// value= double.Parse(HistoricalData.GetValue(i, close).ToString());
Pi = (double.Parse(HistoricalData.GetValue(i, close).ToString()));
PiMinusOne = (i == lastRow - 1 ? 100 : realValues.ElementAt(realValues.Count() - 1));
realValues.Add(double.Parse(HistoricalData.GetValue(i, close).ToString()));
value = (i == lastRow - 1) ? (100) : (100 * Pi / PiMinusOne);
// USING THE THEORITICAL DATE
//datesAndValuation.Add(theoriticalDate, value);
// USING THE REAL DATE
datesAndValuation.Add(currentDate, value);
i -= offset;
theoriticalDate = theoriticalDate.AddDays(DAYS_OFFSET);
}while (i >= 1);
MyBook.Close();
Console.WriteLine(name + " " + datesAndValuation.Count() + "pair of values : ");
}
/// <summary>
/// 关闭Excel内存进程
/// </summary>
/// <param name="xlsApp">应用程序</param>
/// <param name="xlsWorkbook">工作簿</param>
/// <param name="xlsSheet">工作表</param>
public static void CloseXls(Excel.Application xlsApp, Excel.Workbook xlsWorkbook, Excel.Worksheet xlsSheet)
{
xlsWorkbook.Close();
xlsApp.Workbooks.Close();
xlsApp.Quit();
//关闭EXCEL进程
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheet);
xlsWorkbook = null;
xlsApp = null;
}
/// <summary>
/// 导出数据
/// </summary>
/// <param name="selectstring">选择导出数据的SQL选择语句</param>
public static void DataOut(SaveFileDialog dialog, string selectstring)
{
if (dialog.ShowDialog() == DialogResult.OK)
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
Excel.Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
worksheet.Cells.NumberFormatLocal = "@";
///////////////////////////////////
SqlConnection connect = InitConnect.GetConnection();
SqlDataReader read = null;
try
{
connect.Open();
SqlCommand cmd = new SqlCommand(selectstring, connect);
read = cmd.ExecuteReader();
for (int i = 0; i < read.FieldCount; i++)
{
worksheet.Cells[1, i + 1] = read.GetName(i).Trim();
}
int row = 2;
int count = 0;
while (read.Read())
{
count++;
for (int i = 0; i < read.FieldCount; i++)
{
worksheet.Cells[row, i + 1] = read[i].ToString().Trim();
}
row++;
}
MessageBox.Show("成功导出" + count.ToString() + "条记录！", "恭喜", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
catch (Exception ee)
{
MessageBox.Show("错误：" + ee.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
}
finally
{
read.Close();
connect.Close();
object change = false, filename = dialog.FileName;
workbook.SaveCopyAs(filename);
workbook.Close(change, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
excel.Quit();
}
}
}
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 static StringDictionary GetTestData()
{
StringDictionary dataFromExcel = new StringDictionary();
Excel.Application xlApp = new Excel.Application();
xlApp.Visible = false;
Excel.Workbook xlWb = xlApp.Workbooks.Open(@"C:\Users\...\Desktop\1.xlsx");
Excel.Worksheet xlSht1 = xlWb.Worksheets.get_Item(2);
dataFromExcel.Add("firstName", xlSht1.Cells[2, 1].value);
dataFromExcel.Add("lastName", xlSht1.Cells[2, 2].value);
dataFromExcel.Add("gender", xlSht1.Cells[2, 3].value);
dataFromExcel.Add("tool", xlSht1.Cells[2, 4].value);
dataFromExcel.Add("continent", xlSht1.Cells[2, 5].value);
xlWb.Close();
xlApp.Quit();
return(dataFromExcel);
}
public DataTable GetSheetDataAsDataTable(String filePath, String sheetName)
{
DataTable dt = new DataTable();
try
{
xlApp = new Excel.Application();
xlBook = xlApp.Workbooks.Open(filePath);
xlSheet = xlBook.Worksheets[sheetName];
xlRange = xlSheet.UsedRange;
DataRow row = null;
for (int i = 1; i <= xlRange.Rows.Count; i++)
{
if (i != 1)
{
row = dt.NewRow();
}
for (int j = 1; j <= xlRange.Columns.Count; j++)
{
if (i == 1)
{
dt.Columns.Add(xlRange.Cells[1, j].value);
}
else
{
row[j - 1] = xlRange.Cells[i, j].value;
}
}
if (row != null)
{
dt.Rows.Add(row);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
xlBook.Close();
xlApp.Quit();
}
return(dt);
}
public ExcelControl()
{
// This call is required by the Windows.Forms Form Designer.
InitializeComponent();
// TODO: Add any initialization after the InitComponent call
try
{
excelApp = new Excel.ApplicationClass();
excelApp.Visible = true;
Excel.Workbook book = excelApp.Workbooks.Open(@"C:\Documents and Settings\raghunandanr\Desktop\Benefit Design Modeler V3.28_ Blank.xls", Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
this.CreateGraphics().DrawString("ok ok", new System.Drawing.Font("Arial", 25), Brushes.GreenYellow, 10, 10);
book.Close(false, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
this.CreateGraphics().DrawString(ex.Message, new System.Drawing.Font("Arial", 16), Brushes.Chocolate, 10, 10);
}
}
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
}
}
}
private void Form1_Load(object sender, EventArgs e)
{
Excel.Application objExcel = new Excel.Application();
Excel.Workbook objWorkbook = objExcel.Workbooks.Open(@"C:\Users\John\Desktop\cs\TestExcel2_3000.xlsx");
Excel.Worksheet objWorksheet = objWorkbook.Worksheets["Tabelle1"];
zip = ReadSheet(objWorksheet);
objWorkbook.Close();
Excel.Workbook objWorkbook1 = objExcel.Workbooks.Open(@"C:\Users\John\Desktop\cs\TestExcel3_3000.xlsx");
Excel.Worksheet objWorksheet1 = objWorkbook1.Worksheets["Tabelle1"];
place = ReadSheet(objWorksheet1);
objWorkbook1.Close();
objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(objWorkbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objWorkbook1);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel);
}
public ExcelFile()
{
StringBuilder sb = new StringBuilder();
WB = xl.Workbooks.Open(Path);
xl.Visible = true;
foreach (Excel.Worksheet CurrentWS in WB.Worksheets)
{
rng = CurrentWS.UsedRange;
for (int i = 1; i < rng.Count; i++)
{
sb.append(rng.Cells[i].Value);
}
}
FullText = sb.ToString();
WB.Close(false);
xl.Quit();
}
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();
}
private static void excelSaveClose(string szPath, string szResultPath, Excel.Application rExcel, Excel.Workbook rWorkbook)
{
Process[] localByNameApp = Process.GetProcessesByName(szPath);//获取程序名的所有进程
if (localByNameApp.Length > 0)
{
foreach (var app in localByNameApp)
{
// if (!app.HasExited)
// {
#region
////设置禁止弹出保存和覆盖的询问提示框
rExcel.DisplayAlerts = false;
rExcel.AlertBeforeOverwriting = false;
rExcel.Visible = false;
//wb.Saved = true;
////保存工作簿
//rExcel.Application.Workbooks.Add(true).Save();
//保存excel文件
///excel.Save("E:\\c#_test\\winFormTest\\winFormTest\\hahaha.xls");
//确保Excel进程关闭
rExcel.Quit();
rExcel = null;
#endregion
app.Kill();//关闭进程
/*}*/
}
}
if (rWorkbook != null)
{
rExcel.DisplayAlerts = false;
rExcel.AlertBeforeOverwriting = false;
rExcel.Visible = false;
//rExcel.Application.Workbooks.Add(true).Save();
//保存结果
rWorkbook.SaveCopyAs(szResultPath);
///*, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing*/);
//wb.Save();
rWorkbook.Close(false, szPath, szPath);
}
rExcel.Quit();
// 安全回收进程
System.GC.GetGeneration(rExcel);
}
/// <summary>
/// 保存--查询结果
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void toolStructSave_Click(object sender, EventArgs e)
{
//1.打开Excle程序
Excel.Application application = new Excel.Application();
application.Visible = false;
application.UserControl = false;
Excel.Workbook workBook = (Excel.Workbook)(application.Workbooks.Add(Type.Missing));
Excel.Worksheet mySheet = workBook.Sheets[1] as Excel.Worksheet; //第一个sheet页
//2.遍历产品结构树
getchildValue(this.list_productTreeStruct.Items[0], mySheet, 0, 0);
//3.关闭Excle程序
workBook.Close(true, Type.Missing, Type.Missing);
workBook = null;
application.Quit();
application = null;
MessageBox.Show("保存成功");
}
/// <summary>
/// Closes the currently opened spreadsheet
/// </summary>
public void CloseSpreadsheet()
{
try
{
// Repeat xl.Visible and xl.UserControl releases just to be sure
// we didn't error out ahead of time.
if (ExcelObj != null)
{
ExcelObj.Visible = false;
ExcelObj.UserControl = false;
} // end if
if (theWorkbook != null)
{
// Close the document and avoid user prompts to save if our method failed.
theWorkbook.Close(false, null, null);
ExcelObj.Workbooks.Close();
} // end if
}
catch { }
// Gracefully exit out and destroy all COM objects to avoid hanging instances
// of Excel.exe whether our method failed or not.
if (theWorkbook != null)
{
Marshal.ReleaseComObject(theWorkbook);
}
if (ExcelObj != null)
{
ExcelObj.Quit();
}
if (ExcelObj != null)
{
Marshal.ReleaseComObject(ExcelObj);
}
theWorkbook = null;
ExcelObj = null;
GC.Collect();
}
private Boolean xls_exec(OfficeDomain office)
{
Boolean result = false;
if (Array.IndexOf(xls, office.prefix) > -1)
{
log("excel开始工作...");
Excel.Workbook workbook = null;
try
{
//参数含义：路径、只读
workbook = excelApp.Workbooks.Open(office.path, 0, true);
//转换
workbook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, office.pdfFileName);
result = true;
log("excel工作完毕,准备关闭文件..");
}
catch (Exception e)
{
error("excel工作异常：{0}", e.Message);
}
finally
{
if (workbook != null)
{
try
{
workbook.Close();
log("excel文件成功关闭..");
}
catch (Exception e)
{
error("excel文件关闭时发生错误{1},重启{0}.exe", xls_name, e.Message);
xls_start();
}
}
}
}
return(result);
}
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();
}
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();
}
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();
}
private void CreateExcel()
{
try
{
xlApp = new Excel.Application();
xlWB = xlApp.Workbooks.Add(Missing.Value);
xlSheet = xlWB.ActiveSheet;
CreateTable();
xlApp.Visible = true;
xlApp.UserControl = true;
}
catch (Exception ex)
{
string errMsg = string.Format("Error: {0}\nLine: {1}", ex.Message, ex.Source);
MessageBox.Show(errMsg, "Error");
xlWB.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
xlWB = null;
xlApp = null;
}
}
