导出任意形式Excel(共享心得)

參考: http://topic.csdn.net/u/20091113/10/77a993dc-af9a-46a3-ab99-0c5050977d93.html?81131

 

C# code

 /// <summary>    /// 生成EXCEL文件    /// _til是表格的标题，_row是表格的行数,_col是表格的列数,_dt是数据表,_filename是保存的EXCEL表的名称    /// </summary>    public void SaveToExcel(string _til, int _row, int _col, DataTable _dt, string _filename)    {        Microsoft.Office.Interop.Owc11.SpreadsheetClass xlsheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();        //合并单元格        xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, _col]).set_MergeCells(true);        // xlsheet.get_Range(xlsheet.Cells[2, 1], xlsheet.Cells[3, 2]).set_MergeCells(true);        xlsheet.ActiveSheet.Cells[1, 1] = _til;        //字体加粗        xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, _col]).Font.set_Bold(true);        //单元格文本水平居中对齐        xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[_row + 2, _col]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);        //设置字体大小        xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, _col]).Font.set_Size(14);        //设置列宽        //xlsheet.get_Range(xlsheet.Cells[1, 3], xlsheet.Cells[1, 3]).set_ColumnWidth(50);        //画边框线        xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[_row + 2, _col]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);        //定义一个2维数组用来存储DATATABLE里的数据        object[,] dataArray = new object[_row, _col];        //添加列名        for (int k = 0; k < _dt.Columns.Count; k++)        {            xlsheet.ActiveSheet.Cells[2, k + 1] = _dt.Columns[k].Caption.ToString();        }        //把DATATABLE里的数据导到2维数组中        for (int i = 0; i < _row; i++)        {            for (int j = 0; j < _col; j++)            {                dataArray[i, j] = _dt.Rows[i][j];            }        }        //把2维数组中的数据导到EXCEL中        xlsheet.get_Range("A3", xlsheet.Cells[_row + 2, _col]).Value2 = dataArray;        xlsheet.Export(_filename, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);    }    /// <summary>    /// 导入EXCEL表    /// </summary>    /// <param name="strFileName"></param>    /// <returns></returns>    public DataSet CreateDataSource(string strFileName, string Sqlstr)    {        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + "; Extended Properties=Excel 8.0;";        OleDbConnection olecon = new OleDbConnection(strCon);        OleDbDataAdapter myda = new OleDbDataAdapter(Sqlstr, olecon);        DataSet myds = new DataSet();        myda.Fill(myds);        return myds;    }