/// <summary>
/// 导出EXCEL
/// </summary>
/// <param name="ds"></param>
/// <param name="fileName"></param>
public static void ExportToExcel(DataSet ds, string fileName)
{
OleDbConnection conn = null;
try
{
//加工文件名
string[] strTemp = fileName.Split('\\');
string NewName = string.Empty;
foreach (string s in strTemp)
{
NewName = NewName + "\\\\" + s;
}
NewName = NewName.TrimStart('\\');
if (ds.Tables.Count == 0)
{
return;
}
Excel._Application xlApp = new Excel.ApplicationClass();
Excel._Workbook xlBook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
if (ds.Tables.Count > 1)
{
int i = ds.Tables.Count;
while (i > 1)
{
xlBook.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
i--;
}
}
int loop = 1;
foreach (DataTable dt2 in ds.Tables)
{
FillExcelCaption((Excel._Worksheet)xlBook.Worksheets[loop], dt2);
loop++;
}
xlBook.SaveCopyAs(fileName);
xlBook.Saved = true;
xlApp.Quit();
Kill((Excel.Application)xlApp);
//取数据表
//string con = string.Format("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = {0}; Extended Properties = Excel 8.0", NewName);
//conn = new OleDbConnection(con);
//conn.Open();
ExcelConn(NewName, ref conn);
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
foreach (DataTable dt in ds.Tables)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat("INSERT INTO [{0}$] (", dt.TableName);
for (int i = 0; i < dt.Columns.Count; i++)
{
if (!string.IsNullOrEmpty(dt.Columns[i].Caption))
{
sql.AppendFormat("[{0}],", dt.Columns[i].Caption);
}
}
sql.Remove(sql.Length - 1, 1);
sql.Append(") VALUES ");
string head = sql.ToString();
for (int j = 0; j < dt.Rows.Count; j++)
{
sql = new StringBuilder();
sql.Append(head);
sql.Append(" (");
for (int k = 0; k < dt.Columns.Count; k++)
{
if (!string.IsNullOrEmpty(dt.Columns[k].Caption))
{
if (dt.Columns[k].DataType.Equals(typeof(String)) ||
dt.Columns[k].DataType.Equals(typeof(DateTime)) ||
dt.Columns[k].DataType.Equals(typeof(Char)))
{
sql.AppendFormat("'{0}'", dt.Rows[j][k].ToString());
}
else
{
sql.AppendFormat("{0}", dt.Rows[j][k].ToString().Trim().Equals(string.Empty) ? 0 : Convert.ToDecimal(dt.Rows[j][k]));
}
sql.Append(",");
}
}
sql.Remove(sql.Length - 1, 1);
sql.Append(") ");
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
/*<summary>
* 将DataTable中的数据导出到Excel中，并在服务器端AppData文件夹中生成xls文件
* </summary>
* <param name="dt">要导出数据的DataTable</param>
* <param name="head">题头数据</param>
* <param name="absFileName">文件的绝对路径</param>
* <returns></returns>
*/
public static void ExportToExcelDiagramfor9(System.Data.DataTable dt, string[] head, string absFileName)
{
//设置多少行为一个Sheet
int RowsToDivideSheet = 65535;
//计算Sheet数
int sheetCount = (dt.Rows.Count - 1) / RowsToDivideSheet + 1;
GC.Collect();
string excelTemplateDPath; //EXCEL模板默认服务器物理存放路径
string tempEFilePath = "\\TempFiles"; //EXCEL临时文件保存服务器物理存放路径
string tempEFileXPath = "/TempFiles"; //EXCEL临时文件保存服务器虚拟存放路径
Excel.Application excel = null;
Excel._Workbook xBk = null;
Excel._Worksheet xSt = null;
Excel.Workbooks workbooks; //工作簿集合
Excel.Sheets sheets; //SHEET页集合
#region
//读取配置文件中路线模板路径及名称并验证是否存在 //获取配置文件中路线模板路径及名称
excelTemplateDPath = System.Web.HttpContext.Current.Server.MapPath("~/moban");
tempEFileXPath = "~/moban" + tempEFileXPath;
tempEFilePath = excelTemplateDPath + tempEFilePath;
excelTemplateDPath += "\\TaskTemplet9.xls";
//验证EXCEL临时文件夹是否存在
if (!File.Exists(tempEFilePath))
{
Directory.CreateDirectory(tempEFilePath);
}
#endregion
try
{
#region
//启动excel进程并加载模板
//启动EXCEL进程
excel = new Excel.Application();
excel.Visible = false;
excel.UserControl = true;
excel.DisplayAlerts = false;
//加载读取模板
workbooks = excel.Workbooks;
xBk = workbooks.Add(excelTemplateDPath);
sheets = xBk.Worksheets;
xSt = (Excel._Worksheet)sheets.get_Item(1);
#endregion
//循环中要使用的变量
int dvRowStart;
int dvRowEnd;
//对全部Sheet进行操作
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
//计算起始行
dvRowStart = sheetIndex * RowsToDivideSheet;
dvRowEnd = dvRowStart + RowsToDivideSheet - 1;
if (dvRowEnd > dt.Rows.Count - 1)
{
dvRowEnd = dt.Rows.Count - 1;
}
//创建一个Sheet
if (null == xSt)
{
xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
}
else
{
xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);
}
//设置SheetName
xSt.Name = "Excel";
if (sheetCount > 1)
{
xSt.Name += ((int)(sheetIndex + 1)).ToString();
}
//题头导出
int rowCount = head.Length;
int colCount = 1;
object[,] dataArray = new object[colCount, rowCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray[j, i] = head[i];
}
}
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).NumberFormatLocal = "@"; //设置单元格格式为文本
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Value2 = dataArray;
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Name = "Arial";
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Size = 10;
//数据导出
System.Data.DataTable SheetTable = new System.Data.DataTable();
foreach (DataColumn dc in dt.Columns)
{
DataColumn newdc = new DataColumn();
newdc.ColumnName = dc.ColumnName;
newdc.DataType = dc.DataType;
SheetTable.Columns.Add(newdc);
}
for (int drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++)
{
SheetTable.ImportRow(dt.Rows[drvIndex]);
}
//保存数据
rowCount = SheetTable.Rows.Count;
colCount = SheetTable.Columns.Count;
object[,] dataArray1 = new object[rowCount, colCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray1[i, j] = SheetTable.Rows[i][j];
}
}
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).NumberFormatLocal = "0.00_ "; //保留小数位数为2;
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Value2 = dataArray1;
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Name = "Arial";
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Size = 10;
//单元格边框
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).Borders.LineStyle = 1;
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置居中对齐
xSt.Columns.AutoFit(); //自适应宽度
//设置图标的标题
string Charttitle = dt.Rows[0][0].ToString();
}
//删除Sheet1
excel.DisplayAlerts = false; //注意一定要加上这句
((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete();
excel.DisplayAlerts = true; //注意一定要加上这句
object objOpt = System.Reflection.Missing.Value;
excel.Visible = false;
xBk.SaveCopyAs(absFileName);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
}
catch (Exception e)
{
//throw (e);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
}
}
