excelutility.cs

股票操作员每日一记 源程序 股票操作员每日一记 源程序

 
	using System;

	using System.Data;
	using System.Data.OleDb;
	using System.IO;
	using System.Text;


	namespace stock_Log
	{
		class ExcelUtility
		{
			private static string strCurrentDirectory = Directory.GetCurrentDirectory()+"\\Tmp";
			private static int FileNumber = 1;
			private static string CurrentTmpFileName = string.Empty;

			private static string GetExcelFilePath(string FileDirectory)
			{
				while (true)
				{
					string strFileName = FileDirectory + "\\Tmp" + FileNumber.ToString() + ".xls";
					if (File.Exists(strFileName))
						FileNumber++;
					else
						return strFileName;
				}
			}

			private static string GetExcelSheetNameAndInsertHeader(string FilePath)
			{
				Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
				app.Visible = false; 
				app.UserControl = true;

				Workbooks workbooks = app.Workbooks;
				_Workbook workbook = workbooks.Add(FilePath);

				Sheets sheets = workbook.Worksheets;
				_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);

				string strReturn = string.Empty;

				if (worksheet != null)
				{
					strReturn = worksheet.Name;
					Range range = worksheet.get_Range("A1", "A1");
					range = range.EntireRow;
					range.Insert(XlInsertShiftDirection.xlShiftDown, Type.Missing);
					range = worksheet.get_Range("A1", "A1");
					range.set_Value(Type.Missing, "1");
					CurrentTmpFileName = GetExcelFilePath(strCurrentDirectory);
					worksheet.SaveAs(CurrentTmpFileName, XlFileFormat.xlExcel9795, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
					workbook.Close(false, Type.Missing,Type.Missing);
				}
				else
				{
					workbook.Close(false, null, false);
				}

				app.Quit();

				return strReturn;

			}

			private static string GetExcelSheetNameWithoutInsertHeader(string FilePath)
			{
				Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
				app.Visible = false;
				app.UserControl = true;

				Workbooks workbooks = app.Workbooks;
				_Workbook workbook = workbooks.Add(FilePath);

				Sheets sheets = workbook.Worksheets;
				_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);

				string strReturn = string.Empty;

				if (worksheet != null)
				{
					strReturn = worksheet.Name;
					CurrentTmpFileName = GetExcelFilePath(strCurrentDirectory);
					worksheet.SaveAs(CurrentTmpFileName, XlFileFormat.xlExcel9795, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
					workbook.Close(false, Type.Missing, Type.Missing);
				}
				else
				{
					workbook.Close(false, null, false);
				}

				app.Quit();

				return strReturn;
			}

			private static string GetExcelSheetNameWithoutInsertHeaderAddX(string FilePath, int itemcount, int columncount)
			{
				Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
				app.Visible = false;
				app.UserControl = true;

				Workbooks workbooks = app.Workbooks;
				_Workbook workbook = workbooks.Add(FilePath);

				Sheets sheets = workbook.Worksheets;
				_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);

				string strReturn = string.Empty;

				int x = System.Environment.TickCount;

				if (worksheet != null)
				{
					ExcelCellsProcessFrm processfrm = new ExcelCellsProcessFrm();
					processfrm.itemcount = itemcount;
					processfrm.columncount = columncount;
					processfrm.worksheet = worksheet;
					//for (int i = 1; i <= itemcount; i++)
					//{
					//    for (int j = 0; j < columncount; j++)
					//    {
					//        string strCell = string.Format("{0}{1}", columns[j],i);
					//        Range range = worksheet.get_Range(strCell, strCell);
					//        range.Value2 = range.Text + "X";
					//    }
					//}
					processfrm.ShowDialog();
					strReturn = worksheet.Name;
					CurrentTmpFileName = GetExcelFilePath(strCurrentDirectory);
					worksheet.SaveAs(CurrentTmpFileName, XlFileFormat.xlExcel9795, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
					workbook.Close(false, Type.Missing, Type.Missing);
				}
				else
				{
					workbook.Close(false, null, false);
				}

				x = System.Environment.TickCount - x;

				app.Quit();

				return strReturn;
			}


			public static DataSet Excel2DataSet(string FilePath,bool bInsertHeader, int ItemsCount, int ColumnsCount)
			{
//				string strExcelSheetName = string.Empty;
//				if(bInsertHeader)
//					strExcelSheetName = GetExcelSheetNameAndInsertHeader(FilePath);
//				else if(ItemsCount==0)
//					strExcelSheetName = GetExcelSheetNameWithoutInsertHeader(FilePath);
//				else
//					strExcelSheetName = GetExcelSheetNameWithoutInsertHeaderAddX(FilePath,ItemsCount,ColumnsCount);
//				if (string.IsNullOrEmpty(strExcelSheetName))
//					return null;

				string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + CurrentTmpFileName + ";" + "Extended Properties=Excel 8.0;";
				OleDbConnection conn = new OleDbConnection(strConn);
				conn.Open();
				DataSet ds = new DataSet();
				string strExcelSheet = string.Format("select * from [{0}$]",strExcelSheetName);
				OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcelSheet, strConn);
				myCommand.Fill(ds, "table1");
				return ds;
			}

			public static string GetNumber(string strvalue)
			{
				char[] ch = { '-', '+', '.', '1', '2', '3', '4', '5', '6', '7', '8', '9', '0' };
				char[] values = strvalue.ToCharArray();
				for (int i = 0; i < values.Length; i++)
				{
					bool bfound = false;
					for(int j=0;j<ch.Length;j++)
					{
						if (values[i] == ch[j])
						{
							bfound = true;
							break;
						}
					}
					if (!bfound)
						values[i] = ' ';
				}
				string strReturn = new string(values);
				return strReturn.Replace(" ", "");
			}

		}
	}