关于操作Excel多个Sheet的问题
public static void ExportExcel(string TemplatePath, Hashtable hashtable)
{
string strFileName = HttpContext.Current.Server.MapPath(TemplatePath);
beforeTime = DateTime.Now;
Object oMissing = System.Reflection.Missing.Value;
Excel.Application ex = new Excel.Application();
ex.DisplayAlerts = false;
ex.ScreenUpdating = false;
ex.Workbooks.Open(strFileName, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
Excel.Workbook workbook = ex.Application.ActiveWorkbook;
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[1];//from 1 not 0
foreach (object key in hashtable.Keys)
{
if (hashtable[key].GetType() == typeof(DataTable))
{
DataTable dataTable = (DataTable)hashtable[key];
int nRowCount = dataTable.Rows.Count;
int nColumnCount = dataTable.Columns.Count;
#region 如果超過2000行 將截斷DataTable
if (nRowCount > 2000)
{
for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
{
if (rowIndex > 2000)
{
dataTable.Rows.RemoveAt(rowIndex);
rowIndex--;
}
}
}
#endregion
Excel.Range range = worksheet.Cells.Find("$" + key + "$", oMissing, oMissing, Excel.XlLookAt.xlWhole, oMissing, Excel.XlSearchDirection.xlNext, oMissing, oMissing, oMissing);
int startRowIndex = range.Row;
object[,] valueRange = new object[nRowCount, nColumnCount];
int i = 0;
int j = 0;
Excel.Range row;
foreach (DataRow dataRow in dataTable.Rows)
{
row = (Excel.Range)worksheet.Rows[startRowIndex + 1, oMissing];
row.Insert(oMissing, oMissing);
row = (Excel.Range)worksheet.Rows[startRowIndex, oMissing];
row.Copy(oMissing);
row = (Excel.Range)worksheet.Rows[startRowIndex + 1, oMissing];
row.PasteSpecial(Excel.XlPasteType.xlPasteFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationAdd, oMissing, oMissing);
j = 0;
foreach (DataColumn dataColumn in dataTable.Columns)
{
//if (dataColumn.DataType == typeof(DateTime))
//{
// if (dataRow[dataColumn] != DBNull.Value)
// valueRange[i, j] = ((DateTime)dataRow[dataColumn]).ToShortDateString();
//}
//else
valueRange[i, j] = dataRow[dataColumn];
j++;
}
i++;
}
Excel.Range rowDelete = (Excel.Range)worksheet.Rows[startRowIndex + nRowCount, oMissing];
rowDelete.Delete(oMissing);
//j = 1;
//foreach (DataColumn dataColumn in dataTable.Columns)
//{
// if (dataColumn.DataType == typeof(string))
// worksheet.get_Range(worksheet.Cells[startRowIndex, j], worksheet.Cells[startRowIndex + nRowCount - 1, j]).NumberFormat = "@";
// j++;
//}
worksheet.get_Range(worksheet.Cells[startRowIndex, 1], worksheet.Cells[startRowIndex + nRowCount - 1, nColumnCount]).Value2 = valueRange;
}
else
{
//当是图片时，限定参数标式有pic..
if (key.ToString().ToLower().IndexOf("pic") < 0)
{
worksheet.Cells.Replace("$" + key + "$", hashtable[key], Excel.XlLookAt.xlPart, oMissing, oMissing, oMissing, oMissing, oMissing);
}
else
{
if (hashtable[key].ToString() != "")
{
Excel.Range range = worksheet.Cells.Find("$" + key + "$", oMissing, oMissing, Excel.XlLookAt.xlWhole, oMissing, Excel.XlSearchDirection.xlNext, oMissing, oMissing, oMissing);
range.Select();
int row = range.Row;
int col = range.Column;
string ColumnName = returnExcelColumnName(col) + row.ToString();
InsertPicture(range, worksheet, ColumnName, hashtable[key].ToString());
range.Cells.Value2 = "";
}
else
{
worksheet.Cells.Replace("$" + key + "$", hashtable[key], Excel.XlLookAt.xlPart, oMissing, oMissing, oMissing, oMissing, oMissing);
}
}
}
}
afterTime = DateTime.Now;
string strFileName1 = Path.GetFileNameWithoutExtension(strFileName);
strFileName1 += "_" + DateTime.Now.ToString("yyyy-MM-dd-hh") + Path.GetExtension(strFileName);
string strExportExcel = HttpContext.Current.Server.MapPath("~/log/ExportExcel/" + DateTime.Today.ToString("yyyyMM"));
if (!Directory.Exists(strExportExcel))
Directory.CreateDirectory(strExportExcel);
strFileName = strExportExcel + "\" + strFileName1;
workbook.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal, oMissing, oMissing, oMissing, oMissing, Excel.XlSaveAsAccessMode.xlExclusive, oMissing, oMissing, oMissing, oMissing, oMissing);
workbook.Close(oMissing, oMissing, oMissing);
//string strValue1="";
//Excel.XmlMap aaa =worksheet. new ;
//workbook.SaveAsXMLData("", aaa);
//Excel.XlXmlExportResult bbb = aaa.ExportXml(out strValue1);
try
{
//将数字格式化为金额(要格式化的单元格内的值必须为数值型)
//xlsheet.get_Range(xlsheet.Cells[2, 1], xlsheet.Cells[10, 15]).set_NumberFormat("000000");
//xlsheet.get_Range(xlsheet.Cells[2, 1], xlsheet.Cells[10, 15]).set_NumberFormat("￥#,##0.00");
//xlsheet.Export(@"D:\WorldVision\source\WorldVision\WVWeb\_TestOWC.xls", SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportXMLSpreadsheet);
DownFile(HttpContext.Current.Response, strFileName1, strFileName);
//SetResponse(strFileName, "");//用这个会报错catch中
}
catch
{
KillExcelProcess();
throw;
}
finally
{
if (ex != null)
{
ex.Application.Workbooks.Close();
ex.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ex);
ex = null;
GC.Collect();
KillExcelProcess();
}
}
}
excel C#? Sheet
ActiveWorkbook.Sheets.Add方法，有四个参数，分别是：
Before：Variant 类型，可选。指定工作表对象，新建的工作表将置于此工作表之前。
After：Variant 类型，可选。指定工作表对象，新建的工作表将置于此工作表之后。
Count：Variant 类型，可选。要新建的工作表的数目。默认值为 1。
Type：Variant 类型，可选。指定工作表类型。
ActiveWorkbook.Sheets.Add方法使用公式如下：
ActiveWorkbook.Sheets.Add 参数:=参数值
如：ActiveWorkbook.Sheets.Add Count:=6 其功能是：在当前工作薄的前面，插入新的六个工作表。
二、通过ActiveWorkbook.Sheets.Add将新表插入到最后
根据一的基础，下面，我们来实现本文的难题：在任何时候，如何让新表插入到最后？
使用如下代码即可：
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
代码解释，after参数的含义是，在某某表的后面插入新表。Worksheets.Count的含义是，所有表的总数；
现在，完整的看这行代码：
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
其含义是，在当前所有表的总数(也就是最后一个表拉，因为最后一个表的序号刚好就是所有表的总数)的后面插入新表。
三、其它类似的代码
Ａ：ActiveWorkbook.Sheets.Add.Name = "工作表名称"；插入一个给定名称的工作表；
Ｂ：ActiveWorkbook.Sheets.Add.Name = Format(Date, "yy-mm-dd")；插入一个以年-月-日为名称的工作表；
Ｃ：ActiveWorkbook.Sheets.Add before:=Worksheets("Sheet1")；在Sheet1之前插入一个工作表；
Ｄ：综合运行的代码
ActiveWorkbook.Sheets.Add Before:=Worksheets("Sheet6")
ActiveSheet.Name = "我是刚插入的"
以上两行代码的功能是，指定新插入表出现在Sheet6的前面，并且，指定要使用我是刚插入的为表的名称
