导入Excel和导出Excel的简单方法与程序处理方法,使用Excel程序读写Excel ，实现Excel的多个 Sheets读写并导出
导入Excel:
(1)
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]
(2)
SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])
(3)
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
导出Excel:
((DataTable)ViewState["DT"];这个为数据源)
#region 汇出EXCEL
/// <summary>
/// 汇出EXCEL
/// </summary>
/// <returns>返回true则汇出成功</returns>
private bool ExportExcel()
{
try
{
Response.ClearContent();
Response.Charset = "UTF8";
Response.AddHeader("content-disposition", "attachment; filename=PMTM02.xls");
Response.ContentType = "application/excel";
Response.ContentEncoding = System.Text.Encoding.UTF7;
StringWriter o_sw = new StringWriter();
HtmlTextWriter o_htw = new HtmlTextWriter(o_sw);
GridView o_GridView = new GridView();
o_GridView = this.grvQuery;
o_GridView.RowDataBound += new GridViewRowEventHandler(o_GridView_RowDataBound);
o_GridView.DataSource = (DataTable)ViewState["DT"];
o_GridView.AllowPaging = false;
o_GridView.DataBind();
o_GridView.RenderControl(o_htw);
Response.Write(o_sw.ToString());
Response.End();
return true;
}
catch (Exception ex)
{
ex.Message.ToString();
return false;
}
}
#endregion
使用Excel程序读写Excel ，实现Excel的多个 Sheets读写并导出：
注：首先添加Excel的组件Dll
方法一：
protected void btnXMLtoEXCEL_Click(object sender, EventArgs e)
{
string[] FileList = GetFileList("F:\\cc\\Excel\\XML\\", "*.xml");
int s_len = FileList.Length;
if (s_len > 0)
{
for (int i = 0; i < s_len; i++)//遍历路径下的所有XML
{
XMLtoEXCEL(FileList[i].ToString());
}
}
}
private void XMLtoEXCEL(string s_aPath)
{
#region 初始化数据集
DataSet ds = new DataSet();
//FileStream fs = new FileStream(Server.MapPath(s_aPath), FileMode.Open, FileAccess.Read);
FileStream fs = new FileStream(s_aPath, FileMode.Open, FileAccess.Read);
StreamReader reader = new StreamReader(fs);
ds.ReadXml(reader);
fs.Close();
#endregion
//根据Tbl数初始WorkSheet的数量：
int ds_dtcount = ds.Tables.Count;
Excel.Application o_excel = new Excel.Application();
//o_excel.SheetsInNewWorkbook = 3;//ds_dtcount
//o_excel.Workbooks.Add(Type.Missing);//新建
//xlWorkbook = (Excel._Workbook)(xlApp.Workbooks.Add(@"D:\temp\aa.xls"));//读取
o_excel.Workbooks.Add(@"C:\cc.xls");//读取
int i_Count = ds.Tables.Count;//这里是新增的dt数目
for (int i_dt = 1; i_dt <= i_Count; i_dt++)
{
o_excel.Workbooks[1].Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet o_Worksheet = (Excel.Worksheet)o_excel.Workbooks[1].Worksheets[1];
o_Worksheet.Name = ds.Tables[i_dt - 1].TableName;//"KcSheets2" + i_dt;
o_Worksheet.Activate();
System.Data.DataTable dt = new System.Data.DataTable();
dt = ds.Tables[i_dt - 1];
//从dt填充数据仿真
#region 填充数据仿真
if (dt == null) return;
Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
//写入字段
for (int i = 0; i < dt.Columns.Count; i++)
{
o_Worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
range = (Excel.Range)o_Worksheet.Cells[2, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
//写入数值
//this.CaptionVisible = true;
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
o_Worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i];
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
#endregion
}
o_excel.Visible = true;
//o_excel.Quit();
GC.Collect();//强行销毁
}
protected void btnXMLtoEXCEL2_Click(object sender, EventArgs e)
{
string[] FileList = GetFileList("C:\\xml\\", "*.xml");
int s_len = FileList.Length;
if (s_len > 0)
{
//for (int i = 0; i < s_len; i++)//遍历路径下的所有XML
//{
XMLtoEXCEL2(FileList);
//}
}
}
方法二：
private void XMLtoEXCEL2(string[] s_aFileList)
{
#region 初始化数据集
int s_len = s_aFileList.Length;
DataSet ds = new DataSet();
string dt_Name = string.Empty;//打印dt的名
int i_dtcopy = 0;//计算dt的数目
for (int i = 0; i < s_len; i++)//遍历路径下的所有XML
{
DataSet ds_each = new DataSet();
FileStream fs = new FileStream(s_aFileList[i], FileMode.Open, FileAccess.Read);
StreamReader reader = new StreamReader(fs);
ds_each.ReadXml(reader);
fs.Close();
int ds_eachcount = ds_each.Tables.Count;
for (int i_Sencond = 0; i_Sencond < ds_eachcount; i_Sencond++)
{
System.Data.DataTable dt_add = new System.Data.DataTable();
dt_add = ds_each.Tables[i_Sencond];
//这里还需要处理一下同表的情况是mercy还不copy
Boolean is_Copy = true;
for (int i_third = 0; i_third < ds.Tables.Count; i_third++)
{
if (dt_add.TableName.Equals(ds.Tables[i_third].TableName))
is_Copy = false;
}
if (is_Copy)
{
ds.Tables.Add(dt_add.Copy());
i_dtcopy++;//辅助
if (i_dtcopy % 5 == 0)
{
dt_Name = dt_Name + "," + dt_add.TableName + "<br>";//辅助
}
else
{
dt_Name = dt_Name + "," + dt_add.TableName;//辅助
}
}
}
}
Response.Write("共"+i_dtcopy.ToString()+"表<br>");
Response.Write("表名如下(一行五个)：<br>" + dt_Name.ToString().Trim().Substring(0, dt_Name.Length - 1) + "<br>表");
#endregion
//根据Tbl数初始WorkSheet的数量：
int ds_dtcount = ds.Tables.Count;
Excel.Application o_excel = new Excel.Application();
//o_excel.SheetsInNewWorkbook = 1;//ds_dtcount
//o_excel.Workbooks.Add(Type.Missing);//新建
//o_excel.Workbooks[1].Worksheets.Delete();//删除工作表，不能删除最后一个工作表
o_excel.Workbooks.Add(@"C:\JX07102byKangco.xls");//读取
int i_Count = ds.Tables.Count;//这里是新增的dt数目
for (int i_dt = 1; i_dt <= i_Count; i_dt++)
{
o_excel.Workbooks[1].Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet o_Worksheet = (Excel.Worksheet)o_excel.Workbooks[1].Worksheets[1];
o_Worksheet.Name = ds.Tables[i_dt - 1].TableName;//"KcSheets2" + i_dt;
o_Worksheet.Activate();
System.Data.DataTable dt = new System.Data.DataTable();
dt = ds.Tables[i_dt - 1];
//从dt填充数据仿真
#region 填充数据仿真
if (dt == null) return;
Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
//写入字段
for (int i = 0; i < dt.Columns.Count; i++)
{
o_Worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Excel.Range)o_Worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
//写入数值
//this.CaptionVisible = true;
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
o_Worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
#endregion
}
o_excel.Visible = true;
//o_excel.Quit();
GC.Collect();//强行销毁
}
