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();
}
