public override void AutoSizeColumn(int column, string WorksheetName) { Microsoft.Office.Interop.Excel.Worksheet sheet = GetWorksheet(WorksheetName); string columnName = GetExcelColumnName(column + 1); Microsoft.Office.Interop.Excel.Range rng = sheet.get_Range(columnName + "1", columnName + "1"); Microsoft.Office.Interop.Excel.Range entireColumn = rng.EntireColumn; entireColumn.AutoFit(); ReleaseObject(sheet); ReleaseObject(rng); ReleaseObject(entireColumn); }
private string WriterExcel_XLS(string saveName, DataTable DT) { string msg = null; excelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.ActiveSheet; excelApp.Visible = false; try { int rowCount = DT.Rows.Count; //行数 int colCount = DT.Columns.Count; //列数 long pageRows = 65000; //定义每页显示的行数,行数必须小于65536 if (rowCount > pageRows) { int scount = (int)(rowCount / pageRows); //导出数据生成的表单数 if (scount * pageRows < rowCount) //当总行数不被pageRows整除时,经过四舍五入可能页数不准 { scount = scount + 1; } for (int sc = 1; sc <= scount; sc++) { object missing = System.Reflection.Missing.Value; excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.Worksheets.Add( missing, missing, missing, missing); excelSheet.Name = String.Format(@"实地核查-{0}", sc); object[,] datas = new object[pageRows + 1, colCount]; for (int i = 0; i < colCount; i++) { datas[0, i] = DT.Columns[i].ColumnName; } int init = int.Parse(((sc - 1) * pageRows).ToString()); int index = 0; int result; if (pageRows * sc >= rowCount) { result = (int)rowCount; } else { result = int.Parse((pageRows * sc).ToString()); } for (int j = init; j < result; j++) { index = index + 1; for (int i = 0; i < colCount; i++) { Type type = DT.Rows[j][i].GetType(); if (type == typeof(int) || type == typeof(float) || type == typeof(Decimal) || type == typeof(Double)) { datas[index, i] = Double.Parse(DT.Rows[j][i].ToString()); } else if (type == typeof(DateTime)) { datas[index, i] = DateTime.Parse(DT.Rows[j][i].ToString()).ToString("yyyy-MM-dd"); } else { datas[index, i] = DT.Rows[j][i].ToString(); } } } Microsoft.Office.Interop.Excel.Range fchR = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[index + 1, colCount]]; fchR.Value = datas; Microsoft.Office.Interop.Excel.Range allColumn = excelSheet.Columns; allColumn.AutoFit(); } } else { object[,] datas = new object[rowCount + 1, colCount]; for (int i = 0; i < colCount; i++) { datas[0, i] = DT.Columns[i].ColumnName; } int index = 1; for (int j = 0; j < rowCount; j++) { index = j + 1; for (int i = 0; i < colCount; i++) { Type type = DT.Rows[j][i].GetType(); if (type == typeof(int) || type == typeof(float) || type == typeof(Decimal) || type == typeof(Double))//值类型 { datas[index, i] = Double.Parse(DT.Rows[j][i].ToString()); } else if (type == typeof(DateTime))//类型 { datas[index, i] = DateTime.Parse(DT.Rows[j][i].ToString()).ToString("yyyy-MM-dd"); } else { datas[index, i] = DT.Rows[j][i].ToString(); } } } Microsoft.Office.Interop.Excel.Range range = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowCount + 1, colCount]]; range.Value = datas; Microsoft.Office.Interop.Excel.Range allColumn = excelSheet.Columns; allColumn.AutoFit(); } excelBook.SaveAs(saveName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); return(msg); } catch (Exception ex) { msg = ex.Message; return(msg); } finally { this.EndReport(); } }
/// <summary> /// 导出DataSet数据到Excel /// </summary> /// <param name="dataSet">数据源(要导出的数据源,类型应是DataSet)</param> /// <param name="filePath">保存文件的路径 例:'C:\\Files\\'</param> /// <param name="fileName">要保存的文件名,需要加扩展有.xls 例:'Test.xls'</param> public static void ExportToExcel(DataSet dataSet, string filePath, string fileName, Page page) { string SavaFilesPath = filePath + fileName; if (dataSet.Tables.Count == 0) { throw new Exception("DataSet中没有任何可导出的表。"); } //建立一个Excel进程 Application Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application(); //默认值为 True。如果不想在宏运行时被无穷无尽的提示和警告消息所困扰,请将本属性设置为 False;这样每次出现需用户应答的消息时,Microsoft Excel // 将选择默认应答。 //如果将该属性设置为 False,则在代码运行结束后,Micorosoft Excel 将该属性设置为 True,除非正运行交叉处理代码。 //如果使用工作簿的 SaveAs 方法覆盖现有文件,“覆盖”警告默认为“No”,当 DisplayAlerts 属性值设置为 True 时,Excel 选择“Yes”。 excelApplication.DisplayAlerts = false; // 建立或打开一个 Workbook对象生成新Workbook Microsoft.Office.Interop.Excel.Workbook workbook = excelApplication.Workbooks.Add(Missing.Value); //删除原有的sheet while (workbook.Worksheets.Count > 1) { ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1)).Delete(); } bool isFrist = true; foreach (DataTable dt in dataSet.Tables) { Microsoft.Office.Interop.Excel.Worksheet lastWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(workbook.Worksheets.Count); Microsoft.Office.Interop.Excel.Worksheet newSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, lastWorksheet, Type.Missing, Type.Missing); if (isFrist == true) { isFrist = false; ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1)).Delete(); } //设置默认选中是第一个Sheet 类似于Select(); newSheet.Name = dt.TableName; ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1)).Activate(); //获取DataSet的列名,并创建标题行 for (int col = 0; col < dt.Columns.Count; col++) { newSheet.Cells[1, col + 1] = dt.Columns[col].ColumnName; //Microsoft.Office.Interop.Excel.Range targetRange = excelApplication.Range["A1"]; newSheet.Range[newSheet.Cells[1, col + 1], newSheet.Cells[1, dt.Columns.Count]].Columns.AutoFit(); //newSheet //Microsoft.Office.Interop.Excel.Range firstColumn = newSheet.get_Range(dt.Columns[col].ColumnName); ////Range firstColumn = (Range)xlWorkSheet.Columns[0]; //firstColumn.EntireColumn.AutoFit(); } //通过循环把数据添加到Sheet里面 for (int row = 0; row < dt.Rows.Count; row++) { for (int col = 0; col < dt.Columns.Count; col++) { newSheet.Cells[row + 2, col + 1] = dt.Rows[row][col].ToString(); } } Microsoft.Office.Interop.Excel.Range allColumn = newSheet.Columns; allColumn.AutoFit(); allColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; } //删除原来的空Sheet //((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1)).Delete(); // ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1)).Delete(); // ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1)).Delete(); //设置默认选中是第一个Sheet 类似于Select(); ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1)).Activate(); try { workbook.Close(true, SavaFilesPath, System.Reflection.Missing.Value); } catch (Exception e) { throw e; } UploadExcel(SavaFilesPath, page, true); excelApplication.Quit(); KillExcel(); }