Application m_xlsApp=new Application();
object objOpt = System.Reflection.Missing.Value;
Workbooks wbs = m_xlsApp.Workbooks;
Workbook m_Workbook = null;
if (wbs != null && wbs.Count > 0)
{
m_Workbook = wbs.get_Item(filename);
}
else
{
m_Workbook = m_xlsApp.Workbooks.Open(strExcelPath, objOpt, true, objOpt, objOpt, objOpt, true, objOpt, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt);
}
Worksheet m_Worksheet = (Worksheet)m_Workbook.Worksheets.get_Item(strSheetName);
m_Range = m_Worksheet.get_Range(strUnitPosition, objOpt);
if (m_Range.Value2 != null)
{
strUnitValue = m_Range.Value2.ToString();//取值或者 strUnitValue = m_Range.Text.ToString();
strAccount = m_Range.Formula.ToString();//去计算公式
}
2.创建Excel文件
ExcelHelperRadio eh = new ExcelHelperRadio(templetFile, outFile);
eh.DataTableToExcel(dt, 1000, 6, 2, "Sheet1");
outFile = outFile.Substring(outFile.LastIndexOf("\\") + 1);
GoTo("Temp/" + outFile);
public ExcelHelper(string templetFilePath, string outputFilePath)
{
if (templetFilePath == null)
throw new Exception("Excel模板文件路径不能为空！");
if (outputFilePath == null)
throw new Exception("输出Excel文件路径不能为空！");
if (!File.Exists(templetFilePath))
throw new Exception("指定路径的Excel模板文件不存在！");
this.templetFile = templetFilePath;
this.outputFile = outputFilePath;
}
public void DataTableToExcel(DataTable dt, int rows, int top, int left, string sheetPrefixName)
{
int rowCount = dt.Rows.Count; //源DataTable行数
int colCount = dt.Columns.Count; //源DataTable列数
int sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
DateTime beforeTime;
DateTime afterTime;
if (sheetPrefixName == null || sheetPrefixName.Trim() == "")
sheetPrefixName = "Sheet1";
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
Excel.Application app = new Excel.ApplicationClass();
app.Visible = true;
afterTime = DateTime.Now;
//打开模板文件，得到WorkBook对象
Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet对象
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
////复制sheetCount-1个WorkSheet对象
//for(int i=1;i<sheetCount;i++)
//{
// ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
//}
//#region 将源DataTable数据写入Excel
//for(int i=1;i<=sheetCount;i++)
//{
//int startRow = (i - 1) * rows; //记录起始行索引
//int endRow = i * rows; //记录结束行索引
////若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
//if(i == sheetCount)
// endRow = rowCount;
////获取要写入数据的WorkSheet对象，并重命名
//Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
//sheet.Name = sheetPrefixName + "-" + i.ToString();
//将dt中的数据写入WorkSheet
int j = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataTable dtRows = RadioDeclareInfo.GetApplyByDeclareID(dt.Rows[i]["RadioDeclare_ID"].ToString());
int countRows = dtRows.Rows.Count;
workSheet.Cells[i + 2, 1] = dt.Rows[i]["RadioDeclare_ID"].ToString();
if (Convert.ToInt32(dt.Rows[i]["RadioDeclare_RadioNO"].ToString().Substring(3, 2)) >= 11 && Convert.ToInt32(dt.Rows[i]["RadioDeclare_RadioNO"].ToString().Substring(3, 2)) <= 56)
{
workSheet.Cells[i + 2, 2] = "街道/地区";
}
else
{
workSheet.Cells[i + 2, 2] = "委办局";
}
workSheet.Cells[i + 2, 3] = dt.Rows[i]["RadioDeclare_Street"].ToString();
workSheet.Cells[i + 2, 4] = dt.Rows[i]["RadioDeclare_Dept"].ToString();
workSheet.Cells[i + 2, 5] = dt.Rows[i]["RadioDeclare_RadioNO"].ToString();
workSheet.Cells[i + 2, 6] = dt.Rows[i]["RadioDeclare_DeptType"].ToString();
workSheet.Cells[i + 2, 7] = dt.Rows[i]["RadioDeclare_Address"].ToString();
workSheet.Cells[i + 2, 8] = dt.Rows[i]["RadioDeclare_User"].ToString();
workSheet.Cells[i + 2, 9] = dt.Rows[i]["RadioDeclare_Phone"].ToString();
workSheet.Cells[i + 2, 10] = dt.Rows[i]["RadioDeclare_Fax"].ToString();
workSheet.Cells[i + 2, 11] = dt.Rows[i]["RadioDeclare_Area"].ToString();
workSheet.Cells[i + 2, 12] = dt.Rows[i]["RadioDeclare_Num"].ToString();
workSheet.Cells[i + 2, 13] = dt.Rows[i]["RadioPay_Year2007"].ToString();
workSheet.Cells[i + 2, 14] = dt.Rows[i]["RadioDatum_SysCode"].ToString();
workSheet.Cells[i + 2, 15] = dt.Rows[i]["RadioDatum_LicenceCode"].ToString();
workSheet.Cells[i + 2, 16] = dt.Rows[i]["RadioConst_Frequency1"].ToString();
workSheet.Cells[i + 2, 17] = dt.Rows[i]["RadioConst_Frequency2"].ToString();
workSheet.Cells[i + 2, 18] = dt.Rows[i]["RadioConst_Frequency3"].ToString();
workSheet.Cells[i + 2, 19] = dt.Rows[i]["RadioConst_Frequency4"].ToString();
workSheet.Cells[i + 2, 20] = dt.Rows[i]["RadioConst_Frequency5"].ToString();
for (int m = i; m < i + countRows; m++)
{
workSheet.Cells[m + 2, 21] = dt.Rows[m]["RadioMachine_Type"].ToString();
workSheet.Cells[m + 2, 22] = dt.Rows[m]["RadioMachine_LeaveCode"].ToString();
workSheet.Cells[m + 2, 23] = dt.Rows[m]["RadioMachine_Factory"].ToString();
workSheet.Cells[m + 2, 24] = dt.Rows[m]["RadioMachine_Remark"].ToString();
workSheet.Cells[m + 2, 25] = dt.Rows[m]["RadioMachine_Remark1"].ToString();
workSheet.Cells[m + 2, 26] = dt.Rows[m]["RadioMachine_Remark2"].ToString();
workSheet.Cells[m + 2, 27] = dt.Rows[m]["RadioMachine_Remark3"].ToString();
workSheet.Cells[m + 2, 28] = dt.Rows[m]["RadioMachine_Remark4"].ToString();
workSheet.Cells[m + 2, 29] = dt.Rows[m]["RadioMachine_BarCode"].ToString();
}
i = i + countRows - 1;
}
int maxid = RadioDeclareInfo.MaxID() + 1;
int maxNum=0;
string streetNO = "";
if (Session["Use_rName"] != null)
{
DataTable dtStreet=RadioAreaInfo.GetStreetByStreetName(Session["Use_rName"].ToString().Replace("街道", "").Replace("地区", "").Trim());
if (dtStreet != null && dtStreet.Rows.Count > 0)
{
streetNO = dtStreet.Rows[0]["RadioArea_ID"].ToString();
}
dtStreet.Dispose();
}
if (dt != null)
{
maxNum = RadioAreaInfo.GetMaxNO(dt.Rows[0]["RadioDeclare_Street"].ToString());
}
int radiodeclare_radioNO = Convert.ToInt32(GetNO(maxNum,streetNO));
for (int k = 0; k < 10; k++)
{
workSheet.Cells[dt.Rows.Count + 2 + k, 1] = maxid + k;
workSheet.Cells[dt.Rows.Count + 2 + k, 5] = radiodeclare_radioNO + k;
}
try
{
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Application.Quit();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
workSheet = null;
workBook = null;
app = null;
GC.Collect();
}
catch (Exception e)
{
throw e;
}
finally
{
}
}
附：这种方法是把Excel打开处理，相当于对Excel的处理，弊病是每次打开都要创建一个进程Excel.exe,而且不容易消除，这种方法性能不佳。撤销方法：
/// 结束EXCEL 进程
/// </summary>
private void Dispose()
{
workBook.Close(null,null,null);
app.Workbooks.Close();
app.Quit();
if(range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if(range1 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
range1 = null;
}
if(range2 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
range2 = null;
}
if(workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workSheet = null;
}
if(workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
if(app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
GC.Collect();
}
浙公网安备 33010602011771号