62,046
社区成员
发帖
与我相关
我的任务
分享
public void exportTest(DataView dv1, string filename, string sheetname)
{
try
{
Excel.Application appExcel = new Excel.Application();
Excel.Workbook workbookData;
Excel.Worksheet worksheetData;
workbookData = appExcel.Workbooks.Add(Missing.Value);
worksheetData = (Excel.Worksheet)workbookData.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);//添加一个sheet
worksheetData.Name = sheetname;//sheet命名
int rowIndex = 1;
int colIndex = 1;
foreach (DataColumn col in dv1.Table.Columns)
{
worksheetData.get_Range(appExcel.Cells[rowIndex, colIndex], appExcel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
worksheetData.get_Range(appExcel.Cells[rowIndex, colIndex], appExcel.Cells[rowIndex, colIndex]).Font.Bold = true;
appExcel.Cells[rowIndex, colIndex++] = col.ColumnName;
}
int drvIndex;
for (drvIndex = 0; drvIndex < dv1.Count; drvIndex++)
{
DataRowView row = dv1[drvIndex];
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dv1.Table.Columns)
{
if (col.DataType == System.Type.GetType("System.DateTime"))
{
if (!"".Equals(row[col.ColumnName].ToString()))
appExcel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("MM/dd/yyyy");
else
appExcel.Cells[rowIndex, colIndex] = "";
}
else if (col.DataType == System.Type.GetType("System.String"))
{
appExcel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
}
else
{
appExcel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
colIndex++;
}
}
Range allDataWithTitleRange = worksheetData.get_Range(appExcel.Cells[1, 1], appExcel.Cells[rowIndex, colIndex - 1]);
allDataWithTitleRange.Select();
allDataWithTitleRange.Columns.AutoFit();
if (true)
{
allDataWithTitleRange.Borders.LineStyle = 1;
}
string absFileName = HttpContext.Current.Server.MapPath(System.IO.Path.Combine("~/Attachment", filename));
workbookData.SaveCopyAs(absFileName);
workbookData.Close(false, null, null);
appExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookData);
System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheetData);
workbookData = null;
appExcel = null;
worksheetData = null;
GC.Collect();
}
catch (Exception ex)
{ }
}
private void DataViewExcelBySheetMultipleDt(DataSet ds, string fileName)
{
try
{
int sheetCount = ds.Tables.Count;
GC.Collect();
Application excel;
_Workbook xBk;
_Worksheet xSt = null;
excel = new ApplicationClass();
xBk = excel.Workbooks.Add(true);
int rowIndex = 0;
int colIndex = 0;
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
rowIndex = 1;
colIndex = 1;
xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
switch (sheetIndex)
{
case 0:
xSt.Name = "test1";
break;
case 1:
xSt.Name = "test2";
break;
case 2:
xSt.Name = "test3";
break;
case 3:
xSt.Name = "test4";
break;
}
foreach (DataColumn col in ds.Tables[sheetIndex].Columns)
{
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true;
excel.Cells[rowIndex, colIndex++] = col.ColumnName;
}
foreach (DataRow row in ds.Tables[sheetIndex].Rows)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in ds.Tables[sheetIndex].Columns)
{
if (col.DataType == System.Type.GetType("System.DateTime"))
{
if (!"".Equals(row[col.ColumnName].ToString()))
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("MM/dd/yyyy");
else
excel.Cells[rowIndex, colIndex] = "";
}
else if (col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
colIndex++;
}
}
Range allDataWithTitleRange = xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex - 1]);
allDataWithTitleRange.Select();
allDataWithTitleRange.Columns.AutoFit();
allDataWithTitleRange.Borders.LineStyle = 1;
}
string exportDir = "~/Attachment/";
string absFileName = HttpContext.Current.Server.MapPath(System.IO.Path.Combine(exportDir, fileName));
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;
GC.Collect();
}
catch (Exception ex)
{
}
}
workbookData = appExcel.Workbooks.Add(Missing.Value);
if ("1".Equals(existfile))
workbookData = appExcel.Workbooks.Open(path, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "t", false, false, 0, true, Type.Missing, Type.Missing);
else
worksheetData = (Excel.Worksheet)workbookData.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);//添加一个sheet
worksheetData.Name = sheetname;//sheet命名