C# Workbook.LoadFromFile方法代码示例

        /// <summary>
        /// pobieranie danych z excelu
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public List<DataGps> LoadData(string path)
        {
            Workbook workbook = new Workbook();
            List<DataGps> DataGpsList=new List<DataGps>();
            string fileExtension = Path.GetExtension(path).Replace(".", "");
            if (fileExtension == "xls")
                workbook.LoadFromFile(path, ExcelVersion.Version97to2003);
            else if (fileExtension == "xlsx")
                workbook.LoadFromFile(path, ExcelVersion.Version2013);

            Worksheet sheet = workbook.Worksheets[0];

            DataTable dataTable = sheet.ExportDataTable();
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                var dataGps=new DataGps();
                dataGps.BoatSpeed = Convert.ToDouble(dataTable.Rows[i]["predkosc"]);
                dataGps.BoatDirection = Convert.ToDouble(dataTable.Rows[i]["kurs"]);
                dataGps.WindDirection = Convert.ToDouble(dataTable.Rows[i]["kierunek_wiatru"]);
                dataGps.WindSpeed = Convert.ToDouble(dataTable.Rows[i]["sila_wiatru"]);
                dataGps.GeoWidth = dataTable.Rows[i]["szerokosc"].ToString();
                dataGps.GeoHeight = dataTable.Rows[i]["dlugosc"].ToString();
                DataGpsList.Add(dataGps);
            }
            return DataGpsList;
        }

        private void btnRun_Click(object sender, RoutedEventArgs e)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\..\..\..\Data\ImageSample.xls");
            Worksheet sheet = workbook.Worksheets[0];
            //get picture
            ExcelPicture pic = sheet.Pictures[0];

            //save memoryStream
            System.Windows.Controls.Image img = new System.Windows.Controls.Image();
            System.IO.MemoryStream mem = new System.IO.MemoryStream();
            pic.Picture.Save(mem, System.Drawing.Imaging.ImageFormat.Png);
            img.Source = GetBitmapSourceFromStream(mem);
            //create window
            Window imgWindow = new Window();
            imgWindow.Title = "ImageSample";
            imgWindow.WindowStartupLocation = WindowStartupLocation.CenterScreen;
            imgWindow.Width = pic.Picture.Width;
            imgWindow.Height = pic.Picture.Height;
            imgWindow.ResizeMode = ResizeMode.NoResize;
            //set margin
            Thickness thick = new Thickness(0,0,0,0);
            img.Margin = thick;
            imgWindow.Content = img;
            imgWindow.ShowDialog();
        }

        public List<Question> LoadFromXls()
        {
            List<Question> questions = new List<Question>();
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(fileName);
            var worksheet = workbook.Worksheets[0];
            int rowIDX = 1;
            int cellIDX = 1;

            while (rowIDX != worksheet.Rows.Length && !worksheet.Rows[rowIDX].Cells[cellIDX].Value.Equals(""))
            {
                Question question = new Question();
                question.Value = worksheet.Rows[rowIDX].Cells[cellIDX].Value;
                rowIDX++;
                for (int i = 1; i <= 4; i++)
                {
                    question.Add(new Answer(worksheet.Rows[rowIDX].Cells[cellIDX].Value,
                        worksheet.Rows[rowIDX].Cells[cellIDX + 1].Value.Equals("+")));
                    rowIDX++;
                }
                questions.Add(question);
            }



            return questions;
        }

 public string ExceltoPngFormat(string folderStructure, string RetVal)
 {
     Workbook workbook1 = new Workbook();
     workbook1.LoadFromFile(Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, folderStructure + RetVal + "\\" + RetVal + ".xls"));
     Worksheet sheet = workbook1.Worksheets[0];
     sheet.SaveToImage(RetVal + ".png");
     System.Diagnostics.Process.Start(RetVal + ".png");
     return "";
 }

 public ExcelMarkDesigner(string existFileName,string pathFileTemplate)
 {
     currentWorkBook = new Workbook();
     if (pathFileTemplate != string.Empty)
     {
         this.pathFileTemplate = pathFileTemplate;
         currentWorkBook.LoadFromFile(@pathFileTemplate);
     }
     this.existFileName = existFileName;
 }

 /// <summary>
 /// Excel To DataTable
 /// </summary>
 /// <param name="path"></param>
 /// <returns></returns>
 public static DataTable ToDataTable(string path)
 {
     //Create a new workbook
     var workbook = new Workbook();
     //Load a file and imports its data
     workbook.LoadFromFile(path);
     //Initialize worksheet
     var sheet = workbook.Worksheets[0];
     // get the data source that the grid is displaying data for
     return sheet.ExportDataTable();
 }

 public void SaveAndExport(bool excel, bool pdf)
 {
     pack.Save();
     pack.Dispose();
     if (pdf)
     {
         Workbook pdfWork = new Workbook();
         pdfWork.LoadFromFile(@"Report.xlsx");
         pdfWork.SaveToFile(fileinfo.FullName.Replace(".xlsx", ".pdf"), Spire.Xls.FileFormat.PDF);
     }
    
 }

        public void AddRow(CheckPointLogModel logData)
        {
            var columns = new List<KeyValuePair<string, object>>();

            if (logData != null)
            {
                columns.Add(new KeyValuePair<string, object>("№", logData.Counters.Journal));
                columns.Add(new KeyValuePair<string, object>("Дата", this.TruncateDate(logData.StartDate).ToString(StringConstrants.DateTimeFormat)));
                columns.Add(new KeyValuePair<string, object>("Changelist", logData.Counters.MaxCommitChange));
                columns.Add(new KeyValuePair<string, object>("Upgrade", logData.Counters.Upgrade));
                columns.Add(new KeyValuePair<string, object>("Потребители", logData.UserCount));
                columns.Add(new KeyValuePair<string, object>("Проекти", logData.ProjectCount));
                columns.Add(new KeyValuePair<string, object>("Файлове", logData.Sizes.FilesCount));
                columns.Add(new KeyValuePair<string, object>("Версии", logData.Sizes.RevisionsCount));
                columns.Add(new KeyValuePair<string, object>("Депо", string.Format("{0:0.00} Mb", logData.DepotSize)));
                columns.Add(new KeyValuePair<string, object>("Log", string.Format("{0:0.00} Mb", logData.Log.FileSize)));
                columns.Add(new KeyValuePair<string, object>("Auditlog", string.Format("{0:0.00} Mb", logData.AuditLog.FileSize)));
                columns.Add(new KeyValuePair<string, object>("Име", logData.Arhive != null ? logData.Arhive.ArhivePatternName : string.Empty));
                columns.Add(new KeyValuePair<string, object>("Размер", string.Format("{0:0.00} Mb", logData.Arhive != null ? logData.Arhive.Size : 0)));
                columns.Add(new KeyValuePair<string, object>("Платформа", logData.ServerInfo.Platform));
                columns.Add(new KeyValuePair<string, object>("Версия", string.Format("'{0}", logData.ServerInfo.Version)));
                columns.Add(new KeyValuePair<string, object>("Ревизия", logData.ServerInfo.Revision));
                columns.Add(new KeyValuePair<string, object>("от Дата", this.TruncateDate(logData.ServerInfo.Date).ToString(StringConstrants.DateFormat)));
            }

            var workbook = new Workbook();
            workbook.LoadFromFile(this.FullFileRoot);
            Worksheet sheet = workbook.Worksheets.FirstOrDefault(s => s.Name == SheetName) as Worksheet;
            if (sheet == null)
            {
                throw new ArgumentNullException("Excel sheet not found");
            }

            var range = sheet.Range;
            var headerStart = this.FindHeader(range, "№");
            var lastRow = this.FindLastRow(range, headerStart);
            sheet.InsertRow(lastRow.Row, 1, InsertOptionsType.FormatAsBefore);

            for (int index = 0; index < columns.Count; index++)
            {
                var currentCol = lastRow.Column + index;
                var headerText = sheet.Range[headerStart.Row, currentCol].Text;
                var currentCell = columns[index];
                if (headerText == currentCell.Key)
                {
                    sheet.Range[lastRow.Row, currentCol].Value2 = currentCell.Value;
                }
            }

            workbook.Save();
            workbook.Dispose();
        }

		protected void Button1_Click(object sender, System.EventArgs e)
		{
			Workbook workbook = new Workbook();
			
			//Initailize worksheet
			workbook.LoadFromFile(Page.MapPath(@"./Data/EditChartSample.xls"),true);
			Worksheet sheet = workbook.Worksheets[0];

			//Writes chart data
			CreateChartData(sheet);

			workbook.SaveToHttpResponse("Sample.xls", Response);
		}

 public static int ConvertXLFiles(string outDir, int start)
 {
     DirectoryInfo downloadedMessageInfo = new DirectoryInfo(outDir);
     Workbook workbook = new Workbook();
     foreach (FileInfo file in downloadedMessageInfo.GetFiles("*.xlsx"))
     {
         Console.WriteLine("Loading : " + file.Name);
         workbook.LoadFromFile(file.FullName, ExcelVersion.Version2013);
         workbook.SaveToFile(string.Format("{0}{1:000}.pdf", outDir, start), FileFormat.PDF);
         file.Delete();
         start++;
     }
     foreach (FileInfo file in downloadedMessageInfo.GetFiles("*.xls"))
     {
         Console.WriteLine("Loading ->" + file.Name);
         workbook.LoadFromFile(file.FullName, ExcelVersion.Version97to2003);
         workbook.SaveToFile(string.Format("{0}{1:000}.pdf", outDir, start), FileFormat.PDF);
         Console.WriteLine("Saving ->" + string.Format("{0}{1:000}.pdf", outDir, start));
         file.Delete();
         start++;
     }
     return start;
 }

        static void Main(string[] args)
        {
            var existingFile = new FileInfo("FMCG2.xlsx");
            // Open and read the XlSX file.

            using (var package = new ExcelPackage(existingFile))
            {
                // Get the work book in the file
                ExcelWorkbook workBook = package.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {
                        var newfile = new FileInfo("new.xlsx");
                        ExcelPackage pck = new ExcelPackage(newfile);
                        var ws = pck.Workbook.Worksheets.Add("data", workBook.Worksheets["Reports"]);
                        pck.Save();
                        //var drawings = workBook.Worksheets["Reports"].Drawings;

                        //workBook.Worksheets["Calculation"].Cells["D4"].Value = 119;
                        //workBook.Worksheets["Calculation"].Cells["D5"].Value = 519;
                        //IEnumerable<int> arr = new List<int>() { 1, 2, 3, 4, 5 };
                        //Int32[] arr2 = new Int32[] { 1, 2, 3, 4, 5 };
                        //ExcelAddress address = new ExcelAddress("D1:D5");
                        //workBook.Worksheets["Calculation"].Select(address);
                        //workBook.Worksheets["Calculation"].SelectedRange.LoadFromCollection<int>(arr);

                        //workBook.Worksheets["Calculation"].Calculate();

                        //MemoryStream memStream1 = new MemoryStream(); // = new MemoryStream(pck.GetAsByteArray());
                        //package.SaveAs(memStream1);
                        //memStream1.Position = 0;
                        //Workbook spireworkbook2 = new Workbook();
                        //MemoryStream memStream2 = new MemoryStream();
                        //spireworkbook.SaveToStream(memStream2);
                        //spireworkbook.LoadFromStream(memStream1);

                        Workbook spireworkbook = new Workbook();
                        spireworkbook.LoadFromFile("new.xlsx", ExcelVersion.Version2007);
                        spireworkbook.SaveToFile("results.pdf", Spire.Xls.FileFormat.PDF);

                        Console.Read();
                    }
                }
            }
        }

		private void btnRun_Click(object sender, System.EventArgs e)
		{
			Workbook workbook = new Workbook();
			
			workbook.LoadFromFile(@"..\..\..\..\..\..\Data\MarkerDesignerSample.xls");
			DataTable dt = (DataTable)dataGrid1.DataSource;

			Worksheet sheet = workbook.Worksheets[0];

			workbook.MarkerDesigner.AddParameter("Variable1",1234.5678);
			workbook.MarkerDesigner.AddDataTable("Country",dt);
			workbook.MarkerDesigner.Apply();

			sheet.AllocatedRange.AutoFitRows();
			sheet.AllocatedRange.AutoFitColumns();;

			workbook.SaveToFile("Sample.xls");
			ExcelDocViewer(workbook.FileName);
		}

		private void btnRun_Click(object sender, System.EventArgs e)
		{
			Workbook workbook = new Workbook();
			workbook.LoadFromFile(@"..\..\..\..\..\..\Data\ImageSample.xls");
			Worksheet sheet = workbook.Worksheets[0];

			ExcelPicture pic = sheet.Pictures[0];
			
			using( Form frm1 = new Form())
			{
				PictureBox pic1 = new PictureBox();
				pic1.Image = pic.Picture;
				frm1.Width = pic.Picture.Width;
				frm1.Height = pic.Picture.Height;
				frm1.StartPosition = FormStartPosition.CenterParent;
				pic1.Dock = DockStyle.Fill;
				frm1.Controls.Add(pic1);
				frm1.ShowDialog();
			}
		}

		private void btnRun_Click(object sender, System.EventArgs e)
		{
			Workbook workbook = new Workbook();
			
			workbook.LoadFromFile(@"..\..\..\..\..\..\Data\DataTableSample.xls");
			//Initailize worksheet
			Worksheet sheet = workbook.Worksheets[0];
            sheet.SaveToImage("sample.bmp");
            ExcelDocViewer("sample.bmp");
		}

        private void btnRun_Click(object sender, System.EventArgs e)
        {
            Workbook workbook = new Workbook();

            workbook.LoadFromFile(@"..\..\..\..\..\..\Data\MiscDataTable.xls");
            Worksheet sheet = workbook.Worksheets[0];
            sheet.InsertRow(1, 2);
            sheet.Rows[0].RowHeight = 16;

            //Style
            sheet.Range["A1:B1"].Style.Font.IsBold = true;
            sheet.Range["A3:E3"].Style.KnownColor = ExcelColors.LightOrange;

            int index = 4;
            while (sheet.Range[String.Format("A{0}", index)].HasString)
            {
                sheet.Range[String.Format("A{0}:E{0}", index)].Style.KnownColor
                    = index % 2 == 0 ? ExcelColors.PaleBlue : ExcelColors.LightTurquoise;
                index++;
            }

            //define named cell ranges
            sheet.Names.Add("Countries", sheet[String.Format("A4:A{0}", index - 1)]);
            sheet.Names.Add("Cities", sheet[String.Format("B4:B{0}", index - 1)]);
            sheet.Names.Add("Continents", sheet[String.Format("C4:C{0}", index - 1)]);
            sheet.Names.Add("Area", sheet[String.Format("D4:D{0}", index - 1)]);
            sheet.Names.Add("Population", sheet[String.Format("E4:E{0}", index - 1)]);
            sheet.Names.Add("NumberOfCountries", sheet[String.Format("A{0}", index)]);

            //references of names
            sheet.Range["A1"].Value = "Number of Countries:";
            sheet.Range["B1"].Formula = "=NumberOfCountries";
            sheet[String.Format("A{0}", index)].Formula = "=COUNTA(Countries)";
            sheet[String.Format("D{0}", index)].Formula = "=SUM(Area)";
            sheet[String.Format("E{0}", index)].Formula = "=SUM(Population)";

            //style
            sheet.Rows[index - 1].RowHeight = 16;
            String range = String.Format("A{0}:E{0}", index);
            sheet.Range[range].Style.Font.IsBold = true;
            sheet.Range[range].Style.KnownColor = ExcelColors.LightOrange;
            sheet.Range[range].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 0);
            sheet.Range[range].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thick;
            sheet.Range[range].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 0);
            sheet.Range[range].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range[range].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 0);
            sheet.Range[range].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            sheet.Range[range].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 0);
            sheet.Range[range].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;

            workbook.SaveToFile("Sample.xls");

            ExcelDocViewer(workbook.FileName);
        }