导入EXCEL时允许导入的Sheet名不是Sheet1

上網有查到可透過GetOleDbSchemaTable來取到Excel的Sheet Name(Microsoft Jet database engine could not find the object 'sheet1$'.)，這樣就不用寫死在程式中了，如下，

01	using (OleDbConnection excelConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=@你的ExcelPath@;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"))

02

{

03	excelConn.Open();

04	DataTable excelShema = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

05	string firstSheetName = excelShema.Rows[0]["TABLE_NAME"].ToString();

06	string query = string.Format("Select * from [{0}]", firstSheetName);

07	using (OleDbDataAdapter DataAdapter = new OleDbDataAdapter(query, excelConn))

08

{

09	DataTable excelData = new DataTable("ExcelData");

10	DataAdapter.Fill(excelData);

11

}

12	excelConn.Close();

13

}

 

當然，使用Linq to Excel比較方便一些，如下，

1	ExcelQueryFactory excel = new ExcelQueryFactory(@"@你的ExcelPath@如h:\t.xls");

2	if (excel.GetWorksheetNames().Count() > 0)

3

{

4	var excelData = from c in excel.Worksheet(0)

5

select c;

6

}

 

用NPOI也是可以的哦!

 

註:

不管是使用OLEDB or Linq to Excel，如果您的Excel欄位中有混合資料，如某欄前8行的資料是數值，之後是字串。

就有可能會造成讀取判斷錯誤，後面的資料讀入null。

如果有這種狀況，設定註冊機碼TypeGuessRows的值從8改成0或是適合的大小還是需要的哦!

詳細可參考Blog文章:透過 OleDb 精準讀入 Excel 檔的方法

 

Excel导出

public static void DataToExcel(string path, DataTable dataTable)
{
try
{
object missingValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
Microsoft.Office.Interop.Excel._Workbook workBook = excel.Workbooks.Add(missingValue);
int excelRow = 0;
int excelColumn = 0;
for (int i = 0; i < dataTable.Columns.Count; i++)
{
excel.Cells[1, i+1] = dataTable.Columns[i].Caption.ToString();
}
for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
{
for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
{
excelRow = rowIndex + 2;
excelColumn = columnIndex + 1;
excel.Cells[excelRow, excelColumn] = dataTable.Rows[rowIndex][columnIndex];
}
}
workBook.SaveAs(path, missingValue, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null);
workBook.Close(false, missingValue, missingValue);
excel.Quit();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}

 

 

根据模板导出Excel

public static void BomSumDataToExcelByFormat(string path, DataTable dtZz, DataTable dtBomSum)
{
try
{
string tempPath = "";
//需要添加 Microsoft.Office.Interop.Excel引用
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)
{
//服务器上缺少Excel组件，需要安装Office软件。;
return;
}
app.Visible = false;
app.UserControl = true;
Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
tempPath = System.AppDomain.CurrentDomain.BaseDirectory + "BOM汇总表.xlsx";
if (!System.IO.File.Exists(tempPath))
tempPath = System.AppDomain.CurrentDomain.BaseDirectory + "BOM汇总表.xls";
Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(tempPath); //加载模板
Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets;
Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1); //第一个工作薄。
if (worksheet == null)
return; //工作薄中没有工作表.
if (dtBomSum.Rows.Count < 1 || dtZz.Rows.Count < 1)
return; //没有数据，不需要导出。

worksheet.Cells[2, 9] = dtZz.Rows[0]["品号"].ToString();
worksheet.Cells[3, 9] = dtZz.Rows[0]["描述"].ToString();
worksheet.Cells[4, 10] = dtZz.Rows[0]["版本2"].ToString();

//2、写入数据，Excel索引从1开始。
for (int i = 1; i <= dtBomSum.Rows.Count; i++)
{
int row_ = i + 6; //Excel模板上表头和标题行占了2行,根据实际模板需要修改;
int dt_row = i - 1; //dataTable的行是从0开始的。
worksheet.Cells[row_, 1] = dtBomSum.Rows[dt_row]["品号"].ToString();
worksheet.Cells[row_, 3] = dtBomSum.Rows[dt_row]["品名"].ToString() + "[" + dtBomSum.Rows[dt_row]["规格"].ToString() + "]";
worksheet.Cells[row_, 4] = dtBomSum.Rows[dt_row]["单位"].ToString();
worksheet.Cells[row_, 5] = dtBomSum.Rows[dt_row]["数量"].ToString();
worksheet.Cells[row_, 8] = dtBomSum.Rows[dt_row]["备注"].ToString();
worksheet.Cells[row_, 13] = dtBomSum.Rows[dt_row]["版本2"].ToString();
worksheet.Cells[row_, 14] = dtBomSum.Rows[dt_row]["物料组"].ToString();
worksheet.Cells[row_, 15] = dtBomSum.Rows[dt_row]["外部物料组"].ToString();
worksheet.Cells[row_, 16] = dtBomSum.Rows[dt_row]["采购类型"].ToString();
}

//调整Excel的样式。
Microsoft.Office.Interop.Excel.Range rg = worksheet.Range[worksheet.Cells[7, 1], worksheet.Cells[dtBomSum.Rows.Count + 7, 16]];
rg.Borders.LineStyle = 1; //单元格加边框。
worksheet.Columns.AutoFit(); //自动调整列宽。
//3、保存生成的Excel文件。
workbook.SaveAs(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//4、按顺序释放资源。
NAR(worksheet);
NAR(sheets);
NAR(workbook);
NAR(workbooks);
app.Quit();
NAR(app);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private static void NAR(object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
}
catch
{ }
finally
{
o = null;
}
}