在工作中我们经常要实现 Excel 文件和 DataTable 的转换:
首先我们来讨论读取Excel :
我以前通过的方式为:
string sConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + ExcelFile + ";Extended Properties=Excel 8.0";
OleDbConnection cn = new OleDbConnection(sConnectionString);
OleDbDataAdapter adp = new OleDbDataAdapter(
"Select * from [Sheet1$]",cn);
DataTable ds = new DataTable();
adp.Fill(ds,"myTable");
这种方式最大的问题就是读取Excel钱必须确定读取的Excel 文档下的 sheet 名称(红色标记的地方),因为用户的 sheet 名是经常改动的,所以很容易出错,给用户也带来了很多不必要的麻烦,而且在读取单个文档下的多个 sheet 也很不方便。
在微软的MSDN 下我们找到了解决方法:
static DataTable GetSchemaTable(string connectionString)
{
using (OleDbConnection connection = new
OleDbConnection(connectionString))
{
connection.Open();
DataTable schemaTable = connection.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
return schemaTable;
}
}
通过这种方式我们就能动态的获取Excel文档下的所有sheet名
最后整理为:
/// <summary>
/// 将Excel文档转换成Datatable
/// </summary>
/// <param name="path">Excel文档的路径</param>
/// <returns ></returns>
public System.Data.DataTable[] GetExcelData(string path)
{
if (Path.GetExtension(path) != ".xls")
{
path += ".xls";
}
if (!File.Exists(path))
{
throw new Exception("文件不存在!");
}
System.Data.DataTable[] dts;
System.Data.DataTable tempDt = new System.Data.DataTable();
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + path + ";Extended Properties=Excel 8.0;";
OleDbConnection craboDbConnection = new OleDbConnection(strConn);
craboDbConnection.Open();
System.Data.DataTable dtSheetName = craboDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
string[] strTableNames = new string[dtSheetName.Rows.Count];
dts = new System.Data.DataTable[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
try
{
for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [" + strTableNames[i] + "]", craboDbConnection);
OleDbDataAdapter adapter = new OleDbDataAdapter(myOleDbCommand);
dts[i] = new System.Data.DataTable(strTableNames[i]);
adapter.Fill(dts[i]);
}
}
catch (Exception ex)
{
throw new Exception("读取Excel失败!" + ex.Message);
}
finally
{
craboDbConnection.Close();
craboDbConnection = null;
}
return dts;
}
这样我们可以不用知道具体sheet名称的情况下获取多个sheet 的内容
现在我们来讨论将DataTbale 转换成Excel 文件
以前我的方式是这样的:
/// <summary>
/// 将DataTable里面的内容保存成Excel文件
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="savepath">保存路径</param>
public void DataTableConverExcel(System.Data.DataTable dt, string savepath)
{
int count = dt.Rows.Count;//获取数据表中DataRow行总数
int column = dt.Columns.Count;//获取数据表中列总数
Excel.ApplicationClass excelapp = new ApplicationClass();
Excel.Workbook wb = excelapp.Application.Workbooks.Add(true);
int index = 1;
string values;
foreach (DataColumn dc in dt.Columns)//添加列的信息
{
excelapp.Cells[1, index] = dc.ColumnName;
index++;
}
for (int x = 1; x <= count; x++)
{
for (int y = 1; y <= column; y++)
{
values = dt.Rows[x - 1].ItemArray[y - 1].ToString();
if (values != "")
{
if (isNum(values))
{
values = "'" + values;
}
}
excelapp.Cells[x + 1, y] = values;
}
}
string tname = savepath;
wb.SaveAs(tname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
try
{
wb.Saved = true;
excelapp.UserControl = false;
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
excelapp.Quit();
}
}
/// <summary>
///应为在导成Excel文件的时候如果前面为0所有字符为数字的文本类型将会识别成数字类型而0 将不会显示出来
/// </summary>
/// <param name="values">sitrngValue</param>
/// <returns>isNum</returns>
bool isNum(string values)
{
bool isNum = true;
if (values[0] == '0')//防止将数字类型成为文本类型
{
foreach (char c in values.ToCharArray())
{
if (!char.IsNumber(c))
{
isNum = false;
}
}
}
else
{
isNum = false;
}
// Debug.WriteLine(values+" : "+isNum.ToString ());
return isNum;
}
这种方式在数据量比较小的情况下还是可以的,但如果数据量大的话,嘿嘿....
后来在网上找到了通过数组处理的方式:
/// <summary>
/// 将DataTable里面的内容保存成Excel文件
/// </summary>
/// <param name="dt">System.Data.DataTable</param>
/// <param name="strFileName">fileName</param>
public void DataTableToFile(System.Data.DataTable dt, string strFileName)
{
Excel.Application app = null;
Excel.Workbook book = null;
Excel.Worksheet sheet = null;
Excel.Range rng = null;
bool bExp = false;
const int MAX_EXCEL_ROW = 65535;
try
{
app = new Excel.Application();
app.DisplayAlerts = false;
app.Visible = false;
int nSheetIdx = 1;
book = app.Workbooks.Add(Missing.Value);
///列数
int nColumnCnt = 0;
///行数
int nRowCnt = 0;
///当前列索引
int nColumnIdx = 0;
///当前行索引
int nRowIdx = 0;
///Sheet表的个数
int nSheetCnt = 0; //Sheet Number of Same Table
int nSheetRowCnt = 0; //Sheet Row Count
int nSheetRowIdx = 0; //Sheet Row Index
string strValue = " ";
nColumnCnt = dt.Columns.Count;
if (nColumnCnt > 255) nColumnCnt = 255;
nRowCnt = dt.Rows.Count;
if (nRowCnt % MAX_EXCEL_ROW == 0)
nSheetCnt = nRowCnt / MAX_EXCEL_ROW;
else
nSheetCnt = nRowCnt / MAX_EXCEL_ROW + 1;
string[,] strColumns = new string[1, nColumnCnt];
//把列标题存放在数组中。
for (nColumnIdx = 0; nColumnIdx < nColumnCnt; nColumnIdx++)
strColumns[0, nColumnIdx] = dt.Columns[nColumnIdx].ColumnName;
for (int nTableSheetIdx = 0; nTableSheetIdx < nSheetCnt; nTableSheetIdx++)
{
if (nSheetIdx > book.Worksheets.Count)
book.Worksheets.Add(Missing.Value, book.Worksheets.get_Item(nSheetIdx - 1), 1, Missing.Value);
sheet = (Excel.Worksheet)book.Worksheets.get_Item(nSheetIdx);
if (nTableSheetIdx == 0)
if (dt.TableName == "")
{
sheet.Name = "Sheet" + (nTableSheetIdx + 1).ToString();
}
else
{
sheet.Name = dt.TableName;
}
else
if (dt.TableName == "")
{
sheet.Name = (nTableSheetIdx + 1).ToString();
}
else
{
sheet.Name = dt.TableName + "_ " + nTableSheetIdx.ToString();
}
nSheetIdx++;
报告的标题
rng = sheet.get_Range("A1 ", Missing.Value);
rng = rng.get_Resize(1, nColumnCnt);
rng.Font.Bold = true;
rng.set_Value(Missing.Value, strColumns);
rng.EntireColumn.AutoFit();
if (nRowCnt == 0) continue;
rng = sheet.get_Range("A2 ", Missing.Value);
if (nTableSheetIdx == 0)
{
if (nRowCnt > MAX_EXCEL_ROW)
nSheetRowCnt = MAX_EXCEL_ROW;
else
nSheetRowCnt = nRowCnt;
}
else if (nTableSheetIdx < (nSheetCnt - 1))
nSheetRowCnt = MAX_EXCEL_ROW;
else
nSheetRowCnt = nRowCnt - nTableSheetIdx * MAX_EXCEL_ROW;
//获得range的区域
rng = rng.get_Resize(nSheetRowCnt, nColumnCnt);
//定义对象数组,用来存放从数据库中取出来的数。最终写到Excel文件中。
object[,] objValues = new object[nSheetRowCnt, nColumnCnt];
for (nRowIdx = 0 + nTableSheetIdx * MAX_EXCEL_ROW; nRowIdx < (1 + nTableSheetIdx) * MAX_EXCEL_ROW; nRowIdx++)
{
if (nRowIdx == nRowCnt) break;
nSheetRowIdx = nRowIdx - nTableSheetIdx * MAX_EXCEL_ROW;
for (nColumnIdx = 0; nColumnIdx < nColumnCnt; nColumnIdx++)
{
if (dt.Rows[nRowIdx][nColumnIdx] != System.DBNull.Value)
{
strValue = dt.Rows[nRowIdx][nColumnIdx].ToString();
objValues[nSheetRowIdx, nColumnIdx] = strValue;
}
}
}
rng.set_Value(Missing.Value, objValues);
}
}
catch (System.Exception ex)
{
bExp = true;
throw ex;
}
finally
{
if (book != null)
{
if (bExp)
book.Close(false, Missing.Value, Missing.Value);
else
book.SaveAs(strFileName, Excel.XlFileFormat.xlExcel9795, Missing.Value, Missing.Value, false,
false, Excel.XlSaveAsAccessMode.xlNoChange,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
if (app != null)
{
app.Quit();
app = null;
}
}
}
效率的话那就至少高了一个等级,俗话说喝水不忘挖井人,但是我却不记得是哪位高手写的了,呵呵不好意思啊!谁知道原著请提醒下我加上去