public override void OnWizardFinish(CancelEventArgs e) { try { if (string.IsNullOrEmpty(txtFileToImport.Text)) { throw new ApplicationException("请选择excel文件"); } ExcelContext context = GetContext() as ExcelContext; context.ExcelFile = txtFileToImport.Text; context.IncludeHeader = chkFirstRowColumnNames.Checked; DTSWizardBook wizardBook = GetWizard() as DTSWizardBook; if (wizardBook.Session.Direction == DTSDirection.Import) { Import(context); } else { Export(context); } } catch (Exception ex) { e.Cancel = true; MessageBox.Show(ex.Message); } }
private void Import(ExcelContext context) { if (!File.Exists(txtFileToImport.Text)) { throw new ApplicationException("文件不存在"); } context.Data = GetData(context); }
private DataTable GetData(ExcelContext context) { DataTable dt = new DataTable(); try { string ext = Path.GetExtension(context.ExcelFile); Net.SourceForge.Koogra.IWorkbook wb = null; if (ext.Equals(".xlsx", StringComparison.OrdinalIgnoreCase)) { wb = (Net.SourceForge.Koogra.WorkbookFactory.GetExcel2007Reader(context.ExcelFile)); } else if (ext.Equals(".xls", StringComparison.OrdinalIgnoreCase)) { wb = (Net.SourceForge.Koogra.WorkbookFactory.GetExcelBIFFReader(context.ExcelFile)); //读链接内容 Net.SourceForge.Koogra.Excel.Workbook workbook = wb as Net.SourceForge.Koogra.Excel.Workbook; context.Links = workbook.HyperLinks.AsEnumerable().Select(c => c.Link).ToList(); } Net.SourceForge.Koogra.IWorksheet ws = wb.Worksheets.GetWorksheetByIndex(0); int rowIndex = 0; int columnIndex = 0; uint firstRow = ws.FirstRow; if (!context.IncludeHeader) { firstRow += 1; } for (uint r = firstRow; r <= ws.LastRow; ++r) { dt.ExpandRow(rowIndex); Net.SourceForge.Koogra.IRow row = ws.Rows.GetRow(r); if (row != null) { for (uint colCount = ws.FirstCol; colCount <= ws.LastCol; ++colCount) { dt.ExpandColumn(columnIndex); if (row.GetCell(colCount) != null && row.GetCell(colCount).Value != null) { dt.Rows[rowIndex][columnIndex] = row.GetCell(colCount).Value.ToString(); } else { dt.Rows[rowIndex][columnIndex] = ""; } columnIndex++; } columnIndex = 0; } rowIndex++; } } catch (Exception ex) { MessageBox.Show(ex.Message); } return(dt); }
private void Export(ExcelContext context) { DataSet ds = new DataSet(); DataTable dt = context.Data.Copy(); dt.TableName = "tmp"; ds.Tables.Add(dt); //Here's the easy part. Create the Excel worksheet from the data set ExcelLibrary.DataSetHelper.CreateWorkbook(context.ExcelFile, ds); ds.Dispose(); }
private void linkBtn_Click(object sender, EventArgs e) { OnWizardFinish(new CancelEventArgs()); ExcelContext context = GetContext() as ExcelContext; StringBuilder sb = new StringBuilder(); foreach (var s in context.Links) { sb.Append(s.Replace("\0", "")); sb.AppendLine(); } NotepadHelper.ShowMessage(sb.ToString(), "当前excel文档包含链接"); }
public override void Prepare() { WizardContext context = null; if (csvRadioBtn.Checked) { context = new CsvContext(); } else if (sqlserverRadioBtn.Checked) { context = new MsSqlContext(); } else if (excelRadioBtn.Checked) { context = new ExcelContext(); } context.InitailizeWizardPages(); context.Pages.Insert(0, this); GetWizard().Context = context; DTSWizardBook wizardBook = GetWizard() as DTSWizardBook; context.Data = wizardBook.Session.DataSource; }