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;
}