/// <summary>
///
/// </summary>
public void Export()
{
Excel.ApplicationClass e = Open(GetXlsFileName());
if (e != null)
{
e.Cells[ptTitle.Y, ptTitle.X] = MakeTitle();
int rowOffset = 0;
foreach (DataRow row in _table.Rows)
{
string team = row[1].ToString().Trim();
string stName = row[2].ToString().Trim();
string person = row[3].ToString().Trim();
string dt = row[5].ToString().Trim();
e.Cells[ptXgDataBegin.Y + rowOffset, ptXgDataBegin.X + 0] = team;
e.Cells[ptXgDataBegin.Y + rowOffset, ptXgDataBegin.X + 1] = stName;
e.Cells[ptXgDataBegin.Y + rowOffset, ptXgDataBegin.X + 2] = person;
e.Cells[ptXgDataBegin.Y + rowOffset, ptXgDataBegin.X + 3] = dt;
rowOffset++;
}
e.Visible = true;
// e.Workbooks.Close();
e = null;
GC.Collect();
}
}
private bool ValidateIsInstallExcel()
{
if (_HasInstalledExcel != null)
{
return(_HasInstalledExcel.Value);
}
try
{
Xls.Application excel;
excel = new Xls.ApplicationClass();
if (excel == null)
{
AddError("请先安装Excel!");
_HasInstalledExcel = false;
}
else
{
_HasInstalledExcel = true;
excel.Quit();
excel = null;
GC.Collect();
}
}
catch
{
AddError("请先安装Excel!");
_HasInstalledExcel = false;
}
return(_HasInstalledExcel.Value);
}
void WriteH(Excel.ApplicationClass e, string[] h)
{
for (int i = 0; i < h.Length; i++)
{
e.Cells[2, i + 1] = h[i];
}
}
/// <summary>
///
/// </summary>
private void ExportXls()
{
Excel.ApplicationClass e = OpenExcel();
if (e == null)
{
return;
}
string t = GetT();
WriteT(e, t);
string[] h = GetH();
WriteH(e, h);
int r = 3;
for (int i = 0; i < lvGrStRds.Items.Count; i++)
{
int c = 1;
ListViewItem lvi = lvGrStRds.Items[i];
for (int j = 0; j < lvi.SubItems.Count; j++)
{
e.Cells[r, c++] = lvi.SubItems[j].Text;
}
r++;
}
e.Visible = true;
e.DisplayAlerts = false;
}
/// <summary>
/// 将datatable的数据导出到excel
/// </summary>
/// <param name="mdt"></param>
private void DataToExcel(DataTable mdt)
{
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
Excel.Workbooks oBooks = oExcel.Workbooks;
Excel._Workbook oBook = null;
oBook = (Excel._Workbook)(oExcel.Workbooks.Add(true));// 引用excel工作薄
for (int i = 0; i < mdt.Columns.Count; i++)
{
oExcel.Cells[2, i + 1] = mdt.Columns[i].ColumnName.ToString();// m_DataView.Columns[i].HeaderText.ToString();
}
for (int i = 0; i < mdt.Rows.Count; i++)
{
for (int j = 0; j < mdt.Columns.Count; j++)
{
oExcel.Cells[i + 3, j + 1] = mdt.Rows[i][j].ToString();
}
}
oExcel.Visible = true;
object Missing = System.Reflection.Missing.Value;
// oExcel.Run("Sheet1.printdoc", Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing);
oBook.Application.DisplayAlerts = false;
}
// 运行excel的vba宏
public object RunExcelMacro(string strMacroName, object args)
{
LuaTable ltArgs = args as LuaTable;
if (ltArgs == null)
{
MessageBox.Show("调用RunExcelMacro(string strMacroName, object args)时参数为空！");
return(null);
}
object[] oArgArr = new object[ltArgs.Values.Count];
for (int i = 0; i < oArgArr.Length; i++)
{
oArgArr[i] = ltArgs[i];
}
object[] ArgArr30 = new object[30];
for (int i = 0; i < oArgArr.Length; i++)
{
ArgArr30[i] = oArgArr[i];
}
for (int i = oArgArr.Length; i < ArgArr30.Length; i++)
{
ArgArr30[i] = Missing.Value;
}
if (excelApp == null) // 未初始化成功
{
MessageBox.Show("运行Excel的vba宏失败!\n\n请检查是否已在机器上装有Excel.");
return(null); // 脚本已经对null值的处理
}
object result_macro = null;
try
{
result_macro = excelApp.Run(strMacroName,
ArgArr30[0], ArgArr30[1], ArgArr30[2], ArgArr30[3], ArgArr30[4], ArgArr30[5],
ArgArr30[6], ArgArr30[7], ArgArr30[8], ArgArr30[9], ArgArr30[10], ArgArr30[11],
ArgArr30[12], ArgArr30[13], ArgArr30[14], ArgArr30[15], ArgArr30[16], ArgArr30[17],
ArgArr30[18], ArgArr30[19], ArgArr30[20], ArgArr30[21], ArgArr30[22], ArgArr30[23],
ArgArr30[24], ArgArr30[25], ArgArr30[26], ArgArr30[27], ArgArr30[28], ArgArr30[29]);
}
catch (Exception ex)
{
MessageBox.Show("宏运行错误,请不要保存所作修改！");
}
return(result_macro as string[]);
#if false
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
oExcel.Visible = true;
Excel.Workbooks oBooks = oExcel.Workbooks;
Excel._Workbook oBook = null;
string strXL = "C:/Documents and Settings/kuangsihao/桌面/剑网3-NPC数值.xlsm";
oBook = oBooks.Open(strXL, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
RunMacro(oExcel, new Object[] { "矩形1_Click" });
/// <summary>
///
/// </summary>
public void Export()
{
// (row, col)
//
// StationName form (4,1) row ++
// Date from (3,2) col ++
DateTime dt1 = DateTime.Now;
Excel.ApplicationClass excel = OpenExcel();
DateTime dt2 = DateTime.Now;
MsgBox.Show("open excel: ", (dt2 - dt1).ToString());
if (excel == null)
{
return;
}
excel.Cells[1, 1] = GetReportTitle();
WriteDate(excel, this._beginDate, this._endDate);
int rowOffset = 4;
int colOffset = 2;
for (int i = 0; i < _wccrSet.Count; i++)
{
WccResultsCollection wccs = _wccrSet[i];
string stname = wccs.StationName;
int row = rowOffset + i;
excel.Cells[row, 1] = stname;
for (int j = 0; j < wccs.Count; j++)
{
WccResult wccr = wccs[j];
DateTime dt = wccr.Date;
int wc = wccr.WastingCaloric;
//int col = colOffset + j + 1;
int col = GetDateCol(dt) + colOffset;
// 0 - 无数据
// 1 - 只有一条记录无法计算
//
if (wccr.WastingCaloric > 0 &&
wccr.WastingCaloric != 0 &&
wccr.WastingCaloric != 1)
{
excel.Cells[row, col] = wccr.WastingCaloric;
}
}
}
excel.Visible = true;
}
/// <summary>
/// Creates the Excel Application Class and sets the default web options
/// </summary>
private void CreateExcelAppClass()
{
if (m_excelApp == null)
{
//this ensures that there will only be one Excel process
//running per thread since each thread runs this converter class
m_excelApp = new Excel.ApplicationClass();
}
m_excelApp.Visible = false;
m_excelApp.DisplayAlerts = false;
m_excelApp.Interactive = false;
m_excelApp.DefaultWebOptions.Encoding = Office.MsoEncoding.msoEncodingUTF8;
m_excelApp.DefaultWebOptions.AlwaysSaveInDefaultEncoding = true;
}
/// <summary>
/// Quits the Excel Application.
/// </summary>
public void QuitExcel()
{
if (m_excelApp != null)
{
try
{
//may need to re-set this value before quitting since something Excel may change it itself
m_excelApp.UserControl = false;
m_excelApp.Quit();
}
catch (Exception e)
{
Logger.LogError("Failed to quit Excel", e);
}
m_excelApp = null;
}
}
public void Export()
{
Excel.ApplicationClass e = Open(GetFileName());
if (e != null)
{
e.Cells[ptTitle.Y, ptTitle.X] = MakeTitle();
int rowOffset = 0;
foreach (DataRow row in _table.Rows)
{
string Address = row[0].ToString().Trim();
string StrTime = row[1].ToString().Trim();
string BeforeLevel = row[2].ToString().Trim();
string BehindLevel = row[3].ToString().Trim();
string Hight = row[4].ToString().Trim();
string Flus = row[5].ToString().Trim();
string ReWater = row[6].ToString().Trim();
string ToWater = row[7].ToString().Trim();
// string YesWaUse =row[8].ToString().Trim();
//string DayWaUse =row[9].ToString().Trim();
//string Power = row[8].ToString().Trim();
//string Lock = row[9].ToString().Trim();
e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 0] = Address;
e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 1] = StrTime;
e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 2] = BeforeLevel;
e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 3] = BehindLevel;
e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 4] = Hight;
e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 5] = Flus;
e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 6] = ReWater;
e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 7] = ToWater;
// e.Cells[ ptDataBegin.Y + rowOffset, ptDataBegin.X + 8 ] = YesWaUse;
// e.Cells[ ptDataBegin.Y + rowOffset, ptDataBegin.X + 9 ] = DayWaUse;
//e.Cells[ ptDataBegin.Y + rowOffset, ptDataBegin.X + 8 ] = Power;
//e.Cells[ ptDataBegin.Y + rowOffset, ptDataBegin.X + 9 ] = Lock;
rowOffset++;
}
e.Visible = true;
e = null;
GC.Collect();
}
}
public void Export()
{
Excel.ApplicationClass e = Open(GetFileName());
if (e != null)
{
e.Cells[ptTitle.Y, ptTitle.X] = MakeTitle();
int rowOffset = 0;
foreach (DataRow row in _table.Rows)
{
string Address = row[0].ToString().Trim();
string StrTime = row[1].ToString().Trim();
string Flus = row[2].ToString().Trim();
string Efficiency = row[3].ToString().Trim();
string ReWater = row[4].ToString().Trim();
// string YesReWater =row[7].ToString().Trim();
// string DayWaterUse =row[8].ToString().Trim();
string ToWater = row[5].ToString().Trim();
string Run = row[6].ToString().Trim();
// string ForceRun = row[7].ToString().Trim();
//string Vibrate = row[8].ToString().Trim();
// string Power = row[9].ToString().Trim();
e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 0] = Address;
e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 1] = StrTime;
e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 2] = Flus;
e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 3] = Efficiency;
e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 4] = ReWater;
// e.Cells[ ptDataBegin.Y + rowOffset, ptDataBegin.X + 5 ] = YesReWater;
// e.Cells[ ptDataBegin.Y + rowOffset, ptDataBegin.X + 6 ] = DayWaterUse;
e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 5] = ToWater;
e.Cells[ptDataBegin.Y + rowOffset, ptDataBegin.X + 6] = Run;
// e.Cells[ ptDataBegin.Y + rowOffset, ptDataBegin.X + 9 ] = Power;
rowOffset++;
}
e.Visible = true;
e = null;
GC.Collect();
}
}
/// <summary>
///
/// </summary>
/// <param name="excel"></param>
/// <param name="begin"></param>
/// <param name="end"></param>
private void WriteDate(
Excel.ApplicationClass excel,
DateTime begin,
DateTime end
)
{
// date begin pos: row 3, col 2
int row = 3;
int col = 2;
Debug.Assert(begin.Date < end.Date);
DateTime dt = begin.Date;
int colOffset = 0;
while (dt <= end.Date)
{
string strDate = string.Format("{0}月{1}日", dt.Month, dt.Day);
excel.Cells[row, col + colOffset] = strDate;
colOffset++;
dt = dt.AddDays(1);
}
}
public void DataSet2Excel(DataSet ds, Dictionary <string, string> dicColumnNameMapping, string fileName)
{
if (ds == null || ds.Tables.Count == 0)
{
return;
}
if (ValidateIsInstallExcel() == false)
{
return;
}
#region 自动适应超出6万行就分Sheet
int maxcount = 60000;
for (int count = 0; count < ds.Tables.Count; count++)
{
if (ds.Tables[count].Rows.Count > maxcount)
{
int zs = ds.Tables[count].Rows.Count;
int j = zs / maxcount;
int j1 = zs % maxcount;
if (j1 > 0)
{
j = j + 1;
}
for (int a = 0; a < j; a++)
{
System.Data.DataTable dt = new System.Data.DataTable();
for (int i = 0; i < ds.Tables[count].Columns.Count; i++)
{
System.Data.DataColumn dc = new System.Data.DataColumn(ds.Tables[count].Columns[i].ColumnName);
dt.Columns.Add(dc);
}
int qs = a * maxcount;
int js = qs + maxcount;
if (js > zs)
{
js = zs;
}
for (int m = qs; m < js; m++)
{
System.Data.DataRow dr = dt.NewRow();
for (int n = 0; n < dt.Columns.Count; n++)
{
dr[n] = ds.Tables[count].Rows[m][n];
}
dt.Rows.Add(dr);
}
dt.TableName = ds.Tables[count].TableName + (a + 1).ToString();
ds.Tables.Add(dt);
}
ds.Tables.Remove(ds.Tables[count]);
count--;
}
}
#endregion 自动适应超出6万行就分Sheet
try
{
int totalCount = 0;
int currentCount = 0;
for (int n = 0; n < ds.Tables.Count; n++)
{
totalCount += ds.Tables[n].Rows.Count;
}
object omissing = System.Reflection.Missing.Value;
Excel.ApplicationClass xlapp = new Excel.ApplicationClass();
xlapp.DefaultFilePath = "";
xlapp.DisplayAlerts = true;
xlapp.SheetsInNewWorkbook = 1;
Excel.Workbook xlworkbook = xlapp.Workbooks.Add(omissing);
for (int i = ds.Tables.Count - 1; i >= 0; i--)
{
System.Data.DataTable tmpDataTable = ds.Tables[i];
currentCount = DataTable2Excel(tmpDataTable, totalCount, currentCount, dicColumnNameMapping, omissing, xlworkbook);
}
if (!string.IsNullOrEmpty(fileName))
{
xlworkbook.SaveCopyAs(fileName);
xlworkbook.Close(false, null, null);
xlapp.Quit();
}
else
{
xlapp.Visible = true;
}
//System.Runtime.InteropServices.Marshal.ReleaseComObject(xlworkbook);
ReleaseExcelObject(xlworkbook, xlapp);
}
catch (Exception ex)
{
GC.Collect();
AddError(ex.Message);
}
}
/// <summary>
/// 将二维数组数据写入Excel文件（套用模板并分页）
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="sheetPrefixName">WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2...”</param>
public void ArrayToExcel(string[,] arr, int rows, int top, int left, string sheetPrefixName)
{
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
int sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
DateTime beforeTime;
DateTime afterTime;
if (sheetPrefixName == null || sheetPrefixName.Trim() == "")
{
sheetPrefixName = "Sheet";
}
//创建一个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 将二维数组数据写入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();
//将二维数组中的数据写入WorkSheet
for (int j = 0; j < endRow - startRow; j++)
{
for (int k = 0; k < colCount; k++)
{
sheet.Cells[top + j, left + k] = arr[startRow + j, k];
}
}
Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");
txtAuthor.Text = "lingyun_k";
txtDate.Text = DateTime.Now.ToShortDateString();
txtVersion.Text = "1.0.0.0";
}
#endregion
//输出Excel文件并退出
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
{
Process[] myProcesses;
DateTime startTime;
myProcesses = Process.GetProcessesByName("Excel");
//得不到Excel进程ID，暂时只能判断进程启动时间
foreach (Process myProcess in myProcesses)
{
startTime = myProcess.StartTime;
if (startTime > beforeTime && startTime < afterTime)
{
myProcess.Kill();
}
}
}
}
public void ShowCategories(string db,string[] items, string path)
{
int sheet = 1;
//Open the file to write
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range xlRange;
object misValue = System.Reflection.Missing.Value;
try
{
xlApp = new Excel.ApplicationClass();
//xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
string workbookPath = path + "CategoryExport.xls";
try
{
xlWorkBook = xlApp.Workbooks.Open(workbookPath,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue);//,
//misValue,
//misValue);
}
catch (Exception e)
{
Console.WriteLine(e);
xlWorkBook.SaveAs(workbookPath,
Excel.XlFileFormat.xlWorkbookNormal,
misValue,
misValue,
misValue,
misValue,
Excel.XlSaveAsAccessMode.xlExclusive,
misValue,
misValue,
misValue,
misValue);//,
//misValue);//
xlWorkBook = xlApp.Workbooks.Open(workbookPath,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue);//,
//misValue,
//misValue);
}
//xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheet);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheet);
//Open up connector the to database
SQLiteConnection connector = new SQLiteConnection(db);
connector.Open();
SQLiteCommand mycommand = new SQLiteCommand(connector);
int j = 0;
for (int i = 0; i < items.Length;i++)
{
float totalValue = 0.0f;
int cellNumber = 1;
while (String.Compare(items[i],".") == 0)
{
i++;
}
string commandValue = "SELECT * FROM Transactions WHERE Category='";
commandValue += i.ToString();
commandValue += "'";
mycommand.CommandText = commandValue;
SQLiteDataReader reader = mycommand.ExecuteReader();
xlWorkSheet.Cells[cellNumber++,j+1] = items[i];
while (reader.Read())
{
xlWorkSheet.Cells[cellNumber++,j+1] = reader[3];
totalValue += System.Convert.ToSingle(reader[3]);
}
xlWorkSheet.Cells[cellNumber++,j+1] = totalValue;
reader.Close();
j++;
}
// Set the range to fill.
xlRange = xlWorkSheet.get_Range("A1", "E100");
xlRange.Columns.AutoFit();
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
catch (Exception COMException)
{
Console.Write("COMException"+COMException);
}
}
void WriteT(Excel.ApplicationClass e, string t)
{
e.Cells[1, 1] = t;
}
/// <summary>
///
/// </summary>
public void Export()
{
// (row, col)
//
// StationName form (4,1) row ++
// Date from (3,2) col ++
DateTime dt1 = DateTime.Now;
Excel.ApplicationClass excel = OpenExcel();
DateTime dt2 = DateTime.Now;
MsgBox.Show("open excel: ", (dt2 - dt1).ToString());
if (excel == null)
{
return;
}
excel.Cells[1, 1] = GetReportTitle();
WriteDate(excel, this._beginDate, this._endDate);
int rowOffset = 4;
int colOffset = 2;
// for( int i=0; i<_wccrSet.Count; i++ )
// {
// WccResultsCollection wccs = _wccrSet[i];
// string stname = wccs.StationName;
// int row = rowOffset + i;
// excel.Cells[ row, 1 ] = stname;
//
// for( int j=0; j<wccs.Count; j++ )
// {
// WccResult wccr = wccs[j];
// DateTime dt = wccr.Date;
// int wc = wccr.WastingCaloric;
// //int col = colOffset + j + 1;
// int col = GetDateCol( dt ) + colOffset;
//
// // 0 - 无数据
// // 1 - 只有一条记录无法计算
// //
// if ( wccr.WastingCaloric > 0 &&
// wccr.WastingCaloric != 0 &&
// wccr.WastingCaloric != 1 )
// excel.Cells[ row, col ] = wccr.WastingCaloric;
// }
// }
int rowidx = 0;
foreach (object obj in _wdpSet)
{
ArrayList array = (ArrayList)obj;
bool writeName = false;
foreach (object obj2 in array)
{
WaterDataPoint wdp = (WaterDataPoint)obj2;
int col = colOffset + GetDateCol(wdp.Dt);
int row = rowOffset + rowidx;
if (!writeName)
{
excel.Cells[row, 1] = wdp.Name;
writeName = true;
}
//if ( wdp.UsedWater != 0 )
excel.Cells[row, col] = wdp.UsedWater;
}
rowidx++;
}
excel.Visible = true;
}
void InputInfoToDataBase(string file)
{
FileInfo fi = new FileInfo(file);
if (!fi.Exists) return;
string PhoneNum = fi.Directory.Name;
Excel.Application excel; //声明excel对象
excel=new Excel.ApplicationClass(); //创建对象实例,这时在系统进程中会多出一个excel进程
object missing=System.Reflection.Missing.Value; //Missing 用于调用带默认参数的方法。
object readOnly=true;
excel.Visible=false; //是否显示excel文档
//Open Original Excel File
excel.Application.Workbooks.Open(file,missing,readOnly,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing);
Excel.Workbook myBook=excel.Workbooks[1]; //Workbooks从1开始计数的
Excel.Worksheet mySheet=(Excel.Worksheet)myBook.Worksheets[1]; //从1开始计数的
int i = 1;
int j = 1;
string date;
while (!((date=((Excel.Range)mySheet.Cells[i, 1]).Text.ToString())).Contains("-"))
{
i++;
}
i++;
string time=((Excel.Range)mySheet.Cells[i, 1]).Text.ToString();
while (time.Contains("-") || time.Contains(":"))
{
while(time.Contains(":"))
{
string date_time=date+" "+time;
//DateTime dt=DateTime.Parse(date_time);
// Console.WriteLine(file);
// label2.Text = PhoneNum;
//label4.Text = date_time;
string tl=((Excel.Range)mySheet.Cells[i, 4]).Text.ToString();
string ll = ((Excel.Range)mySheet.Cells[i, 5]).Text.ToString();
// label6.Text=tl;
// label8.Text=ll;
label2.Invoke(new SetTextDelegate(SetText), PhoneNum, date_time, tl, ll);
InsertItem(PhoneNum, date_time,tl ,ll );
i++;
time = ((Excel.Range)mySheet.Cells[i, 1]).Text.ToString();
}
if (time.Contains("-"))
{
date = time;
}
else
{
break;
}
i++;
time = ((Excel.Range)mySheet.Cells[i, 1]).Text.ToString();
}
//设置该矩形框的文本格式
//Save As Original Excel File To CurrentPath
//释放Excel对象,但在Asp.net Web程序中只有转向另一个页面的时候进程才结束
//可以考虑使用KillExcelProcess()杀掉进程
//ReleaseComObject 方法递减运行库可调用包装的引用计数。详细信息见MSDN
System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
myBook.Close(null,null,null);
excel.Workbooks.Close();
mySheet=null;
myBook=null;
missing=null;
readOnly=null;
excel.Quit();
excel=null;
}
/// <summary>
/// ��DataTable�����ݵ�����ʾΪ����
/// </summary>
/// <param name="dt">Ҫ����������</param>
/// <param name="strTitle">��������ı���</param>
/// <param name="FilePath">�����ļ���·��</param>
/// <returns></returns>
public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath)
{
beforeTime = DateTime.Now;
Excel.Application excel;
Excel._Workbook xBk;
Excel._Worksheet xSt;
int rowIndex = 4;
int colIndex = 1;
excel = new Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.ActiveSheet;
//ȡ���б���
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[4, colIndex] = col.ColumnName;
//���ñ����ʽΪ���ж���
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select();
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = titleColorindex;//19;//����Ϊdz��ɫ��������56��
}
//ȡ�ñ���е�����
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//���������͵��ֶθ�ʽΪ���ж���
}
else
if (col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//�����ַ��͵��ֶθ�ʽΪ���ж���
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//����һ���ϼ���
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum, 2] = "�ϼ�";
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//����ѡ�еIJ��ֵ���ɫ
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
//xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//����Ϊdz��ɫ��������56��
//ȡ����������ı���
excel.Cells[2, 2] = strTitle;
//������������ı����ʽ
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;
//���ñ�����Ϊ����Ӧ���
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
//������������ı���Ϊ���о���
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//���Ʊ߿�
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//��������Ӵ�
xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//�����ϱ��Ӵ�
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//�����ұ��Ӵ�
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//�����±��Ӵ�
afterTime = DateTime.Now;
//��ʾЧ��
//excel.Visible=true;
//excel.Sheets[0] = "sss";
ClearFile(FilePath);
string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//wkbNew.SaveAs strBookName;
//excel.Save(strExcelFileName);
#region ����Excel����
//��Ҫ��Excel��DCOM�����������:dcomcnfg
//excel.Quit();
//excel=null;
xBk.Close(null, null, null);
excel.Workbooks.Close();
excel.Quit();
//ע�⣺�����õ�������Excel����Ҫִ����������������������Excel����
// if(rng != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
// rng = null;
// }
// if(tb != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
// tb = null;
// }
if (xSt != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xSt = null;
}
if (xBk != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
xBk = null;
}
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
GC.Collect();//��������
#endregion
return filename;
}
public void DataSet2Excel(DataSet ds, Dictionary<string, string> dicColumnNameMapping, string fileName)
{
if (ds == null || ds.Tables.Count == 0)
return;
if (ValidateIsInstallExcel() == false) return;
#region 自动适应超出6万行就分Sheet
int maxcount = 60000;
for (int count = 0; count < ds.Tables.Count; count++)
{
if (ds.Tables[count].Rows.Count > maxcount)
{
int zs = ds.Tables[count].Rows.Count;
int j = zs / maxcount;
int j1 = zs % maxcount;
if (j1 > 0)
j = j + 1;
for (int a = 0; a < j; a++)
{
System.Data.DataTable dt = new System.Data.DataTable();
for (int i = 0; i < ds.Tables[count].Columns.Count; i++)
{
System.Data.DataColumn dc = new System.Data.DataColumn(ds.Tables[count].Columns[i].ColumnName);
dt.Columns.Add(dc);
}
int qs = a * maxcount;
int js = qs + maxcount;
if (js > zs)
js = zs;
for (int m = qs; m < js; m++)
{
System.Data.DataRow dr = dt.NewRow();
for (int n = 0; n < dt.Columns.Count; n++)
{
dr[n] = ds.Tables[count].Rows[m][n];
}
dt.Rows.Add(dr);
}
dt.TableName = ds.Tables[count].TableName + (a + 1).ToString();
ds.Tables.Add(dt);
}
ds.Tables.Remove(ds.Tables[count]);
count--;
}
}
#endregion 自动适应超出6万行就分Sheet
try
{
int totalCount = 0;
int currentCount = 0;
for (int n = 0; n < ds.Tables.Count; n++)
{
totalCount += ds.Tables[n].Rows.Count;
}
object omissing = System.Reflection.Missing.Value;
Excel.ApplicationClass xlapp = new Excel.ApplicationClass();
xlapp.DefaultFilePath = "";
xlapp.DisplayAlerts = true;
xlapp.SheetsInNewWorkbook = 1;
Excel.Workbook xlworkbook = xlapp.Workbooks.Add(omissing);
for (int i = ds.Tables.Count - 1; i >= 0; i--)
{
System.Data.DataTable tmpDataTable = ds.Tables[i];
currentCount = DataTable2Excel(tmpDataTable, totalCount, currentCount, dicColumnNameMapping, omissing, xlworkbook);
}
if (!string.IsNullOrEmpty(fileName))
{
xlworkbook.SaveCopyAs(fileName);
xlworkbook.Close(false, null, null);
xlapp.Quit();
}
else
{
xlapp.Visible = true;
}
//System.Runtime.InteropServices.Marshal.ReleaseComObject(xlworkbook);
ReleaseExcelObject(xlworkbook, xlapp);
}
catch (Exception ex)
{
GC.Collect();
AddError(ex.Message);
}
}
/// <summary>
/// DataSet导出成Excel，调用函数或实例化后一定要写垃圾回收，否则会造成内存泄漏，GC.Collect();
/// </summary>
/// <param name="Excel_DS">要导出的DataSet</param>
public bool ToExcel(DataSet Excel_DS, string strTitle)
{
try
{
// 判断是否有数据表
if (Excel_DS.Tables.Count < 1)
{
MessageBox.Show("没有数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return false;
}
else
{
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动，可能是没有安装或已损坏", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return false;
}
// 新建一个保存窗口，并设置默认属性
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.DefaultExt = ".xls"; // 默认扩展名
saveFileDialog.AddExtension = true; // 允许自动添加设置的默认扩展名
saveFileDialog.RestoreDirectory = true; // 对话框关闭后恢复默认目录
saveFileDialog.Filter = "Microsoft Excel 工作薄 (*.xls)|*.xls";
saveFileDialog.Title = "导出到 Microsoft Excel 工作薄";
DialogResult result = saveFileDialog.ShowDialog(); // 显示保存对话框
if (result == DialogResult.OK)
{
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 循环DataSet中的表
for (int i = 0; i < Excel_DS.Tables.Count; i++)
{
// 列索引，行索引，总列数，总行数
int ColIndex = 0;
int RowIndex = 0;
int ColCount = Excel_DS.Tables[i].Columns.Count;
int RowCount = Excel_DS.Tables[i].Rows.Count;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, ColCount];
xlSheet.Name = Excel_DS.Tables[i].TableName; // 表单名
// 获取列标题
for (int k = 0; k < ColCount; k++)
{
objData[RowIndex, ColIndex++] = Excel_DS.Tables[i].Columns[k].Caption;
}
// 获取数据
for (RowIndex = 1; RowIndex <= RowCount; RowIndex++)
{
for (ColIndex = 0; ColIndex < ColCount; ColIndex++)
{
objData[RowIndex, ColIndex] = Excel_DS.Tables[i].Rows[RowIndex - 1][ColIndex];
}
System.Windows.Forms.Application.DoEvents();
}
// 写入Excel的样式,设置标题,列名等
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, ColCount]);
//xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, ColCount]).MergeCells = true; // 合并指定单元格
//xlApp.ActiveCell.FormulaR1C1 = strTitle; // 设置标题
//xlApp.ActiveCell.Font.Size = 15; // 标题字体大小
//xlApp.ActiveCell.Font.Bold = 1; // 标题字体加粗
//xlApp.ActiveCell.RowHeight = 50; // 标题栏行高
//xlApp.ActiveCell.Interior.ColorIndex = 42; // 标题栏背景色
//xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlLeft; // 标题栏对齐方式
//xlSheet.Cells.Font.Name = "Arial"; // 全表单中字体
//xlSheet.Cells.Columns.WrapText = true; // 全表单的单元格自动换行
//xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, 1]).ColumnWidth = 20; // 第一列的列宽
//xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, ColCount]).Interior.ColorIndex = 28; // 列名栏背景色
//xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, ColCount]).Font.Size = 10; // 列名栏字体大小
//xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, ColCount]).Font.Bold = 1; // 列名栏字体加粗
//xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, ColCount]).HorizontalAlignment = Excel.Constants.xlCenter; // 列名栏对齐方式，水平居中
//xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, ColCount]).Borders.LineStyle = XlLineStyle.xlContinuous; // 列名栏边框样式
//xlSheet.Cells[RowCount + 3, 1] = "End of the Report!";
//xlSheet.get_Range(xlApp.Cells[RowCount + 3, 1], xlApp.Cells[RowCount + 3, ColCount]).MergeCells = true;
//xlSheet.get_Range(xlApp.Cells[RowCount + 3, 1], xlApp.Cells[RowCount + 3, ColCount]).Interior.ColorIndex = 28;
//xlSheet.get_Range(xlApp.Cells[RowCount + 3, 1], xlApp.Cells[RowCount + 3, ColCount]).Font.Size = 10;
//xlSheet.get_Range(xlApp.Cells[RowCount + 3, 1], xlApp.Cells[RowCount + 3, ColCount]).Font.Bold = 1;
//xlSheet.Cells[RowCount + 4, 1] = "* If the number of organization is less than 3, the city index information will be unavaliable.";
//xlSheet.get_Range(xlApp.Cells[RowCount + 4, 1], xlApp.Cells[RowCount + 4, ColCount]).MergeCells = true;
//xlSheet.get_Range(xlApp.Cells[RowCount + 4, 1], xlApp.Cells[RowCount + 4, ColCount]).Font.Size = 10;
//xlSheet.get_Range(xlApp.Cells[RowCount + 4, 1], xlApp.Cells[RowCount + 4, ColCount]).Font.Bold = 1;
//xlSheet.Cells[RowCount + 5, 1] = "* TM: Top Management; M: Management; P: Professional; S: Staff; B: Blue Collar";
//xlSheet.get_Range(xlApp.Cells[RowCount + 5, 1], xlApp.Cells[RowCount + 5, ColCount]).MergeCells = true;
//xlSheet.get_Range(xlApp.Cells[RowCount + 5, 1], xlApp.Cells[RowCount + 5, ColCount]).Font.Size = 10;
//xlSheet.get_Range(xlApp.Cells[RowCount + 5, 1], xlApp.Cells[RowCount + 5, ColCount]).Font.Bold = 1;
// 设置表单内数据的字体大小
for (int n = 0; n < RowCount; n++)
{
xlSheet.get_Range(xlApp.Cells[n + 2, 1], xlApp.Cells[n + 2, ColCount]).Font.Size = 9;
}
// 数据写入Excel
range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[RowCount + 1, ColCount]);
range.Value2 = objData;
}
object oMissiong = System.Reflection.Missing.Value; //通过反射产生一个函数的默认参数
xlSheet.Application.DisplayAlerts = false; //禁止保存提示
xlSheet.SaveAs(saveFileDialog.FileName, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong); //Microsoft Excel 对象类库11.0以上版本时有10个参数，11以下有9个参数，如果少参数则用oMissiong填入
xlApp.Quit();
}
else
{
return false;
}
}
return true;
}
catch (Exception ex)
{
throw ex;
}
}
private bool ExcelDocument(string strPath,string savePath)
{
try
{
Excel.Application excelApp = new Excel.ApplicationClass();
excelApp.Visible = false;
try
{
Excel.Workbook newWorkbook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
}
catch(Exception ee)
{
string errorText = "Unable to open specified WorkBook. Access is denied. "+ ee.Message ;
Console.Write(errorText +"\n");
return false;
}
try
{
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(strPath,
0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
try
{
excelWorkbook.SaveAs(savePath,Excel.XlFileFormat.xlHtml,"","",false,false,XlSaveAsAccessMode.xlNoChange,false,false,false,false,true);
}
catch(Exception ee)
{
string errorText = "Unable to open specified WorkBook. Access is denied. "+ ee.Message ;
Console.Write(errorText +"\n");
return false;
}
excelApp.Quit();
excelApp = null; //GC
GC.Collect(); //GC
return true;
}
catch(Exception ee)
{
string errorText = "Unable to open specified WorkBook. Access is denied. "+ ee.Message ;
Console.Write(errorText +"\n");
return false;
}
return false;
}
catch(Exception ee)
{
}
return false;
}
/// <summary>
///方法，导出DataGridView中的数据到Excel文件
/// </summary>
/// <remarks>
/// </remarks>
/// <param name= "dgv"> DataGridView </param>
public static void DataGridViewToExcel(MultiColHeaderDgv dgv)
{
#region
//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog();
//默然文件后缀
dlg.DefaultExt = "xls ";
//文件后缀列表
dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
//默然路径是系统当前路径
dlg.InitialDirectory = Directory.GetCurrentDirectory();
//打开保存对话框
if (dlg.ShowDialog() == DialogResult.Cancel)
{
return;
}
//返回文件路径
string fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
{
return;
}
//定义表格内数据的行数和列数
int rowscount = dgv.Rows.Count;
int colscount = dgv.Columns.Count;
//行数必须大于0
if (rowscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数必须大于0
if (colscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//行数不可以大于65536
if (rowscount > 65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数不可以大于255
if (colscount > 255)
{
MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//验证以fileNameString命名的文件是否存在，如果存在删除它
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
#endregion
Excel.Application objExcel = new Excel.Application();
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
try
{
//申明对象
objExcel = new Excel.ApplicationClass();
//objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
//objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
//设置EXCEL不可见
objExcel.Visible = false;
//向Excel中写入表格的表头
int displayColumnsCount = 1;
//Excel.Range
Excel.Range range1 = objExcel.get_Range(objExcel.Cells[1, 1], objExcel.Cells[3, 1]);
Excel.Range range2 = objExcel.get_Range(objExcel.Cells[1, 2], objExcel.Cells[3, 2]);
Excel.Range range3 = objExcel.get_Range(objExcel.Cells[1, 3], objExcel.Cells[3, 3]);
Excel.Range range4 = objExcel.get_Range(objExcel.Cells[1, 4], objExcel.Cells[3, 4]);
Excel.Range range5 = objExcel.get_Range(objExcel.Cells[1, 5], objExcel.Cells[3, 5]);
Excel.Range range6 = objExcel.get_Range(objExcel.Cells[1, 6], objExcel.Cells[3, 6]);
Excel.Range range7 = objExcel.get_Range(objExcel.Cells[1, 7], objExcel.Cells[3, 7]);
Excel.Range range8 = objExcel.get_Range(objExcel.Cells[2, 8], objExcel.Cells[3, 8]);
Excel.Range range9 = objExcel.get_Range(objExcel.Cells[1, 8], objExcel.Cells[1, 11]);
Excel.Range range10 = objExcel.get_Range(objExcel.Cells[2, 9], objExcel.Cells[2, 11]);
range1.Merge(0);
range2.Merge(0);
range3.Merge(0);
range4.Merge(0);
range5.Merge(0);
range6.Merge(0);
range7.Merge(0);
range8.Merge(0);
range9.Merge(0);
range9.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range10.Merge(0);
range10.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range1.NumberFormatLocal = "@"; //设置单元格格式为文本
range2.NumberFormatLocal = "@"; //设置单元格格式为文本
range3.NumberFormatLocal = "@"; //设置单元格格式为文本
range4.NumberFormatLocal = "@"; //设置单元格格式为文本
range5.NumberFormatLocal = "@"; //设置单元格格式为文本
range6.NumberFormatLocal = "@"; //设置单元格格式为文本
range7.NumberFormatLocal = "@"; //设置单元格格式为文本
range8.NumberFormatLocal = "@"; //设置单元格格式为文本
range9.NumberFormatLocal = "@"; //设置单元格格式为文本
objsheet.Cells[1, 1] = "财务分类";
objsheet.Cells[1, 2] = "项目代码";
objsheet.Cells[1, 3] = "项目名称";
objsheet.Cells[1, 4] = "项目内涵";
objsheet.Cells[1, 5] = "除外内容";
objsheet.Cells[1, 6] = "计价单位";
objsheet.Cells[1, 7] = "说明";
objsheet.Cells[2, 8] = "省定价";
objsheet.Cells[3, 9] = "三档";
objsheet.Cells[3, 10] = "二档";
objsheet.Cells[3, 11] = "一档";
objsheet.Cells[1, 8] = "价格（元）";
objsheet.Cells[2, 9] = "市定价格";
range2.ColumnWidth = 12; //设置单元格的宽度
range3.ColumnWidth = 20; //设置单元格的宽度
range4.ColumnWidth = 35; //设置单元格的宽度
range5.ColumnWidth = 25; //设置单元格的宽度
range7.ColumnWidth = 25; //设置单元格的宽度
range2.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range3.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range4.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range5.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range7.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
Excel.Range rangGol = objsheet.get_Range("A1", "K" + dgv.RowCount.ToString());
rangGol.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
rangGol.WrapText = true;
//向Excel中逐行逐列写入表格中的数据
for (int row = 0; row <= dgv.RowCount - 1; row++)
{
//tempProgressBar.PerformStep();
displayColumnsCount = 1;
for (int col = 0; col < colscount; col++)
{
if (dgv.Columns[col].Visible == true)
{
try
{
objExcel.Cells[row + 4, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
displayColumnsCount++;
}
catch (Exception)
{
}
}
}
}
//保存文件
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
//关闭Excel应用
if (objWorkbook != null)
{
objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
}
if (objExcel.Workbooks != null)
{
objExcel.Workbooks.Close();
}
if (objExcel != null)
{
objExcel.Quit();
}
objsheet = null;
objWorkbook = null;
objExcel = null;
}
MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void ExportUpEmployee()
{
string strSql = @"select GetOrgName(GetStationOrgID(b.org_Id)) UnitName,
getworkshopname(b.org_id) WorkShopName,
case when c.level_num=4 then c.Short_Name else null end WorkGroupName,
Employee_Name, Identity_CardNo,
e.Train_Plan_Name,
d.Class_Name,f.Post_Name
from zj_train_plan_employee t
inner join Employee b on t.Employee_ID=b.Employee_ID
inner join Org c on b.Org_ID=c.Org_ID
inner join ZJ_Train_Plan_Post_Class d on t.Train_Plan_Post_Class_ID=d.Train_Plan_Post_Class_ID
inner join ZJ_Train_Plan e on d.Train_Plan_ID=e.Train_Plan_ID
inner join Post f on b.Post_ID = f.Post_ID
where t.Train_Plan_Post_Class_Org_ID=" + Request.QueryString.Get("classOrgID");
OracleAccess db = new OracleAccess();
DataSet ds = db.RunSqlDataSet(strSql);
if (ds.Tables[0].Rows.Count == 0)
{
Response.Write("<script>alert('没有已上报的员工信息！');window.close();</script>");
return;
}
string templateFileName = Server.MapPath("/RailExamBao/RandomExam/ProgressBar.htm");
StreamReader reader = new StreamReader(@templateFileName, System.Text.Encoding.GetEncoding("gb2312"));
string html = reader.ReadToEnd();
reader.Close();
Response.Write(html);
Response.Flush();
System.Threading.Thread.Sleep(200);
string jsBlock;
Excel.Application objApp = new Excel.ApplicationClass();
Excel.Workbooks objbooks = objApp.Workbooks;
Excel.Workbook objbook = objbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet objSheet = (Excel.Worksheet)objbook.Worksheets[1]; //取得sheet1
string filename = "";
string strName = "";
string strFileName = "";
try
{
int count = ds.Tables[0].Rows.Count;
strName = "Excel";
strFileName = ds.Tables[0].Rows[0]["UnitName"].ToString() + ds.Tables[0].Rows[0]["Class_Name"].ToString() + "上报人员";
filename = Server.MapPath("/RailExamBao/Excel/" + strName + ".xls");
objSheet.Cells.Font.Size = 10;
objSheet.Cells.Font.Name = "宋体";
int colIndex = 1;
objSheet.Cells[1, colIndex] = "序号";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "站段";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "车间";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "班组";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "姓名";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "身份证号";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "职名";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "上报培训计划名";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "上报计划培训班名";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
System.Threading.Thread.Sleep(10);
jsBlock = "<script>SetPorgressBar('导出培训计划上报人员','" + ((double)(1 * 100) / (double)count + 1).ToString("0.00") + "'); </script>";
Response.Write(jsBlock);
Response.Flush();
int rowIndex = 2;
foreach (DataRow dr in ds.Tables[0].Rows)
{
colIndex = 1;
objSheet.Cells[rowIndex, colIndex] = rowIndex - 1;
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["UnitName"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["WorkShopName"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["WorkGroupName"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["Employee_Name"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = "'" + dr["Identity_CardNo"];
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["Post_Name"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["Train_Plan_Name"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["Class_Name"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
System.Threading.Thread.Sleep(10);
jsBlock = "<script>SetPorgressBar('导出培训计划上报人员','" + ((double)(rowIndex * 100) / (double)count + 1).ToString("0.00") + "'); </script>";
Response.Write(jsBlock);
Response.Flush();
rowIndex++;
}
objSheet.Cells.Columns.AutoFit();
objApp.Visible = false;
objbook.Saved = true;
objbook.SaveCopyAs(filename);
// 处理完成
jsBlock = "<script>SetCompleted('处理完成。'); </script>";
Response.Write(jsBlock);
Response.Flush();
}
catch (Exception ex)
{
throw ex;
}
finally
{
objbook.Close(Type.Missing, filename, Type.Missing);
objbooks.Close();
objApp.Application.Workbooks.Close();
objApp.Application.Quit();
objApp.Quit();
GC.Collect();
}
Response.Write("<script>top.returnValue='" + strName + "|" + strFileName + "';window.close();</script>");
}
public void Export(System.Data.DataView source, string fileName, string sheetName, ArrayList columnsProperties)
{
this._columnsProperties = columnsProperties;
System.IO.StreamWriter excelDoc;
excelDoc = new System.IO.StreamWriter(fileName);
const string startExcelXML = "<xml version>\r\n<Workbook " +
"xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +
" xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
"xmlns:x=\"urn:schemas- microsoft-com:office:" +
"excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +
"office:spreadsheet\">\r\n <Styles>\r\n " +
"<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
"<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +
"\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" +
"\r\n <Protection/>\r\n </Style>\r\n " +
"<Style ss:ID=\"BoldColumn\">\r\n <Font " +
"x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " +
"<Style ss:ID=\"StringLiteral\">\r\n <NumberFormat" +
" ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +
"ss:ID=\"Decimal\">\r\n <NumberFormat " +
"ss:Format=\"0.0000\"/>\r\n </Style>\r\n " +
"<Style ss:ID=\"Integer\">\r\n <NumberFormat " +
"ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +
"ss:ID=\"DateLiteral\">\r\n <NumberFormat " +
"ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " +
"</Styles>\r\n ";
const string endExcelXML = "</Workbook>";
int rowCount = 0;
int sheetCount = 1;
/*
* <xml version>
* <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
* xmlns:o="urn:schemas-microsoft-com:office:office"
* xmlns:x="urn:schemas-microsoft-com:office:excel"
* xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
* <Styles>
* <Style ss:ID="Default" ss:Name="Normal">
* <Alignment ss:Vertical="Bottom"/>
* <Borders/>
* <Font/>
* <Interior/>
* <NumberFormat/>
* <Protection/>
* </Style>
* <Style ss:ID="BoldColumn">
* <Font x:Family="Swiss" ss:Bold="1"/>
* </Style>
* <Style ss:ID="StringLiteral">
* <NumberFormat ss:Format="@"/>
* </Style>
* <Style ss:ID="Decimal">
* <NumberFormat ss:Format="0.0000"/>
* </Style>
* <Style ss:ID="Integer">
* <NumberFormat ss:Format="0"/>
* </Style>
* <Style ss:ID="DateLiteral">
* <NumberFormat ss:Format="mm/dd/yyyy;@"/>
* </Style>
* </Styles>
* <Worksheet ss:Name="Sheet1">
* </Worksheet>
* </Workbook>
*/
excelDoc.Write(startExcelXML);
excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
excelDoc.Write("<Table>");
excelDoc.Write("<Row>");
ArrayList aux = new ArrayList();
foreach (PropertiesColumn pc in this._columnsProperties)
{
if (pc.Visible)
{
aux.Add(pc);
excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
excelDoc.Write(pc.ColumnCaption);
excelDoc.Write("</Data></Cell>");
}
}
excelDoc.Write("</Row>");
foreach (DataRowView x in source)
{
rowCount++;
//if the number of rows is > 64000 create a new page to continue output
if (rowCount == 64000)
{
rowCount = 0;
sheetCount++;
excelDoc.Write("</Table>");
excelDoc.Write(" </Worksheet>");
excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
excelDoc.Write("<Table>");
}
excelDoc.Write("<Row>"); //ID=" + rowCount + "
foreach (PropertiesColumn pc in aux)
{
if (source.Table.Columns.Contains(pc.ColumnName))
{
System.Type rowType = x.Row[pc.ColumnName].GetType();
switch (rowType.ToString())
{
case "System.String":
string XMLstring = x.Row[pc.ColumnName].ToString();
XMLstring = XMLstring.Trim();
XMLstring = XMLstring.Replace("&", "");
XMLstring = XMLstring.Replace(">", ")");
XMLstring = XMLstring.Replace("<", "(");
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");
excelDoc.Write(XMLstring);
excelDoc.Write("</Data></Cell>");
break;
case "System.DateTime":
//Excel has a specific Date Format of YYYY-MM-DD followed by
//the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
//The Following Code puts the date stored in XMLDate
//to the format above
DateTime XMLDate = (DateTime)x.Row[pc.ColumnName];
string XMLDatetoString = ""; //Excel Converted Date
XMLDatetoString = XMLDate.Year.ToString() +
"-" + (XMLDate.Month < 10 ? "0" + XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
"-" + (XMLDate.Day < 10 ? "0" + XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
"T" + (XMLDate.Hour < 10 ? "0" + XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
":" + (XMLDate.Minute < 10 ? "0" + XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) + ":" +
(XMLDate.Second < 10 ? "0" + XMLDate.Second.ToString() : XMLDate.Second.ToString()) + ".000";
excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" + "<Data ss:Type=\"DateTime\">");
excelDoc.Write(XMLDatetoString);
excelDoc.Write("</Data></Cell>");
break;
case "System.Boolean":
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");
excelDoc.Write(x.Row[pc.ColumnName].ToString());
excelDoc.Write("</Data></Cell>");
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
"<Data ss:Type=\"Number\">");
excelDoc.Write(x.Row[pc.ColumnName].ToString());
excelDoc.Write("</Data></Cell>");
break;
case "System.Decimal":
case "System.Double":
excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
"<Data ss:Type=\"Number\">");
excelDoc.Write(x.Row[pc.ColumnName].ToString());
excelDoc.Write("</Data></Cell>");
break;
case "System.DBNull":
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");
excelDoc.Write("");
excelDoc.Write("</Data></Cell>");
break;
default:
throw(new Exception(rowType.ToString() + " not handled."));
}
}
else
{
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");
excelDoc.Write("");
excelDoc.Write("</Data></Cell>");
}
}
excelDoc.Write("</Row>");
}
excelDoc.Write("</Table>");
excelDoc.Write(" </Worksheet>");
excelDoc.Write(endExcelXML);
excelDoc.Close();
Excel.ApplicationClass EXL = new Excel.ApplicationClass();
workbook = EXL.Workbooks.Open(fileName,
0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
EXL.Visible = true;
Worksheet worksheet = (Worksheet)EXL.ActiveSheet;
worksheet.Activate();
}
/// <summary>
/// 将DataTable的数据导出显示为报表
/// </summary>
/// <param name="dt">要导出的数据</param>
/// <param name="strTitle">导出报表的标题</param>
/// <param name="FilePath">保存文件的路径</param>
/// <returns></returns>
public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath)
{
beforeTime = DateTime.Now;
Excel.Application excel;
Excel._Workbook xBk;
Excel._Worksheet xSt;
int rowIndex = 4;
int colIndex = 1;
excel = new Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.ActiveSheet;
//取得列标题
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[4, colIndex] = col.ColumnName;
//设置标题格式为居中对齐
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select();
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = titleColorindex;//19;//设置为浅黄色，共计有56种
}
//取得表格中的数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if (col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//加载一个合计行
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum, 2] = "合计";
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//设置选中的部分的颜色
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
//xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种
//取得整个报表的标题
excel.Cells[2, 2] = strTitle;
//设置整个报表的标题格式
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;
//设置报表表格为最适应宽度
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
//设置整个报表的标题为跨列居中
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//绘制边框
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗
xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗
afterTime = DateTime.Now;
//显示效果
//excel.Visible=true;
//excel.Sheets[0] = "sss";
ClearFile(FilePath);
string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//wkbNew.SaveAs strBookName;
//excel.Save(strExcelFileName);
#region 结束Excel进程
//需要对Excel的DCOM对象进行配置:dcomcnfg
//excel.Quit();
//excel=null;
xBk.Close(null, null, null);
excel.Workbooks.Close();
excel.Quit();
//注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
// if(rng != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
// rng = null;
// }
// if(tb != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
// tb = null;
// }
if (xSt != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xSt = null;
}
if (xBk != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
xBk = null;
}
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
GC.Collect();//垃圾回收
#endregion
return filename;
}
/// <summary>
/// 将DataTable数据写入Excel文件（套用模板并分页）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="sheetPrefixName">WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2...”</param>
public void DataTableToExcel(System.Data.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 = "Sheet";
}
//创建一个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
for (int j = 0; j < endRow - startRow; j++)
{
for (int k = 0; k < colCount; k++)
{
sheet.Cells[top + j, left + k] = dt.Rows[startRow + j][k].ToString();
}
}
//写文本框数据
//Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
//Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
//Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");
//txtAuthor.Text = "lingyun_k";
//txtDate.Text = DateTime.Now.ToShortDateString();
//txtVersion.Text = "1.0.0.0";
}
#endregion
/*
* try
* {
* // MessageProcess.ZAMessageShowWarning(this.outputFile);
* workBook.SaveAs(this.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
* {
* Process[] myProcesses;
* DateTime startTime;
* myProcesses = Process.GetProcessesByName("Excel");
*
* //得不到Excel进程ID，暂时只能判断进程启动时间
* foreach (Process myProcess in myProcesses)
* {
* startTime = myProcess.StartTime;
*
* if (startTime > beforeTime && startTime < afterTime)
* {
* myProcess.Kill();
* }
* }
* }*/
}
// 运行excel的vba宏
public object RunExcelMacro(string strMacroName, object args)
{
LuaTable ltArgs = args as LuaTable;
if (ltArgs == null)
{
MessageBox.Show("调用RunExcelMacro(string strMacroName, object args)时参数为空！");
return null;
}
object[] oArgArr = new object[ltArgs.Values.Count];
for (int i = 0; i < oArgArr.Length; i++)
{
oArgArr[i] = ltArgs[i];
}
object[] ArgArr30 = new object[30];
for (int i = 0; i < oArgArr.Length; i++)
{
ArgArr30[i] = oArgArr[i];
}
for (int i = oArgArr.Length; i < ArgArr30.Length; i++)
{
ArgArr30[i] = Missing.Value;
}
if(excelApp == null) // 未初始化成功
{
MessageBox.Show("运行Excel的vba宏失败!\n\n请检查是否已在机器上装有Excel.");
return null; // 脚本已经对null值的处理
}
object result_macro = null;
try
{
result_macro = excelApp.Run(strMacroName,
ArgArr30[0], ArgArr30[1], ArgArr30[2], ArgArr30[3], ArgArr30[4], ArgArr30[5],
ArgArr30[6], ArgArr30[7], ArgArr30[8], ArgArr30[9], ArgArr30[10], ArgArr30[11],
ArgArr30[12], ArgArr30[13], ArgArr30[14], ArgArr30[15], ArgArr30[16], ArgArr30[17],
ArgArr30[18], ArgArr30[19], ArgArr30[20], ArgArr30[21], ArgArr30[22], ArgArr30[23],
ArgArr30[24], ArgArr30[25], ArgArr30[26], ArgArr30[27], ArgArr30[28], ArgArr30[29]);
}
catch (Exception ex)
{
MessageBox.Show("宏运行错误,请不要保存所作修改！");
}
return (result_macro as string[]);
#if false
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
oExcel.Visible = true;
Excel.Workbooks oBooks = oExcel.Workbooks;
Excel._Workbook oBook = null;
string strXL = "C:/Documents and Settings/kuangsihao/桌面/剑网3-NPC数值.xlsm";
oBook = oBooks.Open(strXL, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
RunMacro(oExcel, new Object[] { "矩形1_Click" });
private bool ValidateIsInstallExcel()
{
if (_HasInstalledExcel != null) return _HasInstalledExcel.Value;
try
{
Xls.Application excel;
excel = new Xls.ApplicationClass();
if (excel == null)
{
AddError("请先安装Excel!");
_HasInstalledExcel = false;
}
else
{
_HasInstalledExcel = true;
excel.Quit();
excel = null;
GC.Collect();
}
}
catch
{
AddError("请先安装Excel!");
_HasInstalledExcel = false;
}
return _HasInstalledExcel.Value;
}
/// <summary>
/// 将DataTable的数据导出显示为报表
/// </summary>
/// <param name="dt">要导出的数据</param>
/// <param name="strTitle">导出报表的标题</param>
/// <param name="FilePath">保存文件的路径</param>
/// /// <param name="fileName">保存文件的文件名</param>
/// <returns></returns>
public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath, string fileName)
{
beforeTime = DateTime.Now;
Excel.Application excel;
Excel._Workbook xBk;
Excel._Worksheet xSt;
int rowIndex = 4;
int colIndex = 1;
excel = new Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.ActiveSheet;
//取得列标题
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[4, colIndex] = col.ColumnName;
//设置标题格式为居中对齐
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select();
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = titleColorindex;//19;//设置为浅黄色，共计有56种
}
//取得表格中的数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if (col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置字符型的字段格式为居中对齐
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//加载一个合计行
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum, 2] = "合计";
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//设置选中的部分的颜色
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
//xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种
//取得整个报表的标题
excel.Cells[2, 2] = strTitle;
//设置整个报表的标题格式
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;
//设置报表表格为最适应宽度
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
//设置整个报表的标题为跨列居中
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//绘制边框
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick; //设置左边线加粗
xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick; //设置上边线加粗
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick; //设置右边线加粗
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick; //设置下边线加粗
afterTime = DateTime.Now;
//显示效果
//excel.Visible=true;
//excel.Sheets[0] = "sss";
//ClearFile(FilePath);
string filename = DateTime.Now.ToString("yyyy-MM-dd HHmmssff " + fileName) + ".xls";
excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//wkbNew.SaveAs strBookName;
//excel.Save(strExcelFileName);
#region 结束Excel进程
//需要对Excel的DCOM对象进行配置:dcomcnfg
//excel.Quit();
//excel=null;
xBk.Close(null, null, null);
excel.Workbooks.Close();
excel.Quit();
//注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
// if(rng != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
// rng = null;
// }
// if(tb != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
// tb = null;
// }
if (xSt != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xSt = null;
}
if (xBk != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
xBk = null;
}
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
GC.Collect();//垃圾回收
#endregion
return(filename);
}
public void ExcelExport(int sheet, int cells, Budget budgetClass, string path,int year)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range xlRange;
//Excel.Application xlApp;
//Excel.Workbook xlWorkBook;
//Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
try
{
xlApp = new Excel.ApplicationClass();
//xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
string workbookPath = path + "budgeting" + year.ToString() + ".xls";
//xlWorkBook = xlApp.Workbooks.Open(workbookPath, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);//
try
{
xlWorkBook = xlApp.Workbooks.Open(workbookPath,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue);//,
//misValue,
//misValue);
}
catch (Exception e)
{
Console.WriteLine(e);
xlWorkBook.SaveAs(workbookPath,
Excel.XlFileFormat.xlWorkbookNormal,
misValue,
misValue,
misValue,
misValue,
Excel.XlSaveAsAccessMode.xlExclusive,
misValue,
misValue,
misValue,
misValue);//,
//misValue);//
xlWorkBook = xlApp.Workbooks.Open(workbookPath,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue,
misValue);//,
//misValue,
//misValue);
}
//xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheet);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheet);
//the column must be + 3 due to the month starting at 0 and the columns being 1
//and then having a label and a budgeted amount column
//the rows start at 1 also which means you have to add 2 to the row for
//the label
//Re-add in all the data just in case it has never been put in.
xlWorkSheet.Cells[1,1] = "Budget Cat";
xlWorkSheet.Cells[1,2] = "Budget Amt";
xlWorkSheet.Cells[1,3] = "January";
xlWorkSheet.Cells[1,4] = "February";
xlWorkSheet.Cells[1,5] = "March";
xlWorkSheet.Cells[1,6] = "April";
xlWorkSheet.Cells[1,7] = "May";
xlWorkSheet.Cells[1,8] = "June";
xlWorkSheet.Cells[1,9] = "July";
xlWorkSheet.Cells[1,10] = "August";
xlWorkSheet.Cells[1,11] = "September";
xlWorkSheet.Cells[1,12] = "October";
xlWorkSheet.Cells[1,13] = "November";
xlWorkSheet.Cells[1,14] = "December";
SQLiteConnection connector = new SQLiteConnection("Data Source=2013Tomo.d");
connector.Open();
SQLiteCommand mycommand = new SQLiteCommand(connector);
SQLiteDataReader reader;
for (int i=0;i<budgetClass.totalBudget;i++)
{
xlWorkSheet.Cells[i+2, 1] = budgetClass.budgetCategories[i];
}
for (int i=0;i<budgetClass.totalBudget;i++)
{
xlWorkSheet.Cells[i+2, 2] = budgetClass.budgetedAmount[i];
}
for (int month=0;month<12;month++)
{
for (int i=0;i<budgetClass.totalBudget;i++)
{
string command = "select Total(Amount) from Transactions where Category=";
command += i.ToString();
command += " and month="+month.ToString();
mycommand.CommandText = command;
reader = mycommand.ExecuteReader();
reader.Read();
float amountValue = System.Convert.ToSingle(reader[0]);
mycommand.Dispose();
command = "select Total(Amount) from AmazonTransactions where Category=";
command += i.ToString();
command += " and month="+month.ToString();
mycommand.CommandText = command;
reader = mycommand.ExecuteReader();
reader.Read();
float amountValue2 = System.Convert.ToSingle(reader[0]);
mycommand.Dispose();
xlWorkSheet.Cells[i+2, month+3] = amountValue-amountValue2;//budgetClass.budgetSpentAmount[i];
}
}
for (int i=0;i<budgetClass.totalBudget;i++)
{
string command = "select Total(Amount) from Transactions where Category=";
command += i.ToString();
mycommand.CommandText = command;
reader = mycommand.ExecuteReader();
reader.Read();
float amountValue = System.Convert.ToSingle(reader[0]);
mycommand.Dispose();
command = "select Total(Amount) from AmazonTransactions where Category=";
command += i.ToString();
mycommand.CommandText = command;
reader = mycommand.ExecuteReader();
reader.Read();
float amountValue2 = System.Convert.ToSingle(reader[0]);
mycommand.Dispose();
xlWorkSheet.Cells[i+2, 15] = amountValue-amountValue2;//budgetClass.budgetSpentAmount[i];
}
connector.Close();
// Set the range to fill.
xlRange = xlWorkSheet.get_Range("A1", "E100");
xlRange.Columns.AutoFit();
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
catch (Exception COMException)
{
Console.Write("COMException"+COMException);
}
}