User-718146471 posted
Decided since Interop is the only clean way of doing this without having to spend $400, I build a windows forms app to do it. The code that gets the data sorted out is actually pretty cool, so I'm posting it for anyone else who might run across this.
Keeping that in mind, this code is for Windows Forms. I'm only posting to help others avoid hours of frustration.
// This is the code that determines the fields, flat text (Value2) versus actual hyperlinks
// Leaving out extraneous code that does non-related tasks
private void button1_Click(object sender, EventArgs e)
{
string ConnStr = string.Empty;
//Lets me decide in the app config if its dev or prod
string ConnChoice = ConfigurationManager.AppSettings["ConnChoice"];
if (ConnChoice == "DEV")
{
ConnStr = ConfigurationManager.ConnectionStrings["ConnStrDev"].ConnectionString.ToString();
}
else
{
ConnStr = ConfigurationManager.ConnectionStrings["ConnStrProd"].ConnectionString.ToString();
}
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string textval = string.Empty;
string textval2 = string.Empty;
string str = string.Empty;
int rCnt = 0;
int cCnt = 0;
int HyperLink = 2;
string ExcelFileName = label1.Text.ToString();
xlWorkBook = xlApp.Workbooks.Open(ExcelFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// get_Item(1) is sheet #1. You could probably put this into an int loop increment so it grabs every sheet
// providing they have the same format.
range = xlWorkSheet.UsedRange;
str = string.Empty;
string myString = string.Empty;
for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
for (cCnt = 1; cCnt <= HyperLink; cCnt++)
{
// this .Hyperlinks.Count check for != 0 tells me if Column B is hyperlink
if (range.Cells[rCnt, HyperLink].Hyperlinks.Count != 0)
{
if (cCnt == HyperLink)
{
textval = Convert.ToString(((range.Cells[rCnt, cCnt] as Excel.Range).Value2));
textval2 = ((range.Cells[rCnt, cCnt] as Excel.Range).Cells.Hyperlinks[1].Address);
}
else
{
str = Convert.ToString((range.Cells[rCnt, cCnt] as Excel.Range).Value2);
}
}
}
// str is the class title
// textval is the class number
// textval2 is the embedded URL
// Do your db insert/update work here
str = string.Empty;
textval = string.Empty;
textval2 = string.Empty;
}
}
// Close the Workbook and Excel
xlWorkBook.Close(true, null, null);
xlApp.Quit();
// Kill off all objects and do garbage collection
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
// Leave the importer application since the work is done
Application.Exit();
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
private void btnOpenFile_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "Excel 2007/2010 File (*.xlsx)|";
dialog.InitialDirectory = @"C:\";
dialog.Title = "Select excel file";
dialog.ShowDialog();
foreach (var file in dialog.FileNames)
{
label1.Text = file.ToString();
}
button1.Visible = true;
}
}
