OLEDB导入导出Excel

导入

    

string arrerrorInfo = "";

string str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + openFileDialog1.FileName +
"';Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
OleDbConnection OLECN = new OleDbConnection(str);
////后加----------- 
OLECN.Open();
System.Data.DataTable dtSheetName = OLECN.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
OLECN.Close();
////-----------

ArrayList arrTables = new ArrayList();

int err = 0;
List<string> lt = new List<string>();

foreach(DataRow drSheetName in dtSheetName.Rows)
{

string sheetName = Convert.ToString("" + drSheetName["table_name"]);



string SQLSTR = "Select * from [" + sheetName + "]";
OleDbDataAdapter OLEDB = new OleDbDataAdapter(SQLSTR, OLECN);
DataSet ds = new DataSet();
OLEDB.Fill(ds, "TempBOM");
//IREPApp.ShowWarn(RSForm1,sheetName);
sheetName = sheetName.TrimStart('\'').TrimEnd('\'').TrimEnd('$');
int isign = 0;
for(int i = 0 ;i<lt.Count ; i++)
{
if(sheetName.IndexOf(lt[i]) != -1)
{
isign = 1;
break;	
}
}
if(isign == 1)
{
continue;
}
lt.Add(sheetName);　

导出：

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法启动EXcel,可能机器未安装Execl", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}

Microsoft.Office.Interop.Excel.Workbook workbook = xlApp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;

((Microsoft.Office.Interop.Excel.Range)(worksheet.Columns["A",System.Type.Missing])).ColumnWidth = "3.5";
((Microsoft.Office.Interop.Excel.Range)(worksheet.Columns["B",System.Type.Missing])).ColumnWidth = "26.5";
((Microsoft.Office.Interop.Excel.Range)(worksheet.Columns["C",System.Type.Missing])).ColumnWidth = "21";

range = worksheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, 21]);
range.Merge(Type.Missing);
range.NumberFormatLocal = "@";
xlApp.Cells[1, 1] = "表";
range.Font.Bold = true;
range.Font.Size = 18;
range.RowHeight = "30";
// range.Borders.Weight = 3;
//range.Borders.LineStyle = 1;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter ;

 

range = worksheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, 21]);
range.NumberFormatLocal = "@";
range.Borders.LineStyle = 1;
range.RowHeight = "20";
range = worksheet.get_Range(xlApp.Cells[2, 2], xlApp.Cells[2, 7]);
range.Merge(Type.Missing);
xlApp.Cells[2, 1] = "";

//插入图片

range =worksheet.get_Range(xlApp.Cells[i+8, 2], xlApp.Cells[i+8, 2]);
range.Select();
float PicLeft, PicTop;
PicLeft = Convert.ToSingle(range.Left)+2;
PicTop = Convert.ToSingle(range.Top ) + 5 ;//(i+k)*90 / 2 -43 ;
if(cImagePath+"" != "")
{
Image img = null;
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(new Uri(cImagePath));
req.Method = "GET";
req.UserAgent = " Mozilla/5.0 (Windows NT 6.3; Trident/7.0; rv:11.0) like Gecko";
req.Accept = "image/png, image/svg+xml, image/*;q=0.8, */*;q=0.5";
req.Headers.Add("X-HttpWatch-RID", " 46990-10314");
req.Headers.Add("Accept-Language", "zh-Hans-CN,zh-Hans;q=0.8,en-US;q=0.5,en;q=0.3");

HttpWebResponse ress = (HttpWebResponse)req.GetResponse();
Stream sstreamRes = ress.GetResponseStream();
img = System.Drawing.Image.FromStream(sstreamRes);
img.Save(Application.StartupPath+"\\temp.bmp");
if(!string.IsNullOrEmpty(cImagePath))
{
worksheet.Shapes.AddPicture(Application.StartupPath+"\\temp.bmp", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, 160,85);

}
}

workbook.SaveCopyAs(SaveFileDialog.FileName);