private void simpleButton5_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application oExcel_12 = null;
Microsoft.Office.Interop.Excel.Workbook oBook = null;
Microsoft.Office.Interop.Excel.Sheets oSheetsColl = null;
Microsoft.Office.Interop.Excel.Worksheet oSheet = null;
Microsoft.Office.Interop.Excel.Range oRange = null;
Object oMissing = System.Reflection.Missing.Value;
oExcel_12 = new Microsoft.Office.Interop.Excel.Application();
oExcel_12.Visible = true;
oExcel_12.UserControl = true;
oBook = oExcel_12.Workbooks.Add(oMissing);
oSheetsColl = oExcel_12.Worksheets;
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheetsColl.get_Item(1);
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j + 1];
oRange.Value2 = dataGridView1.Columns[j].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i + 2, j + 1];
oRange.Value2 = dataGridView1[j, i].Value;
}
}
oBook = null;
oExcel_12 = null;
GC.Collect();
}
private void browse_Click(object sender, EventArgs e)
{
using (OpenFileDialog openFileDialog = new OpenFileDialog()
{
Filter = "Excel Workbook|*.xlsx|Excel 97-2003 Workbook|*.xls"
})
{
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
Cursor.Current = Cursors.WaitCursor;
this._open.Enabled = true;
fileName.Text = openFileDialog.FileName;
theWorkbook = ExcelObj.Workbooks.Open(fileName.Text);
Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
cmbSheet.Items.Clear();
var numSheet = sheets.Count;
for (int i = 1; i <= numSheet; i++)
{
Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i);
cmbSheet.Items.Add(worksheet.Name);
cmbSheet.SelectedIndex = 0;
}
}
}
}
public ExcelWriter(string _fileName)
{
fileName = _fileName;
if(File.Exists(FilePath))
throw new ApplicationException("File already exists: " + FilePath);
File.Create(FilePath);
app = new Microsoft.Office.Interop.Excel.Application();
Console.Error.WriteLine("Connected to Excel");
wbs = app.Workbooks;
wb = wbs.Add(1);
wb.Activate();
wss = wb.Sheets;
ws = (Microsoft.Office.Interop.Excel.Worksheet)wss.get_Item(1);
Console.Error.WriteLine("Excel Worksheet Initialized");
}
/// <summary>
/// Create Excel application parameters instances
/// </summary>
private void CreateExcelRef()
{
_excelApp = new Microsoft.Office.Interop.Excel.Application();
_books = (Microsoft.Office.Interop.Excel.Workbooks)_excelApp.Workbooks;
_book = (Microsoft.Office.Interop.Excel._Workbook)(_books.Add(_optionalValue));
_sheets = (Microsoft.Office.Interop.Excel.Sheets)_book.Worksheets;
_sheet = (Microsoft.Office.Interop.Excel._Worksheet)(_sheets.get_Item(1));
}
/// <summary>
/// activate the excel application
/// </summary>
///
protected virtual void ActivateExcel()
{
_excelApplication = new Microsoft.Office.Interop.Excel.Application();
_workBooks = (Microsoft.Office.Interop.Excel.Workbooks)_excelApplication.Workbooks;
_workBook = (Microsoft.Office.Interop.Excel._Workbook)(_workBooks.Add(_value));
_excelSheets = (Microsoft.Office.Interop.Excel.Sheets)_workBook.Worksheets;
_excelSheet = (Microsoft.Office.Interop.Excel._Worksheet)(_excelSheets.get_Item(1));
}
public void ReadExistingExcel(DataTable dt, string templatePath, string Exportpath, string late1, string late2, string late3)
{
try
{
//string path = @"C:\Users\zkritsawan.p\Desktop\Project ใช้จริง\testreportMo\TestReport\report\encotemplate.xls";
string path = templatePath;
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oXL.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbooks workbooks = oXL.Workbooks;
mWorkBook = workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = oXL.Worksheets;
//Get the allready exists sheet
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
int colCount = range.Columns.Count;
//int rowCount = range.Rows.Count;
int rowCount = 5;
mWSheet1.Cells[rowCount, 7] = late1;
mWSheet1.Cells[rowCount, 8] = late2;
mWSheet1.Cells[rowCount, 9] = late3;
for (int r = 0; r < dt.Rows.Count; r++)
{
mWSheet1.Cells[rowCount + r + 1, 2] = dt.Rows[r]["NAME"].ToString();
mWSheet1.Cells[rowCount + r + 1, 7] = dt.Rows[r]["Case1"].ToString();
mWSheet1.Cells[rowCount + r + 1, 8] = dt.Rows[r]["Case2"].ToString();
mWSheet1.Cells[rowCount + r + 1, 9] = dt.Rows[r]["Case3"].ToString();
mWSheet1.Cells[rowCount + r + 1, 6] = string.Format("=E{0}/30", rowCount + r + 1);
mWSheet1.Cells[rowCount + r + 1, 11] = string.Format("=E{0}-J{0}", rowCount + r + 1);
mWSheet1.Cells[rowCount + r + 1, 10] = string.Format("=((G{0}*F{0})+(H{0}*F{0})+(I{0}*F{0}))", rowCount + r + 1);
}
// string pathSave = @"C:\Users\zkritsawan.p\Desktop\Project ใช้จริง\testreportMo\TestReport\report\EncoReport(1).xls";
string pathSave = Exportpath;
mWorkBook.SaveAs(pathSave, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
mWSheet1 = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
catch (Exception e)
{
}
}
private void button1_Click(object sender, EventArgs e)
{
string path = @"D:\NYT_Lasttry\MyConfigScapy.xls";
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oXL.DisplayAlerts = false;
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
//Get the allready exists sheet
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
mWSheet1.Cells[3, 2] = this.textBox1.Text;
mWSheet1.Cells[4, 2] = this.textBox2.Text;
mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value);
mWorkBook.Close(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
mWSheet1 = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
this.mainForm.label1.Text = "IP = " + this.textBox1.Text;;
this.mainForm.label2.Text = "Payload = " + this.textBox2.Text;;
int ExitCode;
Process p = new Process();
p.StartInfo.UseShellExecute = true;
p.StartInfo.FileName = "D:\\NYT_Lasttry\\batch.bat";
p.Start();
p.WaitForExit();
ExitCode = p.ExitCode;
p.Close();
this.Close();
}
/// <summary>
/// Save the string To the excel File.
/// </summary>
/// <param name="ToFile">String Of TOLEDO Format</param>
/// <param name="SavedFlag">Boolean Flag that Shows this string Saved or not</param>
public void ReadExistingExcel(String ToFile, Boolean SavedFlag)
{
try
{
string path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\Resources\\iTOLEDO_Loggs.xls";
oXL = new Microsoft.Office.Interop.Excel.Application();
// oXL.Visible = true;
oXL.DisplayAlerts = false;
try
{
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
}
catch (Exception)
{
// mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
}
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
//Get the allready exists sheet
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
int colCount = range.Columns.Count;
int rowCount = range.Rows.Count;
///Add Columns To the Excel file.
for (int index = 1; index < 2; index++)
{
mWSheet1.Cells[rowCount + index, 1] = DateTime.Now.ToString("MMM dd, yyyy hh:mm:ss tt");
mWSheet1.Cells[rowCount + index, 2] = ToFile;
mWSheet1.Cells[rowCount + index, 3] = SavedFlag.ToString();
}
mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
mWSheet1 = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
catch (Exception)
{ }
}
private void createExcelFile(List <SqlClient.LicenseUse> accumulatedList)
{
string filename = Path.GetTempFileName();
filename = Path.ChangeExtension(filename, ".xlsx");
File.WriteAllBytes(filename, Properties.Resources.ExcelTemplate);
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook workbook;
Microsoft.Office.Interop.Excel.Worksheet worksheet;
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
workbook = excel.Workbooks.Open(Path.GetFullPath(filename));
try
{
Microsoft.Office.Interop.Excel.Sheets excelSheets = workbook.Worksheets;
worksheet = excelSheets.get_Item(1) as Microsoft.Office.Interop.Excel.Worksheet;
int row = 1;
foreach (SqlClient.LicenseUse lu in accumulatedList.OrderBy(n => n.Profile).ThenBy(n => n.License))
{
row++;
worksheet.Cells[row, 1] = lu.Profile;
worksheet.Cells[row, 2] = lu.License;
worksheet.Cells[row, 3] = lu.Count;
}
workbook.Save();
}
catch (Exception e)
{
try { File.Delete(filename); } catch { }
throw e;
}
finally
{
workbook.Close();
excel.Quit();
}
System.Diagnostics.Process.Start(filename);
}
public static void ReadExistingExcel()
{
string path = @"C:\Tool\Reports1.xls";
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oXL.DisplayAlerts = false;
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
//Get the allready exists sheet
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
int colCount = range.Columns.Count;
int rowCount = range.Rows.Count;
for (int index = 1; index < 15; index++)
{
mWSheet1.Cells[rowCount + index, 1] = rowCount + index;
mWSheet1.Cells[rowCount + index, 2] = "New Item" + index;
}
mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
mWSheet1 = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
/// <summary>
/// 打印交易票
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void printBtn_Click(object sender, EventArgs e)
{
try
{
vehicle = VehicleDao.GetBySerial(this.serial.Text);
vehicle.Isprinted = true;
vehicle.Isgrant = false;
VehicleDao.UpdateVehicle(vehicle);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
Object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application m_objExcel = null;
Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
Microsoft.Office.Interop.Excel.Range m_objRange = null;
try
{
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
DirectoryInfo Dir = new DirectoryInfo(".");
m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "/Templete/Input.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));
// 开票日期
m_objRange = m_objSheet.get_Range("B1", oMissing);
m_objRange.Value = DateTime.ParseExact(VehicleDao.GetCurrentDate(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture).ToString("yyyy/MM/dd");
// 开票人
m_objRange = m_objSheet.get_Range("K19", oMissing);
m_objRange.Value = LoginForm.user.UsersName;
// 买 方 单 位 /个人
m_objRange = m_objSheet.get_Range("C5", oMissing);
m_objRange.Value = this.currentName.Text;
// 单位代码/身份证号码
m_objRange = m_objSheet.get_Range("K5", oMissing);
m_objRange.Value = this.currentId.Text;
// 买方单位/个人住址
m_objRange = m_objSheet.get_Range("C6", oMissing);
m_objRange.Value = this.currentAddress.Text;
// 电话
m_objRange = m_objSheet.get_Range("L6", oMissing);
m_objRange.Value = this.currentPhone.Text;
// 卖 方 单 位/ 个人
m_objRange = m_objSheet.get_Range("C7", oMissing);
m_objRange.Value = this.originName.Text;
// 单位代码/身份证号码
m_objRange = m_objSheet.get_Range("K7", oMissing);
m_objRange.Value = this.originId.Text;
// 卖方单位/个人住址
m_objRange = m_objSheet.get_Range("C8", oMissing);
m_objRange.Value = this.originAddress.Text;
// 电话
m_objRange = m_objSheet.get_Range("L8", oMissing);
m_objRange.Value = this.originPhone.Text;
// 车 牌 照 号
m_objRange = m_objSheet.get_Range("C9", oMissing);
m_objRange.Value = "辽B." + this.license.Text;
// 登记证号
m_objRange = m_objSheet.get_Range("E9", oMissing);
m_objRange.Value = this.certificate.Text;
// 车 辆 类 型
m_objRange = m_objSheet.get_Range("L9", oMissing);
m_objRange.Value = this.vehicleType.Text;
// 车架号/车辆识别代码
m_objRange = m_objSheet.get_Range("C10", oMissing);
m_objRange.Value = this.vin.Text;
// 厂牌型号
m_objRange = m_objSheet.get_Range("E10", oMissing);
m_objRange.Value = this.brand.Text;
// 转入地车辆管理所名称
m_objRange = m_objSheet.get_Range("L10", oMissing);
m_objRange.Value = this.department.Text;
// 车价 合 计（大写）
m_objRange = m_objSheet.get_Range("C11", oMissing);
m_objRange.Value = this.transactions.Text;
// 车价 合 计（小写）
m_objRange = m_objSheet.get_Range("L11", oMissing);
m_objRange.Value = this.transactions.Text;
m_objExcel.DisplayAlerts = false;
m_objSheet.PrintOut();
}
catch (Exception ex)
{
MessageBox.Show("打印失败，请检查打印机设置。错误信息：" + ex.Message);
}
finally
{
if (m_objBook != null)
{
m_objBook.Close(oMissing, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
}
if (m_objExcel != null)
{
m_objExcel.Workbooks.Close();
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
m_objBook = null;
m_objExcel = null;
GC.Collect();
}
this.resetControlContent();
this.setControlReadOnly(true);
this.serial.Focus();
this.serial.Select(this.serial.Text.Length, 0);
}
/// <summary>
/// 创建新的EXCEL并写入数据
/// </summary>
/// <param name="dataSet"></param>
/// <param name="savePath"></param>
public void ToExcel(DataSet dataSet, string savePath)
{
DataTable ds = dataSet.Tables[0];
int rowNumber = ds.Rows.Count;
int columnNumber = ds.Columns.Count;
if (rowNumber == 0)
{
MessageBox.Show("没有任何数据可以导入到Excel文件！");
}
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks wbks = excel.Workbooks;
Microsoft.Office.Interop.Excel._Workbook _wbk = wbks.Add(true);
Microsoft.Office.Interop.Excel.Sheets shs = _wbk.Sheets;
Microsoft.Office.Interop.Excel._Worksheet _wsh = (Microsoft.Office.Interop.Excel._Worksheet)shs.get_Item(1);
excel.Application.Workbooks.Add(true);
excel.Visible = false;//是否打开该Excel文件
List <uint> fieldPlaceList;
List <Dictionary <uint, string[]> > combineRowList = GetCombineList(dataSet, out fieldPlaceList);
//数据合并
List <List <StringBuilder> > combineList = new List <List <StringBuilder> >();
for (int i = 0; i < combineRowList.Count; i++)
{
// combineList.Add(DataCombine(dataSet, combineRowList[i]));
}
//填充数据
for (int c = 0; c < rowNumber; c++)
{
for (int j = 0; j < ds.Columns.Count + combineList.Count; j++)//一行中的每列
{
if (j >= ds.Columns.Count)
{
int index = j - ds.Columns.Count;
switch (c)
{
case 0:
_wsh.Cells[c + 1, j + 1] = "合并字段#客户端";
break;
case 1:
_wsh.Cells[c + 1, j + 1] = "@" + combineList[index][2].ToString().Replace('&', '#') + " " + combineList[index][3].ToString().Replace('_', ' ');
break;
case 2:
_wsh.Cells[c + 1, j + 1] = "uint#" + combineList[index][3].ToString();
break;
case 3:
_wsh.Cells[c + 1, j + 1] = combineList[index][3].ToString();
break;
default:
_wsh.Cells[c + 1, j + 1] = combineList[index][c].ToString();
break;
}
}
else
{
//每个单元格内容，加到StringBuilder中
if (fieldPlaceList.Contains((uint)j) && c == 0)
{
_wsh.Cells[c + 1, j + 1] = ds.Rows[c].ItemArray[j] + "#合并";
}
else
{
_wsh.Cells[c + 1, j + 1] = ds.Rows[c].ItemArray[j];
}
}
}
}
_wbk.SaveCopyAs(savePath);
}
/// <summary>
/// 打印
/// </summary>
private void printInfo()
{
Object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application m_objExcel = null;
Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
Microsoft.Office.Interop.Excel.Range m_objRange = null;
try
{
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
DirectoryInfo Dir = new DirectoryInfo(".");
m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "/Templete/Alter.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));
// 号牌种类
m_objRange = m_objSheet.get_Range("D2", oMissing);
m_objRange.Value = this.category.Text;
// 号牌号码
m_objRange = m_objSheet.get_Range("H2", oMissing);
m_objRange.Value = "辽B" + this.license.Text;
// 变更机动车所有人姓名/名称
m_objRange = m_objSheet.get_Range("D4", oMissing);
m_objRange.Value = this.ownerName.Text;
//共同所有的机动车变更所有人
m_objRange = m_objSheet.get_Range("D5", oMissing);
m_objRange.Value = this.owner.Text;
// 住所在车辆管理所辖区内迁移
m_objRange = m_objSheet.get_Range("D6", oMissing);
m_objRange.Value = this.newAddress.Text;
// 邮寄地址
m_objRange = m_objSheet.get_Range("D7", oMissing);
m_objRange.Value = " " + this.postAddress.Text;
// 邮政编码
m_objRange = m_objSheet.get_Range("D8", oMissing);
m_objRange.Value = " " + this.postcode.Text;
// 电子信箱
m_objRange = m_objSheet.get_Range("D9", oMissing);
m_objRange.Value = " " + this.email.Text;
// 固定电话
m_objRange = m_objSheet.get_Range("H8", oMissing);
m_objRange.Value = " " + this.phone.Text;
// 移动电话
m_objRange = m_objSheet.get_Range("H9", oMissing);
m_objRange.Value = " " + this.mobile.Text;
// 省（自治县、直辖市)
m_objRange = m_objSheet.get_Range("D10", oMissing);
m_objRange.Value = " " + this.province.Text;
// 车辆管理所
m_objRange = m_objSheet.get_Range("H10", oMissing);
m_objRange.Value = this.department.Text;
// 变更后的信息
m_objRange = m_objSheet.get_Range("D15", oMissing);
m_objRange.Value = this.information.Text;
// 代理人姓名、名称
m_objRange = m_objSheet.get_Range("C21", oMissing);
m_objRange.Value = this.agentName.Text;
// 代理人邮寄地址
m_objRange = m_objSheet.get_Range("C22", oMissing);
m_objRange.Value = this.agentAddress.Text;
// 代理人邮政编码
m_objRange = m_objSheet.get_Range("C23", oMissing);
m_objRange.Value = this.agentPostcode.Text;
// 代理人联系电话
m_objRange = m_objSheet.get_Range("F23", oMissing);
m_objRange.Value = this.agentPhone.Text;
// 代理人电子信箱
m_objRange = m_objSheet.get_Range("C24", oMissing);
m_objRange.Value = this.agentEmail.Text;
// 经办人电话
m_objRange = m_objSheet.get_Range("C25", oMissing);
m_objRange.Value = this.handlerName.Text;
// 经办人联系电话
m_objRange = m_objSheet.get_Range("F25", oMissing);
m_objRange.Value = this.handlerPhone.Text;
// 办理日期
m_objRange = m_objSheet.get_Range("H20", oMissing);
m_objRange.Value = DateTime.Today.Year + " " + DateTime.Today.Month + " " + DateTime.Today.Day + " ";
m_objRange = m_objSheet.get_Range("H25", oMissing);
m_objRange.Value = DateTime.Today.Year + " " + DateTime.Today.Month + " " + DateTime.Today.Day + " ";;
m_objExcel.DisplayAlerts = false;
m_objBook.Save();
m_objSheet.PrintOut();
}
catch (Exception ex)
{
// 输出异常信息
MessageBox.Show("打印失败,异常信息为：" + ex.Message);
}
finally
{
if (m_objBook != null)
{
m_objBook.Close(oMissing, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
}
if (m_objExcel != null)
{
m_objExcel.Workbooks.Close();
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
m_objBook = null;
m_objExcel = null;
GC.Collect();
}
}
private void Run_Report(object Rpt_Code_Rpt_Name_Rpt_Year_Rpt_Prd)
{
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
try
{
string[] Shet = Rpt_Code_Rpt_Name_Rpt_Year_Rpt_Prd.ToString().Split((new string[] { "##" }), StringSplitOptions.None);
string Rpt_Code = Shet[0];
string Rpt_Name = Shet[1];
int Rpt_Year = int.Parse(Shet[2]);
int Rpt_Prd = int.Parse(Shet[3]);
DataTable dtCols = DataClass.RetrieveData("SELECT GRCT.Col_No, GRCT.Col_Type, GRCT.Col_Source, GRCT.Col_Range, GRCT.Col_Period, GRCT.Col_Header, GRCT.Col_Calc_Formula, GRCT.Col_Prec_Source, GRCT.Col_Width, GRCT.Col_Sup, GRCT.Col_Calc_Divide, GR.Daily_Rev FROM dbo.G_Report AS GR LEFT OUTER JOIN dbo.G_Report_Col_Det AS GRCT ON GR.Col_Code = GRCT.Col_Code WHERE (GR.Rpt_Code = N'" + Rpt_Code + "')");
DataTable dtRows = DataClass.RetrieveData("SELECT GRCT.Row_No, GRCT.Row_Type, GRCT.Row_Accounts, GRCT.Row_Divide_By_Acc, GRCT.Row_Desc, GRCT.Row_TOT_Formula, GRCT.Row_Prec_Row, GRCT.Row_Rev, GRCT.Row_Sup, GRCT.Row_Format, GRCT.Row_Divide_By_Amt, GR.Daily_Rev FROM dbo.G_Report_Row_Det AS GRCT RIGHT OUTER JOIN dbo.G_Report AS GR ON GRCT.Row_Code = GR.Row_Code WHERE (GR.Rpt_Code = N'" + Rpt_Code + "')");
Microsoft.Office.Interop.Excel.ApplicationClass myexl = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook mywb = myexl.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Sheets mysh = mywb.Sheets;
string currentSheet = "Sheet1";
Microsoft.Office.Interop.Excel.Worksheet mywsh = (Microsoft.Office.Interop.Excel.Worksheet)mysh.get_Item(currentSheet);
mywsh.Name = Rpt_Name;
string letter = GetLetter(dtCols.Rows.Count);
Microsoft.Office.Interop.Excel.Range rr = mywsh.get_Range("A1", "A1");
////for (int c = 0; c < DT.Columns.Count; c++)
////{
//// rr[1, c + 1] = DT.Columns[c].ColumnName;
////}
//rr.Font.Bold = true;
for (int row = 0; row < dtRows.Rows.Count; row++)
{
string Accounts = dtRows.Rows[row]["Row_Accounts"].ToString();
Accounts = Accounts.Replace("-", "");
Accounts = Accounts.Replace("(", "");
Accounts = Accounts.Replace(")", "");
if (Accounts.Contains("TO"))
{
Accounts = Accounts.Replace("TO", "AND");
}
else
{
Accounts = Accounts + " AND " + Accounts;
}
string Divide_Acc = dtRows.Rows[row]["Row_Divide_By_Acc"].ToString();
if (Divide_Acc.Trim() != "")
{
Divide_Acc = Divide_Acc.Replace("-", "");
Divide_Acc = Divide_Acc.Replace("(", "");
Divide_Acc = Divide_Acc.Replace(")", "");
if (Divide_Acc.Contains("TO"))
{
Divide_Acc = Divide_Acc.Replace("TO", "AND");
}
else
{
Divide_Acc = Divide_Acc + " AND " + Divide_Acc;
}
}
if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "DTL" || dtRows.Rows[row]["Row_Type"].ToString().Trim() == "TOT")
{
for (int col = 0; col < dtCols.Rows.Count; col++)
{
rr = mywsh.get_Range(dtCols.Rows[col]["Col_No"].ToString() + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(), dtCols.Rows[col]["Col_No"].ToString() + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString());
if (dtCols.Rows[col]["Col_Type"].ToString() == "GL")
{
if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "DTL")
{
DateTime Start_Date = DateTime.Now.Date;
DateTime End_Date = DateTime.Now.Date;
string str = "GLTotals";
string strPrd = "PeriodBalance";
if (dtCols.Rows[col]["Col_Period"].ToString().Substring(0, 3) == "PRD")
{
Rpt_Prd = int.Parse(dtCols.Rows[col]["Col_Period"].ToString().Remove(0, 3));
}
switch (dtCols.Rows[col]["Col_Source"].ToString())
{
case "LST":
Rpt_Year--;
break;
case "BGT":
str = "GLBudget";
strPrd = "Period";
break;
}
if (!DataClass.isExsist("*", "FiscalYear = " + Rpt_Year, "GLFiscalPeriodSetup"))
{
rr[1, 1] = 0;
}
else
{
switch (dtCols.Rows[col]["Col_Range"].ToString())
{
case "MTD":
if ((bool)dtCols.Rows[col]["Daily_Rev"] && dtCols.Rows[col]["Col_Source"].ToString() != "BGT")
{
string PeriodID = DataClass.ReturnRecordNameByID("Select PeriodID" + Rpt_Prd + " From GLFiscalPeriodSetup WHERE FiscalYear = " + Rpt_Year);
Start_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodStartDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID));
End_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodEndDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID));
}
else
{
strPrd += Rpt_Prd.ToString();
}
break;
case "YTD":
if ((bool)dtCols.Rows[col]["Daily_Rev"] && dtCols.Rows[col]["Col_Source"].ToString() != "BGT")
{
string PeriodID = DataClass.ReturnRecordNameByID("Select PeriodID1 From GLFiscalPeriodSetup WHERE FiscalYear = " + Rpt_Year);
Start_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodStartDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID));
PeriodID = DataClass.ReturnRecordNameByID("Select PeriodID" + Rpt_Prd + " From GLFiscalPeriodSetup WHERE FiscalYear = " + Rpt_Year);
End_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodEndDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID));
}
else
{
string PeriodID = DataClass.ReturnRecordNameByID("Select MIN(PeriodID) AS PeriodID From GLFiscalPeriod WHERE Status='OPEN'");
string PeriodNumber = DataClass.ReturnRecordNameByID("Select PeriodNumber From GLFiscalPeriod WHERE PeriodID = " + PeriodID);
string FiscalYear = DataClass.ReturnRecordNameByID("Select FiscalYear From GLFiscalPeriodSetup WHERE PeriodID" + PeriodNumber + " = " + PeriodID);
if (int.Parse(FiscalYear) > Rpt_Year)
{
strPrd += Rpt_Prd.ToString();
}
else if (int.Parse(FiscalYear) < Rpt_Year)
{
string s = "";
for (int i = 1; i < Rpt_Prd; i++)
{
s += strPrd + i.ToString() + "+";
}
s += strPrd + Rpt_Prd.ToString();
strPrd = s;
}
else if (int.Parse(FiscalYear) == Rpt_Year)
{
if (int.Parse(PeriodNumber) >= Rpt_Prd - 1)
{
strPrd += Rpt_Prd.ToString();
}
else
{
string s = "";
for (int i = int.Parse(PeriodNumber); i < Rpt_Prd; i++)
{
s += strPrd + i.ToString() + "+";
}
s += strPrd + Rpt_Prd.ToString();
strPrd = s;
}
}
}
break;
case "DAY":
if ((bool)dtCols.Rows[col]["Daily_Rev"] && dtCols.Rows[col]["Col_Source"].ToString() != "BGT")
{
string PeriodID = DataClass.ReturnRecordNameByID("Select PeriodID" + Rpt_Prd + " From GLFiscalPeriodSetup WHERE FiscalYear = " + Rpt_Year);
Start_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodEndDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID));
End_Date = Start_Date;
}
break;
case "BEG":
if (strPrd == "Period")
{
strPrd = "0";
}
else
{
strPrd = "BeginningBalance";
}
break;
}
DataTable DT = new DataTable();
if ((bool)dtCols.Rows[col]["Daily_Rev"] && dtCols.Rows[col]["Col_Range"].ToString() != "BEG" && dtCols.Rows[col]["Col_Source"].ToString() != "BGT")
{
System.Collections.ArrayList ItemsNames = new System.Collections.ArrayList();
System.Collections.ArrayList ItemsValues = new System.Collections.ArrayList();
ItemsNames.Add("Rpt_Fdate");
ItemsValues.Add(Start_Date.Date);
ItemsNames.Add("Rpt_Tdate");
ItemsValues.Add(End_Date.Date);
DT = DataClass.SelectRecord("SELECT { fn IFNULL(SUM(GLT.AmountLC), 0) } AS Amt FROM dbo.Batch AS B INNER JOIN dbo.GLTransactions AS GLT ON B.BatchNo = GLT.BatchNo WHERE (B.BatchStat = 'P') AND (Convert(int,replace(GLT.GLAccount,'-','')) Between " + Accounts + ") AND (B.BatchDate Between @Rpt_Fdate AND @Rpt_Tdate ) AND (B.BatchJNL IN (Select JournalCodeID From G_Report_Journal Where Rpt_Code = '" + Rpt_Code + "'))", ItemsNames, ItemsValues);
}
else
{
DT = DataClass.RetrieveData("SELECT { fn IFNULL(Sum(" + strPrd.Trim() + "), 0) } AS Amt FROM dbo." + str.Trim() + " WHERE (Convert(int,replace(AccountNumber,'-','')) Between " + Accounts + ") AND ({ fn IFNULL(FiscalYear, 0) } = " + Rpt_Year.ToString() + ") ");
}
if (DT.Rows.Count > 0)
{
double DAccAmt = 0;
if (Divide_Acc.Trim() != "")
{
DataTable DT2 = new DataTable();
if ((bool)dtCols.Rows[col]["Daily_Rev"] && dtCols.Rows[col]["Col_Range"].ToString() != "BEG" && dtCols.Rows[col]["Col_Source"].ToString() != "BGT")
{
System.Collections.ArrayList ItemsNames = new System.Collections.ArrayList();
System.Collections.ArrayList ItemsValues = new System.Collections.ArrayList();
ItemsNames.Add("Rpt_Fdate");
ItemsValues.Add(Start_Date.Date);
ItemsNames.Add("Rpt_Tdate");
ItemsValues.Add(End_Date.Date);
DT2 = DataClass.SelectRecord("SELECT { fn IFNULL(SUM(GLT.AmountLC), 0) } AS Amt FROM dbo.Batch AS B INNER JOIN dbo.GLTransactions AS GLT ON B.BatchNo = GLT.BatchNo WHERE (B.BatchStat = 'P') AND (Convert(int,replace(GLT.GLAccount,'-','')) Between " + Divide_Acc + ") AND (B.BatchDate Between @Rpt_Fdate AND @Rpt_Tdate ) AND (B.BatchJNL IN (Select JournalCodeID From G_Report_Journal Where Rpt_Code = '" + Rpt_Code + "'))", ItemsNames, ItemsValues);
}
else
{
DT2 = DataClass.RetrieveData("SELECT { fn IFNULL(Sum(" + strPrd.Trim() + "), 0) } AS Amt FROM dbo." + str.Trim() + " WHERE (Convert(int,replace(AccountNumber,'-','')) Between " + Divide_Acc + ") AND ({ fn IFNULL(FiscalYear, 0) } = " + Rpt_Year.ToString() + ") ");
}
if (DT2.Rows.Count > 0)
{
double.TryParse(DT2.Rows[0]["Amt"].ToString(), out DAccAmt);
}
}
int Div_Day = 0;
switch (dtRows.Rows[row]["Row_Divide_By_Amt"].ToString().Trim())
{
case "Day":
string PeriodID = DataClass.ReturnRecordNameByID("Select PeriodID" + Rpt_Prd + " From GLFiscalPeriodSetup WHERE FiscalYear = " + Rpt_Year);
int.TryParse(DataClass.ReturnRecordNameByID("Select datediff (d, PeriodStartDate, PeriodEndDate) + 1 From GLFiscalPeriod WHERE PeriodID = " + PeriodID), out Div_Day);
break;
default:
int.TryParse(dtRows.Rows[row]["Row_Divide_By_Amt"].ToString().Trim(), out Div_Day);
break;
}
string sval = "";
if (DAccAmt != 0)
{
sval = "=" + DT.Rows[0]["Amt"].ToString() + "/" + DAccAmt.ToString();
}
if (Div_Day != 0)
{
if (sval.Contains("="))
{
sval += "/" + Div_Day.ToString();
}
else
{
sval = "=" + DT.Rows[0]["Amt"].ToString() + "/" + Div_Day.ToString();
}
}
if (sval.Trim() != "")
{
rr[1, 1] = sval.Trim();
}
else
{
rr[1, 1] = double.Parse(DT.Rows[0]["Amt"].ToString());
}
}
}
}
else if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "TOT")
{
string RTF = dtRows.Rows[row]["Row_TOT_Formula"].ToString();
RTF = RTF.Replace("00", "");
RTF = RTF.Replace("A", dtCols.Rows[col]["Col_No"].ToString());
rr[1, 1] = "=" + RTF.Trim();
}
}
else if (dtCols.Rows[col]["Col_Type"].ToString() == "DB" || dtCols.Rows[col]["Col_Type"].ToString() == "CR")
{
if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "DTL")
{
string BEG = "";
if (dtCols.Rows[col]["Col_Period"].ToString().Substring(0, 3) == "PRD")
{
Rpt_Prd = int.Parse(dtCols.Rows[col]["Col_Period"].ToString().Remove(0, 3));
}
if (dtCols.Rows[col]["Col_Source"].ToString() == "LST")
{
Rpt_Year--;
}
if (!DataClass.isExsist("*", "FiscalYear = " + Rpt_Year, "GLFiscalPeriodSetup"))
{
rr[1, 1] = 0;
}
else
{
string PeriodID = DataClass.ReturnRecordNameByID("Select PeriodID" + Rpt_Prd + " From GLFiscalPeriodSetup WHERE FiscalYear = " + Rpt_Year);
DateTime Start_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodStartDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID));
DateTime End_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodEndDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID));
switch (dtCols.Rows[col]["Col_Range"].ToString())
{
case "YTD":
PeriodID = DataClass.ReturnRecordNameByID("Select PeriodID1 From GLFiscalPeriodSetup WHERE FiscalYear = " + Rpt_Year);
Start_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodStartDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID));
break;
case "DAY":
Start_Date = Start_Date.AddDays(Start_Date.Day * -1).AddDays(DateTime.Now.Day);
End_Date = Start_Date;
break;
case "BEG":
End_Date = Start_Date.AddDays(-1);
PeriodID = DataClass.ReturnRecordNameByID("Select PeriodID1 From GLFiscalPeriodSetup WHERE FiscalYear = " + Rpt_Year);
Start_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodStartDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID));
BEG = " + " + DataClass.ReturnRecordNameByID("SELECT { fn IFNULL(Sum(BeginningBalance), 0) } AS Amt FROM dbo.GLTotals WHERE (Convert(int,replace(AccountNumber,'-','')) Between " + Accounts + ") AND ({ fn IFNULL(FiscalYear, 0) } = " + Rpt_Year.ToString() + ") ");
break;
}
System.Collections.ArrayList ItemsNames = new System.Collections.ArrayList();
System.Collections.ArrayList ItemsValues = new System.Collections.ArrayList();
ItemsNames.Add("Rpt_Fdate");
ItemsValues.Add(Start_Date.Date);
ItemsNames.Add("Rpt_Tdate");
ItemsValues.Add(End_Date.Date);
string type = ">=";
if (dtCols.Rows[col]["Col_Type"].ToString() == "CR")
{
type = "<";
}
DataTable DT = new DataTable();
if ((bool)dtCols.Rows[col]["Daily_Rev"] && dtCols.Rows[col]["Col_Range"].ToString() != "BEG" && dtCols.Rows[col]["Col_Source"].ToString() != "BGT")
{
DT = DataClass.SelectRecord("SELECT { fn IFNULL(SUM(GLT.AmountLC), 0) } " + BEG + " AS Amt FROM dbo.Batch AS B INNER JOIN dbo.GLTransactions AS GLT ON B.BatchNo = GLT.BatchNo WHERE (B.BatchStat = 'P') AND (GLT.AmountLC " + type + " 0) AND (Convert(int,replace(GLT.GLAccount,'-','')) Between " + Accounts + ") AND (B.BatchDate Between @Rpt_Fdate AND @Rpt_Tdate ) AND (B.BatchJNL IN (Select JournalCodeID From G_Report_Journal Where Rpt_Code = '" + Rpt_Code + "'))", ItemsNames, ItemsValues);
}
else
{
DT = DataClass.SelectRecord("SELECT { fn IFNULL(SUM(GLT.AmountLC), 0) } " + BEG + " AS Amt FROM dbo.Batch AS B INNER JOIN dbo.GLTransactions AS GLT ON B.BatchNo = GLT.BatchNo WHERE (B.BatchStat = 'P') AND (GLT.AmountLC " + type + " 0) AND (Convert(int,replace(GLT.GLAccount,'-','')) Between " + Accounts + ") AND (B.BatchDate Between @Rpt_Fdate AND @Rpt_Tdate )", ItemsNames, ItemsValues);
}
if (DT.Rows.Count > 0)
{
rr[1, 1] = double.Parse(DT.Rows[0]["Amt"].ToString());
}
}
}
else if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "TOT")
{
string RTF = dtRows.Rows[row]["Row_TOT_Formula"].ToString();
RTF = RTF.Replace("00", "");
RTF = RTF.Replace("A", dtCols.Rows[col]["Col_No"].ToString());
rr[1, 1] = "=" + RTF.Trim();
}
}
else if (dtCols.Rows[col]["Col_Type"].ToString() == "PCT")
{
if (dtCols.Rows[col]["Col_Prec_Source"].ToString().Trim() != "" && dtRows.Rows[row]["Row_Prec_Row"].ToString().Trim() != "")
{
string CPS = dtCols.Rows[col]["Col_Prec_Source"].ToString();
string RPR = (int.Parse(dtRows.Rows[row]["Row_Prec_Row"].ToString()) / 100).ToString();
string RN = (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString();
rr[1, 1] = "=IF(" + CPS + "" + RPR + "=0, 0, " + CPS + "" + RN + "/" + CPS + "" + RPR + "*100)";
}
}
else if (dtCols.Rows[col]["Col_Type"].ToString() == "CALC")
{
if (dtCols.Rows[col]["Col_Calc_Formula"].ToString().Trim() != "")
{
string CCF = dtCols.Rows[col]["Col_Calc_Formula"].ToString();
string RN = (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString();
CCF = CCF.Replace("0", RN);
if (dtCols.Rows[col]["Col_Calc_Divide"].ToString().Trim() != "")
{
string CCD = dtCols.Rows[col]["Col_Calc_Divide"].ToString();
CCD = CCD.Replace("0", RN);
CCF = "IF((" + CCD + ")=0,0,(" + CCF + "/ (" + CCD + ")))";
}
rr[1, 1] = "=" + CCF.Trim();
}
}
else if (dtCols.Rows[col]["Col_Type"].ToString() == "DESC")
{
rr[1, 1] = dtRows.Rows[row]["Row_Desc"].ToString();
rr.HorizontalAlignment = 3;
rr.VerticalAlignment = 3;
}
}
}
else if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "DESC")
{
rr = mywsh.get_Range("A" + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(), letter + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString());
rr.MergeCells = true;
rr[1, 1] = dtRows.Rows[row]["Row_Desc"].ToString();
rr.HorizontalAlignment = 3;
rr.VerticalAlignment = 3;
}
else if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "HDL")
{
rr = mywsh.get_Range("A" + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(), letter + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString());
rr.MergeCells = true;
rr[1, 1] = dtRows.Rows[row]["Row_Desc"].ToString();
rr.HorizontalAlignment = 1;
rr.VerticalAlignment = 3;
}
else if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "HDC")
{
rr = mywsh.get_Range("A" + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(), letter + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString());
rr.MergeCells = true;
rr[1, 1] = dtRows.Rows[row]["Row_Desc"].ToString();
rr.HorizontalAlignment = 3;
rr.VerticalAlignment = 3;
}
else if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "HDR")
{
rr = mywsh.get_Range("A" + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(), letter + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString());
rr.MergeCells = true;
rr[1, 1] = dtRows.Rows[row]["Row_Desc"].ToString();
rr.HorizontalAlignment = 4;
rr.VerticalAlignment = 3;
}
else if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "---")
{
rr = mywsh.get_Range("A" + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(), letter + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString());
rr.MergeCells = true;
rr.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
rr.RowHeight = 2;
}
else if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "===")
{
rr = mywsh.get_Range("A" + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(), letter + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString());
rr.MergeCells = true;
rr.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDouble;
rr.RowHeight = 2;
}
}
for (int col = 0; col < dtCols.Rows.Count; col++)
{
rr = mywsh.get_Range(dtCols.Rows[col]["Col_No"].ToString() + "1", dtCols.Rows[col]["Col_No"].ToString() + dtRows.Rows.Count.ToString());
double i = double.Parse(rr.ColumnWidth.ToString());
double.TryParse(dtCols.Rows[col]["Col_Width"].ToString(), out i);
if ((bool)dtCols.Rows[col]["Col_Sup"])
{
rr.ColumnWidth = 0;
}
else
{
rr.ColumnWidth = i;
}
}
for (int row = 0; row < dtRows.Rows.Count; row++)
{
rr = mywsh.get_Range("A" + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(), dtCols.Rows[dtCols.Rows.Count - 1]["Col_No"].ToString() + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString());
rr.NumberFormat = dtRows.Rows[row]["Row_Format"].ToString().Trim();
if ((bool)dtRows.Rows[row]["Row_Sup"])
{
rr.RowHeight = 0;
}
}
rr = mywsh.get_Range("A1", letter + "1");
rr.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, null);
rr.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, null);
rr.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, null);
for (int col = 0; col < dtCols.Rows.Count; col++)
{
rr = mywsh.get_Range(dtCols.Rows[col]["Col_No"].ToString() + "1", dtCols.Rows[col]["Col_No"].ToString() + "2");
switch (dtCols.Rows[col]["Col_Type"].ToString())
{
case "PCT":
rr[1, 1] = "";
rr[2, 1] = "%";
break;
case "CALC":
rr[1, 1] = "";
rr[2, 1] = dtCols.Rows[col]["Col_Header"].ToString();
break;
case "DB":
rr[1, 1] = dtCols.Rows[col]["Col_Range"].ToString() + "(" + "Debit)";
rr[2, 1] = dtCols.Rows[col]["Col_Header"].ToString();
break;
case "CR":
rr[1, 1] = dtCols.Rows[col]["Col_Range"].ToString() + "(" + "Credits)";
rr[2, 1] = dtCols.Rows[col]["Col_Header"].ToString();
break;
case "GL":
rr[1, 1] = dtCols.Rows[col]["Col_Range"].ToString();
rr[2, 1] = dtCols.Rows[col]["Col_Header"].ToString();
break;
}
rr.HorizontalAlignment = 3;
rr.VerticalAlignment = 3;
}
rr = mywsh.get_Range("A3", letter + "3");
rr.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
rr.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
rr.MergeCells = true;
rr[1, 1] = "Period " + Rpt_Prd.ToString() + "/" + Rpt_Year.ToString();
rr.HorizontalAlignment = 3;
rr.VerticalAlignment = 3;
rr.Font.Bold = true;
//rr.Columns.AutoFit();
myexl.Visible = true;
}
catch (Exception ex)
{ MessageBox.Show(ex.Message, "System Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
finally
{
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
Function.StateThrid = "";
Thread.CurrentThread.Abort();
}
}
/// <summary>
/// 打印
/// </summary>
private void printInfo()
{
Object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application m_objExcel = null;
Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
Microsoft.Office.Interop.Excel.Range m_objRange = null;
try
{
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
DirectoryInfo Dir = new DirectoryInfo(".");
m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "/Templete/Register.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));
// 号牌种类
m_objRange = m_objSheet.get_Range("D11", oMissing);
m_objRange.Value = this.category.Text;
// 号牌号码
m_objRange = m_objSheet.get_Range("H11", oMissing);
m_objRange.Value = "辽B" + this.license.Text;
// 品牌型号
m_objRange = m_objSheet.get_Range("D12", oMissing);
m_objRange.Value = this.brand.Text;
//车辆识别代号
m_objRange = m_objSheet.get_Range("H12", oMissing);
m_objRange.Value = this.vin.Text;
// 机动车所有人姓名/名称
m_objRange = m_objSheet.get_Range("E3", oMissing);
m_objRange.Value = this.ownerName.Text;
// 机动车所有人邮寄地址
m_objRange = m_objSheet.get_Range("E4", oMissing);
m_objRange.Value = this.ownerAddress.Text;
// 机动车所有人邮政编码
m_objRange = m_objSheet.get_Range("I3", oMissing);
m_objRange.Value = this.ownerPostcode.Text;
// 机动车所有人固定电话
m_objRange = m_objSheet.get_Range("I5", oMissing);
m_objRange.Value = this.ownerPhone.Text;
// 机动车所有人移动电话
m_objRange = m_objSheet.get_Range("E5", oMissing);
m_objRange.Value = this.ownerMobile.Text;
// 省（自治县、直辖市)
m_objRange = m_objSheet.get_Range("D10", oMissing);
m_objRange.Value = " " + this.province.Text + " " + this.department.Text;
// 代理人姓名、名称
m_objRange = m_objSheet.get_Range("E6", oMissing);
m_objRange.Value = this.agentName.Text;
// 代理人联系电话
m_objRange = m_objSheet.get_Range("H6", oMissing);
m_objRange.Value = this.agentPhone.Text;
// 办理日期
m_objRange = m_objSheet.get_Range("F18", oMissing);
m_objRange.Value = DateTime.Today.Year + " " + DateTime.Today.Month + " " + DateTime.Today.Day;
m_objExcel.DisplayAlerts = false;
m_objBook.Save();
m_objSheet.PrintOut();
}
catch (Exception ex)
{
// 输出异常信息
MessageBox.Show("打印失败,异常信息为：" + ex.Message);
}
finally
{
if (m_objBook != null)
{
m_objBook.Close(oMissing, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
}
if (m_objExcel != null)
{
m_objExcel.Workbooks.Close();
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
m_objBook = null;
m_objExcel = null;
GC.Collect();
}
}
private void buttonExcel_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); // Creates a new Excel Application
excelApp.Visible = true; // Makes Excel visible to the user.
// The following line adds a new workbook
Microsoft.Office.Interop.Excel.Workbook newWorkbook = excelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
// The following code opens an existing workbook
string workbookPath = Application.StartupPath + "\\template.xls"; // Add your own path here
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,
false, 0, true, false, false);
// The following gets the Worksheets collection
Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
// The following gets Sheet1 for editing
string currentSheet = "Бланк";
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet);
// The following gets cell A1 for editing
//Microsoft.Office.Interop.Excel.Range excelCell = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("A1", "A1");
// The following sets cell A1's value to "Hi There"
//excelCell.Value2 = "Hi There";
_Ekskursovody a;
int n = queueAccounts.Count;
for (int i = 0; i < n; i++)
{
a = (_Ekskursovody)queueAccounts.Dequeue();
queueAccounts.Enqueue(a);
if (a.id == m_journal.account_id.ToString())
{
Microsoft.Office.Interop.Excel.Range excelCellAccount = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("C8", "C8");
excelCellAccount.Value2 = a.name;
break;
}
}
Microsoft.Office.Interop.Excel.Range excelCellID = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("D11", "D11");
excelCellID.Value2 = m_journal.id.ToString();
Microsoft.Office.Interop.Excel.Range excelCellStudent = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("E14", "F14");
excelCellStudent.Value2 = m_journal.student;
_PaymentNames pn;
n = queuePaymentNames.Count;
for (int i = 0; i < n; i++)
{
pn = (_PaymentNames)queuePaymentNames.Dequeue();
queuePaymentNames.Enqueue(pn);
if (pn.id == m_journal.payment_name_id)
{
Microsoft.Office.Interop.Excel.Range excelCellPaymentName = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("B19", "C19");
excelCellPaymentName.Value2 = pn.name;
break;
}
}
_Semester s;
n = queueSemesters.Count;
for (int i = 0; i < n; i++)
{
s = (_Semester)queueSemesters.Dequeue();
queueSemesters.Enqueue(s);
if (s.id == m_journal.semester)
{
Microsoft.Office.Interop.Excel.Range excelCellSemester = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("B20", "C20");
excelCellSemester.Value2 = s.name;
break;
}
;
}
_Pdv p;
n = queuePdv.Count;
for (int i = 0; i < n; i++)
{
p = (_Pdv)queuePdv.Dequeue();
queuePdv.Enqueue(p);
if (p.id == m_journal.pdv)
{
Microsoft.Office.Interop.Excel.Range excelCellPdv = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("E22", "E22");
excelCellPdv.Value2 = p.name;
break;
}
;
}
_EduYears ey;
n = queueEduYears.Count;
for (int i = 0; i < n; i++)
{
ey = (_EduYears)queueEduYears.Dequeue();
queueEduYears.Enqueue(ey);
if (ey.id.ToString() == m_journal.edu_year_id.ToString())
{
Microsoft.Office.Interop.Excel.Range excelCellSemester = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("B21", "C21");
excelCellSemester.Value2 = ey.name;
break;
}
;
}
Microsoft.Office.Interop.Excel.Range excelCellSum = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("F21", "F21");
excelCellSum.Value2 = m_journal.sum;
String date = "";
if (DateTime.Parse(m_journal.date).Day < 10)
{
date += "0";
}
date += DateTime.Parse(m_journal.date).Day;
date += "/";
if (DateTime.Parse(m_journal.date).Month < 10)
{
date += "0";
}
date += DateTime.Parse(m_journal.date).Month;
date += "/";
date += DateTime.Parse(m_journal.date).Year;
Microsoft.Office.Interop.Excel.Range excelCellDate = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("F11", "F11");
excelCellDate.Value2 = date;
}
private void buttonExcel_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
BringToFront();
string workbookPath = Application.StartupPath + "\\templates/zhurnal.xls";
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,
false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Бланк";
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet);
int i = 1;
int i2 = 4;
foreach (ListViewItem lvi in listViewZhurnal.Items)
{
i = 1;
foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems)
{
excelWorksheet.Cells[i2, i] = lvs.Text;
i++;
}
i2++;
}
}
// 完全读取 excel
/// <summary>
/// 解析Excel，返回DataTable
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public System.Data.DataTable ImpExcel(string fileName)
{
System.Data.DataTable dt = new System.Data.DataTable();
dt.TableName = excelsheetname.ToString();
try
{
//Microsoft.Office.Interop.Excel.Application app;
//Microsoft.Office.Interop.Excel.Workbook wbs;
// Microsoft.Office.Interop.Excel.Worksheet ws;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); //新建文件
// 多数使用缺省值 (除了 read-only我们设置它为 true)
Microsoft.Office.Interop.Excel.Workbook wbs = app.Workbooks.Open(fileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null);
// 取得工作簿（workbook）中表单的集合（sheets）
Microsoft.Office.Interop.Excel.Sheets wss = wbs.Worksheets;
// 取得表单集合中唯一的一个表（worksheet）
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wss.get_Item(1);
//app = new Microsoft.Office.Interop.Excel.Application();
//wbs = app.Workbooks.Open(fileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null);
//ws = (Microsoft.Office.Interop.Excel.Worksheet)app.Worksheets.get_Item(1);
//int a = ws.Rows.Count;
int a = ws.UsedRange.Cells.Rows.Count + 2;
//int b = ws.Columns.Count;
int b = 20;
string name = ws.Name;
for (int i = 1; i < a; i++)
{
if (i == 1)
{
for (int j = 1; j <= b; j++)
{
Microsoft.Office.Interop.Excel.Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]);
range.Select();
string columnnames = app.ActiveCell.Text.ToString();
dt.Columns.Add(columnnames, typeof(string));
}
}
else
{
DataRow dr = dt.NewRow();
for (int j = 1; j <= b; j++)
{
Microsoft.Office.Interop.Excel.Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]);
range.Select();
int rowco = j - 1;
string cellc = app.ActiveCell.Text.ToString();;
dr[rowco] = cellc;
}
dt.Rows.Add(dr);
}
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
ws = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(wss);
wss = null;
wbs.Close(true, Type.Missing, Type.Missing);
//(Microsoft.Office.Interop.Excel.Worksheet)wbs.Close(true, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
wbs = null;
app.Application.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
int generation = System.GC.GetGeneration(app);
Kill(app);
app = null;
System.GC.Collect(generation);
return(dt);
}
catch (Exception ex)
{
// MessageBox.Show("数据绑定Excel失败! 失败原因：Excel格式不正确！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(dt);
}
finally
{
GC.Collect(); //垃圾回收
GC.WaitForPendingFinalizers();
}
}
private static void exportexcel(System.Data.DataTable dt, string filename, string charttitle, bool tableflag, bool bl)
{
//System.Data.DataTable dt = new System.Data.DataTable();
if (dt == null)
{
return;
}
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel.Series oSeries;
//Microsoft.Office.Interop.Excel.Range oResizeRange;
Microsoft.Office.Interop.Excel._Chart oChart;
//String sMsg;
//int iNumQtrs;
GC.Collect();//系统的垃圾回收
//string filename = @"C:\Documents and Settings\tongxl\桌面\nnn.xls";
//Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(filename);
Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(true);
if (ep == null)
{
MsgBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
ep.Visible = true;
Microsoft.Office.Interop.Excel.Sheets sheets = wb.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet ws = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);// [System.Type.Missing];//.get.get_Item("xx");
ws.UsedRange.Select();
ws.UsedRange.Copy(System.Type.Missing);
// wb.Charts.Add(System.Type.Missing, System.Type.Missing, 1, System.Type.Missing);
int rowIndex = 1;
int colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
ws.Cells[rowIndex, colIndex++] = col.ColumnName;
}
for (int drvIndex = 0; drvIndex < dt.Rows.Count; drvIndex++)
{
DataRow row = dt.Rows[drvIndex];
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
ws.Cells[drvIndex + 2, colIndex] = row[col.ColumnName].ToString();
colIndex++;
}
}
oWB = (Microsoft.Office.Interop.Excel._Workbook)ws.Parent;
oChart = (Microsoft.Office.Interop.Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
int rcount = dt.Rows.Count;
int ccount = dt.Columns.Count;
oChart.ChartWizard(ws.get_Range(ws.Cells[1, 1], ws.Cells[rcount + 2, ccount + 2]), Microsoft.Office.Interop.Excel.XlChartType.xlLine, Missing.Value,
Microsoft.Office.Interop.Excel.XlRowCol.xlRows, 1, true, true,
charttitle, Missing.Value, Missing.Value, Missing.Value);
// oSeries = (Microsoft.Office.Interop.Excel.Series)oChart.SeriesCollection(1);
//string str = String.Empty;
//for (int I = 1; I < 15; I++)
//{
// str += I.ToString() + "\t";
//}
//try { oSeries.XValues = str; }
//catch { }
// oSeries.HasDataLabels = true;
// string charttitle ,bool tableflag ,bool bl)
if (tableflag == true)
{
oChart.HasDataTable = true;
}
else
{
oChart.HasDataTable = false;
}
oChart.PlotVisibleOnly = false;
//
Microsoft.Office.Interop.Excel.Axis axis = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
Microsoft.Office.Interop.Excel.XlAxisType.xlValue,
Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
//axis.HasTitle = true;
//axis.AxisTitle.Text = "Sales Figures";
// axis.HasMinorGridlines=true;
Microsoft.Office.Interop.Excel.Axis ax = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
//ax.HasTitle = true;
//ax.AxisTitle.Text = "Sales Figures";
ax.HasMajorGridlines = true;
// string filename = @"C:\Documents and Settings\tongxl\桌面\ccsb.xls";
// ws.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
try
{
wb.Saved = true;
wb.SaveCopyAs(filename);
}
catch (Exception ex)
{
MsgBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
ep.Quit();
GC.Collect();//强行销毁
}
public void Export(string fileName)
{
if (values.Count == 0)
{
throw new Exception("Данные не заданы");
}
var excel = new Microsoft.Office.Interop.Excel.Application();
try
{
if (File.Exists(fileName))
{
excel.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
excel.SheetsInNewWorkbook = 2;
excel.Workbooks.Add(Type.Missing);
excel.Workbooks[1].SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, Type.Missing,
Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
var workbook = excel.Workbooks[1];
Microsoft.Office.Interop.Excel.Sheets excelsheets = workbook.Worksheets;
var excelworksheetData = (Microsoft.Office.Interop.Excel.Worksheet)excelsheets.get_Item(1);
excelworksheetData.Cells.Clear();
excelworksheetData.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
excelworksheetData.PageSetup.CenterHorizontally = true;
excelworksheetData.PageSetup.CenterVertically = true;
for (var i = 1; i <= values.Count; i++)
{
if (i <= captions.Count)
{
excelworksheetData.Range["A" + i].Value = captions[i - 1];
}
excelworksheetData.Range["B" + i].Value = values[i - 1];
}
excelworksheetData.Name = "Data";
var excelworksheetDia = (Microsoft.Office.Interop.Excel.Worksheet)excelsheets.get_Item(2);
excelworksheetDia.Name = "Diagram";
var charts = excelworksheetDia.ChartObjects() as Microsoft.Office.Interop.Excel.ChartObjects;
var chartObject = charts.Add(60, 10, 300, 300) as Microsoft.Office.Interop.Excel.ChartObject;
var chart = chartObject.Chart;
chart.ChartWizard(excelworksheetData.Range["A1", $"B{values.Count}"], Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn, "New Chart");
excel.Workbooks[1].Save();
}
catch (Exception e)
{
throw e;
}
finally
{
excel.Quit();
}
releaseObject(excel);
}
/// <summary>
/// Extension method to write list data to Microsoft.Office.Interop.Excel File.
/// </summary>
/// <typeparam name="T">Ganeric list</typeparam>
/// <param name="list"></param>
/// <param name="PathToSave">Path to save file.</param>
public static void ToExcel <T>(this List <T> list, string PathToSave)
{
#region Declarations
if (string.IsNullOrEmpty(PathToSave))
{
throw new Exception("Invalid file path.");
}
else if (PathToSave.ToLower().Contains("") == false)
{
throw new Exception("Invalid file path.");
}
if (list == null)
{
throw new Exception("No data to export.");
}
Microsoft.Office.Interop.Excel.Application excelApp = null;
Microsoft.Office.Interop.Excel.Workbooks books = null;
Microsoft.Office.Interop.Excel._Workbook book = null;
Microsoft.Office.Interop.Excel.Sheets sheets = null;
Microsoft.Office.Interop.Excel._Worksheet sheet = null;
Microsoft.Office.Interop.Excel.Range range = null;
Microsoft.Office.Interop.Excel.Font font = null;
// Optional argument variable
object optionalValue = Missing.Value;
string strHeaderStart = "A2";
string strDataStart = "A3";
#endregion
#region Processing
try
{
#region Init Microsoft.Office.Interop.Excel app.
excelApp = new Microsoft.Office.Interop.Excel.Application();
books = (Microsoft.Office.Interop.Excel.Workbooks)excelApp.Workbooks;
book = (Microsoft.Office.Interop.Excel._Workbook)(books.Add(optionalValue));
sheets = (Microsoft.Office.Interop.Excel.Sheets)book.Worksheets;
sheet = (Microsoft.Office.Interop.Excel._Worksheet)(sheets.get_Item(1));
#endregion
#region Creating Header
Dictionary <string, string> objHeaders = new Dictionary <string, string>();
PropertyInfo[] headerInfo = typeof(T).GetProperties();
foreach (var property in headerInfo)
{
var attribute = property.GetCustomAttributes(typeof(DisplayNameAttribute), false)
.Cast <DisplayNameAttribute>().FirstOrDefault();
objHeaders.Add(property.Name, attribute == null ?
property.Name : attribute.DisplayName);
}
range = sheet.get_Range(strHeaderStart, optionalValue);
range = range.get_Resize(1, objHeaders.Count);
range.set_Value(optionalValue, objHeaders.Values.ToArray());
range.BorderAround(Type.Missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
font = range.Font;
font.Bold = true;
range.Interior.Color = Color.LightGray.ToArgb();
#endregion
#region Writing data to cell
int count = list.Count;
object[,] objData = new object[count, objHeaders.Count];
for (int j = 0; j < count; j++)
{
var item = list[j];
int i = 0;
foreach (KeyValuePair <string, string> entry in objHeaders)
{
var y = typeof(T).InvokeMember(entry.Key.ToString(), BindingFlags.GetProperty, null, item, null);
objData[j, i++] = (y == null) ? "" : y.ToString();
}
}
range = sheet.get_Range(strDataStart, optionalValue);
range = range.get_Resize(count, objHeaders.Count);
range.set_Value(optionalValue, objData);
range.BorderAround(Type.Missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
range = sheet.get_Range(strHeaderStart, optionalValue);
range = range.get_Resize(count + 1, objHeaders.Count);
range.Columns.AutoFit();
#endregion
#region Saving data and Opening Microsoft.Office.Interop.Excel file.
if (string.IsNullOrEmpty(PathToSave) == false)
{
book.SaveCopyAs(PathToSave);
}
excelApp.Visible = true;
#endregion
#region Release objects
try
{
if (sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
sheet = null;
if (sheets != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
}
sheets = null;
if (book != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
}
book = null;
if (books != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
}
books = null;
if (excelApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}
excelApp = null;
}
catch (Exception ex)
{
sheet = null;
sheets = null;
book = null;
books = null;
excelApp = null;
}
finally
{
GC.Collect();
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
#endregion
}
private void buttonOst_Click(object sender, EventArgs e)
{
//массив0 выдано
refreshOst();
int[,] arr = new int[listViewBilety2.Items.Count, listViewBilety2.Columns.Count];
for (int x = 0, maxX = listViewBilety2.Items.Count; x < maxX; x++)
{
for (int y = 0, maxY = listViewBilety2.Columns.Count; y < maxY; y++)
{
arr[x, y] = int.Parse(listViewBilety2.Items[x].SubItems[y].Text.Trim());
}
}
//массив1 сдано
refreshOst1();
int[,] arr1 = new int[listViewBilety2.Items.Count, listViewBilety2.Columns.Count];
for (int x = 0, maxX = listViewBilety2.Items.Count; x < maxX; x++)
{
for (int y = 0, maxY = listViewBilety2.Columns.Count; y < maxY; y++)
{
arr1[x, y] = int.Parse(listViewBilety2.Items[x].SubItems[y].Text.Trim());
}
}
//массив2 названия
refreshOst2();
string[,] b = new string[listViewBilety2.Items.Count, listViewBilety2.Columns.Count];
for (int x = 0, maxX = listViewBilety2.Items.Count; x < maxX; x++)
{
for (int y = 0, maxY = listViewBilety2.Columns.Count; y < maxY; y++)
{
b[x, y] = listViewBilety2.Items[x].SubItems[y].Text;
}
}
//refreshBilety2();
listViewBilety2.Items.Clear();
listViewBilety2.Columns.Clear();
List <int> ARR = new List <int>();
List <int> ARR1 = new List <int>();
List <int> ARR2 = new List <int>();
List <int> ARR3 = new List <int>();
List <int> unique = new List <int>();
List <object> unique2 = new List <object>();
//диапазоны из нач/кон значений выдано
for (int x = 0; x <= ((arr.Length / 2) - 1); x++)
{
for (int i = arr[x, 0], maxi = arr[x, 1]; i <= maxi; i++)
{
ARR.Add(i);
}
}
//диапазоны из нач/кон значений сдано
for (int x = 0; x <= ((arr1.Length / 2) - 1); x++)
{
for (int i = arr1[x, 0], maxi = arr1[x, 1]; i <= maxi; i++)
{
ARR1.Add(i);
}
}
//удаление того, что есть в журнале, но нет в билетах
for (int x = 0; x < ARR.Count; x++)
{
for (int y = 0; y < ARR1.Count; y++)
{
if (ARR[x].Equals(ARR1[y]))
{
ARR3.Add(ARR[x]);
}
}
}
//объединение списков
ARR2.AddRange(ARR.ToArray());
ARR2.AddRange(ARR3.ToArray());
ARR2.Sort();
//удаление двойных
for (int m = 0; m < ARR2.Count; m++)
{
for (int j = m + 1; j < ARR2.Count; j++)
{
if (ARR2[j].Equals(ARR2[m]))
{
ARR2.RemoveAt(j--);
ARR2.RemoveAt(m);
}
}
}
//приведение к начальным
for (int x = 0; x <= ((arr.Length / 2) - 1); x++)
{
for (int i = arr[x, 0], maxi = arr[x, 1]; i <= maxi; i++)
{
if (ARR2.Contains(i))
{
unique.Add(x + 1);
unique.Add(i);
}
}
}
//собственно диапазоны остатка
for (int x = 0; x < unique.Count; x = x + 2)
{
int m;
if (x == 0)
{
m = x + 1;
}
else
{
m = x - 2;
}
if (!unique[x].Equals(unique[m]))
{
unique2.Add(unique[x]);
unique2.Add(unique[x + 1]);
}
int f;
if (x < unique.Count - 2)
{
f = x + 2;
}
else
{
f = x - 1;
}
if (!unique[x].Equals(unique[f]))
{
unique2.Add(unique[x + 1]);
for (int y = 0; y < b.Length / 3; y++)
{
if (unique[x + 1].Equals(int.Parse(b[y, 0])))
{
unique2.Add(b[y, 1]);
unique2.Add(b[y, 2]);
}
}
}
}
//массив для екселя
object[,] c = new object[unique2.Count, unique2.Count / 3];
for (int i = 0, k = 0; i < unique2.Count; i = i + 5, k++)
{
c[k, 0] = unique2[i];
}
for (int i = 1, k = 0; i < unique2.Count; i = i + 5, k++)
{
c[k, 1] = unique2[i];
}
for (int i = 2, k = 0; i < unique2.Count; i = i + 5, k++)
{
c[k, 2] = unique2[i];
}
for (int i = 3, k = 0; i < unique2.Count; i = i + 5, k++)
{
c[k, 3] = unique2[i];
}
for (int i = 4, k = 0; i < unique2.Count; i = i + 5, k++)
{
c[k, 4] = unique2[i];
}
object[] a = unique2.ToArray();
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
BringToFront();
string workbookPath = Application.StartupPath + "\\templates/bilety.xls";
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,
false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Бланк";
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet);
Microsoft.Office.Interop.Excel.Range excelCellName = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("A4", "E" + unique2.Count);
excelCellName.Value2 = c;
}
public static string multiSheetExcel(DataSet ds)
{
DataTable table100 = ds.Tables[0];
DataTable table50first = ds.Tables[1];
DataTable table50second = ds.Tables[2];
DataTable table50full = ds.Tables[3];
DataTable table5050 = ds.Tables[4];
DataTable tableCn = ds.Tables[5];
DataTable tableRmb = ds.Tables[6];
string filename = "";
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = excelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;
for (int i = sheets.Count; i > 1; i--)
{
((Microsoft.Office.Interop.Excel.Worksheet)sheets[i]).Delete();
}
for (int i = 1; i <= 6; i++)
{
workbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i);
}
Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[1];
sheet1.Name = "100% Normal";
for (int i = 0; i < table100.Columns.Count; i++)
{
sheet1.Cells[1, i + 1] = table100.Columns[i].ColumnName;
}
for (int i = 0; i < table100.Rows.Count; i++)
{
for (int j = 0; j < table100.Columns.Count; j++)
{
sheet1.Cells[i + 2, j + 1] = table100.Rows[i][j].ToString();
}
}
Microsoft.Office.Interop.Excel.Worksheet sheet2 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[2];
sheet2.Name = "50% MPA 1st 50%";
for (int i = 0; i < table50first.Columns.Count; i++)
{
sheet2.Cells[1, i + 1] = table50first.Columns[i].ColumnName;
}
for (int i = 0; i < table50first.Rows.Count; i++)
{
for (int j = 0; j < table50first.Columns.Count; j++)
{
sheet2.Cells[i + 2, j + 1] = table50first.Rows[i][j].ToString();
}
}
Microsoft.Office.Interop.Excel.Worksheet sheet3 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[3];
sheet3.Name = "50% MPA 2nd 50%";
for (int i = 0; i < table50second.Columns.Count; i++)
{
sheet3.Cells[1, i + 1] = table50second.Columns[i].ColumnName;
}
for (int i = 0; i < table50second.Rows.Count; i++)
{
for (int j = 0; j < table50second.Columns.Count; j++)
{
sheet3.Cells[i + 2, j + 1] = table50second.Rows[i][j].ToString();
}
}
Microsoft.Office.Interop.Excel.Worksheet sheet4 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[4];
//Microsoft.Office.Interop.Excel.Worksheet sheet4 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets.Add(After: excelApp.Sheets[excelApp.Sheets.Count]);
sheet4.Name = "50% MPA 100% 2pcs";
for (int i = 0; i < table50full.Columns.Count; i++)
{
sheet4.Cells[1, i + 1] = table50full.Columns[i].ColumnName;
}
for (int i = 0; i < table50full.Rows.Count; i++)
{
for (int j = 0; j < table50full.Columns.Count; j++)
{
sheet4.Cells[i + 2, j + 1] = table50full.Rows[i][j].ToString();
}
}
Microsoft.Office.Interop.Excel.Worksheet sheet5 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[5];
sheet5.Name = "50% MPA 100% 1+1 pcs";
for (int i = 0; i < table5050.Columns.Count; i++)
{
sheet5.Cells[1, i + 1] = table5050.Columns[i].ColumnName;
}
for (int i = 0; i < table5050.Rows.Count; i++)
{
for (int j = 0; j < table5050.Columns.Count; j++)
{
sheet5.Cells[i + 2, j + 1] = table5050.Rows[i][j].ToString();
}
}
Microsoft.Office.Interop.Excel.Worksheet sheet6 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[6];
sheet6.Name = "OEM=CN";
for (int i = 0; i < tableCn.Columns.Count; i++)
{
sheet6.Cells[1, i + 1] = tableCn.Columns[i].ColumnName;
}
for (int i = 0; i < tableCn.Rows.Count; i++)
{
for (int j = 0; j < tableCn.Columns.Count; j++)
{
sheet6.Cells[i + 2, j + 1] = tableCn.Rows[i][j].ToString();
}
}
Microsoft.Office.Interop.Excel.Worksheet sheet7 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[7];
sheet7.Name = "RMB";
for (int i = 0; i < tableRmb.Columns.Count; i++)
{
sheet7.Cells[1, i + 1] = tableRmb.Columns[i].ColumnName;
}
for (int i = 0; i < tableRmb.Rows.Count; i++)
{
for (int j = 0; j < tableRmb.Columns.Count; j++)
{
sheet7.Cells[i + 2, j + 1] = tableRmb.Rows[i][j].ToString();
}
}
SaveFileDialog sfd = new SaveFileDialog()
{
DefaultExt = "xls",
Filter = "EXCEL Files (*.xls)|*.xls",
FilterIndex = 1,
FileName = DateTime.Today.ToString("yyyyMMdd") + "_ReceivedPO"// + year + month + day,
};
if (sfd.ShowDialog() == DialogResult.OK)
{
try
{
filename = sfd.FileName;
excelApp.ActiveWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel5, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//excelApp.ActiveWorkbook.Saved = true;
//excelApp.AlertBeforeOverwriting = false;
excelApp.Quit();
MessageBox.Show("Record saved to " + filename);
}
catch (COMException ex)
{
MessageBox.Show(ex.Message + ex.StackTrace);
Debug.WriteLine(ex.Message + ex.StackTrace);
}
}
return(filename);
}
static void ExportToExcel(DataTable dataAdded, DataTable dataModified)
{
Console.WriteLine("Writing to Excel file");
string path = GetOutputFile();
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
oXL.DisplayAlerts = false;
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
//Write to New Contribution
mWSheet = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("GitHub New Contribution");
Microsoft.Office.Interop.Excel.Range range = mWSheet.UsedRange;
int rowCount = range.Rows.Count;
int index = 1;
foreach (DataRow item in dataAdded.Rows)
{
mWSheet.Cells[rowCount + index, 1] = item["StartDate"];
mWSheet.Cells[rowCount + index, 2] = item["EndDate"];
mWSheet.Cells[rowCount + index, 3] = item["GitUser"];
mWSheet.Cells[rowCount + index, 4] = item["AccountType"];
mWSheet.Cells[rowCount + index, 5] = item["TotalContribution"];
mWSheet.Cells[rowCount + index, 6] = item["DataConnectors"];
mWSheet.Cells[rowCount + index, 7] = item["Workbooks"];
mWSheet.Cells[rowCount + index, 8] = item["Playbooks"];
mWSheet.Cells[rowCount + index, 9] = item["Exploration Queries"];
mWSheet.Cells[rowCount + index, 10] = item["Hunting Queries"];
mWSheet.Cells[rowCount + index, 11] = item["Sample Data"];
mWSheet.Cells[rowCount + index, 12] = item["Tools"];
mWSheet.Cells[rowCount + index, 13] = item["Detections"];
mWSheet.Cells[rowCount + index, 14] = item["Notebooks @ efbace2"];
index++;
}
//Write to Update Contribution
mWSheet = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("GitHub Update Contribution");
range = mWSheet.UsedRange;
rowCount = range.Rows.Count;
index = 1;
foreach (DataRow item in dataModified.Rows)
{
mWSheet.Cells[rowCount + index, 1] = item["StartDate"];
mWSheet.Cells[rowCount + index, 2] = item["EndDate"];
mWSheet.Cells[rowCount + index, 3] = item["GitUser"];
mWSheet.Cells[rowCount + index, 4] = item["AccountType"];
mWSheet.Cells[rowCount + index, 5] = item["TotalContribution"];
mWSheet.Cells[rowCount + index, 6] = item["DataConnectors"];
mWSheet.Cells[rowCount + index, 7] = item["Workbooks"];
mWSheet.Cells[rowCount + index, 8] = item["Playbooks"];
mWSheet.Cells[rowCount + index, 9] = item["Exploration Queries"];
mWSheet.Cells[rowCount + index, 10] = item["Hunting Queries"];
mWSheet.Cells[rowCount + index, 11] = item["Sample Data"];
mWSheet.Cells[rowCount + index, 12] = item["Tools"];
mWSheet.Cells[rowCount + index, 13] = item["Detections"];
mWSheet.Cells[rowCount + index, 14] = item["Notebooks @ efbace2"];
index++;
}
mWorkBook.Save();
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
mWSheet = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
Console.WriteLine("Excel file has been save at location: " + Environment.NewLine + path);
}
//写EXCEL
public void saveExcel(string savepath, DataSet dstemp)
{
string filepath = Server.MapPath("Template/Template.xls");
Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application(); //新建文件
// 多数使用缺省值 (除了 read-only我们设置它为 true)
Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(filepath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null);
// 取得工作簿（workbook）中表单的集合（sheets）
Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
// 取得表单集合中唯一的一个表（worksheet）
if (ExcelObj == null)
{
//MessageBox.Show("无法启动Excel，可能您的电脑未安装Excel");
return;
}
try
{
for (int i = 0; i < dstemp.Tables.Count; i++)
{
string dtempname = dstemp.Tables[i].TableName;
int rowIndex = 1;
int colIndex = 0;
int sizeofsheet = sheets.Count;
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(dtempname);
string a = worksheet.Name;
int countrow = dstemp.Tables[dtempname].Rows.Count;
int countcolumn = 0;
if (dtempname == "主管碰头会议决议事项督办清单")
{
countcolumn = 9;
}
else
{
countcolumn = 8;
}
var RowAll = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[countrow + 1, countcolumn]);
RowAll.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
System.Data.DataTable table = dstemp.Tables[dtempname];
foreach (DataColumn col in table.Columns)
{
colIndex++;
worksheet.Cells[2, colIndex] = col.ColumnName;
}
foreach (DataRow row in table.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
worksheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
rowIndex = 1;
colIndex = 0;
}
ExcelObj.DisplayAlerts = false;
ExcelObj.ActiveWorkbook.SaveAs(savepath);
//MessageBox.Show("数据导出成功！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
ExcelObj.Visible = false;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
sheets = null;
theWorkbook.Close(true, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(theWorkbook);
theWorkbook = null;
ExcelObj.Application.Workbooks.Close();
ExcelObj.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelObj);
Kill(ExcelObj);
int generation = System.GC.GetGeneration(ExcelObj);
ExcelObj = null;
System.GC.Collect(generation);
}
catch (Exception ex)
{
//MessageBox.Show(ex.ToString());
}
finally
{
GC.Collect();//垃圾回收
GC.WaitForPendingFinalizers();
}
}
/// <summary>
/// 打印交易票
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void printBtn_Click(object sender, EventArgs e)
{
if (!this.ValidateChildren(ValidationConstraints.Enabled & ValidationConstraints.TabStop & ValidationConstraints.Selectable))
{
MessageBox.Show("控件内容不合法");
return;
}
Object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application m_objExcel = null;
Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
Microsoft.Office.Interop.Excel.Range m_objRange = null;
try
{
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
DirectoryInfo Dir = new DirectoryInfo(".");
m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "\\input.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));
// 开票日期
m_objRange = m_objSheet.get_Range("B1", oMissing);
m_objRange.Value = VehicleDao.GetCurrentDate();
// 买 方 单 位 /个人
m_objRange = m_objSheet.get_Range("C5", oMissing);
m_objRange.Value = this.currentName.Text;
// 单位代码/身份证号码
m_objRange = m_objSheet.get_Range("K5", oMissing);
m_objRange.Value = this.currentId.Text;
// 买方单位/个人住址
m_objRange = m_objSheet.get_Range("C6", oMissing);
m_objRange.Value = this.currentAddress.Text;
// 电话
m_objRange = m_objSheet.get_Range("L6", oMissing);
m_objRange.Value = this.currentPhone.Text;
// 卖 方 单 位/ 个人
m_objRange = m_objSheet.get_Range("C7", oMissing);
m_objRange.Value = this.originName.Text;
// 单位代码/身份证号码
m_objRange = m_objSheet.get_Range("K7", oMissing);
m_objRange.Value = this.originId.Text;
// 卖方单位/个人住址
m_objRange = m_objSheet.get_Range("C8", oMissing);
m_objRange.Value = this.originAddress.Text;
// 电话
m_objRange = m_objSheet.get_Range("L8", oMissing);
m_objRange.Value = this.originPhone.Text;
// 车 牌 照 号
m_objRange = m_objSheet.get_Range("C9", oMissing);
m_objRange.Value = "辽B." + this.license.Text;
// 登记证号
m_objRange = m_objSheet.get_Range("E9", oMissing);
m_objRange.Value = this.certificate.Text;
// 车 辆 类 型
m_objRange = m_objSheet.get_Range("L9", oMissing);
m_objRange.Value = this.vehicleType.Text;
// 车架号/车辆识别代码
m_objRange = m_objSheet.get_Range("C10", oMissing);
m_objRange.Value = this.vin.Text;
// 厂牌型号
m_objRange = m_objSheet.get_Range("E10", oMissing);
m_objRange.Value = this.brand.Text;
// 转入地车辆管理所名称
m_objRange = m_objSheet.get_Range("L10", oMissing);
m_objRange.Value = this.department.Text;
// 车价 合 计（大写）
m_objRange = m_objSheet.get_Range("C11", oMissing);
m_objRange.Value = this.transactions.Text;
// 车价 合 计（小写）
m_objRange = m_objSheet.get_Range("L11", oMissing);
m_objRange.Value = this.transactions.Text;
m_objExcel.DisplayAlerts = false;
m_objSheet.PrintOut();
}
catch (Exception ex)
{
MessageBox.Show("打印失败，请检查打印机设置。错误代码：" + ex.Message);
}
finally
{
if (m_objBook != null)
{
m_objBook.Close(oMissing, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
}
if (m_objExcel != null)
{
m_objExcel.Workbooks.Close();
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
m_objBook = null;
m_objExcel = null;
GC.Collect();
}
}
public DataSet ImportExcel(string filePath)
{
DataSet ds = null;
DataTable dt = null;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = null;
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
Microsoft.Office.Interop.Excel.Sheets sheets = null;
Microsoft.Office.Interop.Excel.Range range = null;
object missing = System.Reflection.Missing.Value;
try
{
if (excel == null)
{
return(null);
}
//打开 Excel 文件
workbook = excel.Workbooks.Open(filePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//获取所有的 sheet 表
sheets = workbook.Worksheets;
ds = new DataSet();
for (int i = 1; i <= sheets.Count; i++)
{
//获取第一个表
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i);
int rowCount = worksheet.UsedRange.Rows.Count;
int colCount = worksheet.UsedRange.Columns.Count;
int rowIndex = 1; //起始行为 1
int colIndex = 1; //起始列为 1
DataColumn dc;
dt = new DataTable();
dt.TableName = "table" + i.ToString();
//读取列名
for (int j = 0; j < colCount; j++)
{
range = worksheet.Cells[rowIndex, colIndex + j];
dc = new DataColumn();
dc.DataType = Type.GetType("System.String");
dc.ColumnName = range.Text.ToString().Trim();
//添加列
dt.Columns.Add(dc);
}
//读取行数据
for (int k = 1; k < rowCount; k++)
{
DataRow dr = dt.NewRow();
for (int l = 0; l < colCount; l++)
{
range = worksheet.Cells[rowIndex + k, colIndex + l];
//使用 range.Value.ToString(); 或 range.Value2.ToString(); 或 range.Text.ToString(); 都可以获取单元格的值
dr[l] = range.Text.ToString();
}
dt.Rows.Add(dr.ItemArray);
}
ds.Tables.Add(dt);
}
}
catch (Exception ex)
{
throw;
}
finally
{
workbook.Close();
//关闭退出
excel.Quit();
//释放 COM 对象
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excel);
worksheet = null;
workbook = null;
excel = null;
GC.Collect();
}
return(ds);
}
public void Export(DataTable dt, string sheetName, string title)
{
//Tạo mới một Excel WorkBook
oExcel.Visible = true;
oExcel.DisplayAlerts = false;
oExcel.Application.SheetsInNewWorkbook = 1;
oBooks = oExcel.Workbooks;
oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
oSheets = oBook.Worksheets;
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);
oSheet.Name = sheetName;
// Tạo phần đầu nếu muốn
Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "D1");
head.MergeCells = true;
head.Value2 = title;
head.Font.Bold = true;
head.Font.Name = "Times New Roman";
head.Font.Size = "16";
head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// Tạo tiêu đề cột
Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3");
cl1.Value2 = "Mã Học Sinh";
cl1.ColumnWidth = 15;
Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3");
cl2.Value2 = "Mã Lớp";
cl2.ColumnWidth = 15.0;
Microsoft.Office.Interop.Excel.Range cl3 = oSheet.get_Range("C3", "C3");
cl3.Value2 = "Tên Lớp";
cl3.ColumnWidth = 17.0;
Microsoft.Office.Interop.Excel.Range cl4 = oSheet.get_Range("D3", "D3");
cl4.Value2 = "Điểm Tổng Kết";
cl4.ColumnWidth = 15.0;
Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "D3");
rowHead.Font.Bold = true;
// Kẻ viền
rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// Thiết lập màu nền
rowHead.Interior.ColorIndex = 15;
rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable,
// vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần.
object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
//Chuyển dữ liệu từ DataTable vào mảng đối tượng
for (int r = 0; r < dt.Rows.Count; r++)
{
DataRow dr = dt.Rows[r];
for (int c = 0; c < dt.Columns.Count; c++)
{
arr[r, c] = dr[c];
}
}
//Thiết lập vùng điền dữ liệu
int rowStart = 4;
int columnStart = 1;
int rowEnd = rowStart + dt.Rows.Count - 1;
int columnEnd = dt.Columns.Count;
// Ô bắt đầu điền dữ liệu
Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart];
// Ô kết thúc điền dữ liệu
Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd];
// Lấy về vùng điền dữ liệu
Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2);
//Điền dữ liệu vào vùng đã thiết lập
range.Value2 = arr;
// Kẻ viền
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// Căn giữa cột STT
Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart];
Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3);
oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
oSheet.Cells[rowEnd + 2, 1] = "Kết quả thống kê: ";
oSheet.Cells[rowEnd + 2, 2] = "Loại Giỏi: " + gioil;
oSheet.Cells[rowEnd + 3, 2] = "Loại Khá: " + khal;
oSheet.Cells[rowEnd + 4, 2] = "Loại TB: " + tbl;
oSheet.Cells[rowEnd + 5, 2] = "Loại Yếu: " + yeul;
oSheet.Cells[rowStart - 2, 2] = " Lớp: " + lop + " Năm học: " + namlop;
}
/// <summary>
/// 输出
/// </summary>
/// <param name="title"></param>
/// <param name="coumnTitles"></param>
/// <param name="textList"></param>
public static void OutExcel(string title, ArrayList coumnTitles, ArrayList textList)
{
//外部Excel对象
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A1", System.Reflection.Missing.Value);
//标题
worksheet.get_Range(app.Cells[1, 1], app.Cells[1, 1]).Value2 = title;
worksheet.get_Range(app.Cells[1, 1], app.Cells[1, 9]).Merge(0);
worksheet.get_Range(app.Cells[1, 1], app.Cells[1, 9]).VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
worksheet.get_Range(app.Cells[1, 1], app.Cells[1, 9]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.get_Range(app.Cells[1, 1], app.Cells[1, 9]).Font.Size = 16;
worksheet.get_Range(app.Cells[1, 1], app.Cells[1, 9]).Font.Bold = true;
//列标题
for (int i = 1; i <= coumnTitles.Count; i++)
{
worksheet.get_Range(app.Cells[3, i], app.Cells[3, i]).Value2 = coumnTitles[i - 1].ToString();
worksheet.get_Range(app.Cells[3, i], app.Cells[3, i]).Font.Size = 10;
worksheet.get_Range(app.Cells[3, i], app.Cells[3, i]).Font.Bold = true;
}
//内容
for (int i = 0; i < textList.Count; i++)
{
string[] text = (string[])textList[i];
for (int j = 1; j <= text.Length; j++)
{
worksheet.get_Range(app.Cells[4 + i, j], app.Cells[4 + i, j]).Value2 = text[j - 1].ToString();
worksheet.get_Range(app.Cells[4 + i, j], app.Cells[4 + i, j]).Font.Size = 9;
}
}
}
public string ReadExistingExcel(List <ExcellData> data, string currency, string day, string month, string year, ExcelType excelType)
{
string excelName = "İşçiTemp";
if (excelType == ExcelType.Official)
{
excelName = "MemurTemp";
}
var path = GetFilePath(excelName);
excel = new Microsoft.Office.Interop.Excel.Application
{
Visible = true,
DisplayAlerts = false
};
try
{
workBook = excel.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
excel.Visible = false;
workSheets = workBook.Worksheets;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workSheets.get_Item("kurummaas");
Microsoft.Office.Interop.Excel.Range range = worksheet.UsedRange;
int colCount = range.Columns.Count;
int rowCount = range.Rows.Count;
worksheet.Cells[7, 3] = month;
worksheet.Cells[8, 4] = currency;
worksheet.Cells[4, 3] = string.Concat(day, "/", month, "/", year);
//worksheet.Cells[5, 4] = totalAmount;
//worksheet.Cells[6, 4] = data.Count;
int rowBeginning = 10;
foreach (var item in data)
{
rowBeginning++;
for (int index = 1; index < 2; index++)
{
worksheet.Cells[rowBeginning, 1] = item.FullName;
worksheet.Cells[rowBeginning, 2] = item.AccountNumber;
worksheet.Cells[rowBeginning, 3] = item.RegisterNumber;
worksheet.Cells[rowBeginning, 4] = item.Amount;
worksheet.Cells[rowBeginning, 5] = item.Iban;
}
}
year = year.Substring(2, 2);
string name = string.Concat("\\Sources\\İSO-DER-SM-", excelName.Replace("Temp", "").ToUpper(), " (mdm) ", day, ".", month, ".", year, ".xls");
string destinatonPath = string.Concat(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), name);
workBook.SaveCopyAs(destinatonPath);
workBook.Close(Missing.Value, Missing.Value, Missing.Value);
worksheet = null;
workBook = null;
excel.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
return(destinatonPath);
}
private void button2_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds.ReadXml(Application.StartupPath + "\\ExcelBindingXml.xml");
Microsoft.Office.Interop.Excel.Application m_objExcel = null;
Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
Microsoft.Office.Interop.Excel.Range m_objRange = null;
object m_objOpt = System.Reflection.Missing.Value;
try
{
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
m_objBook = m_objExcel.Workbooks.Open(Application.StartupPath + "\\ExcelTemplate.xlsx", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));
int maxRow = m_objSheet.UsedRange.Rows.Count;
int maxCol = m_objSheet.UsedRange.Columns.Count;
int TitleRow = 0;
for (int excelrow = 1; excelrow <= maxRow; excelrow++)
{
for (int col = 0; col < maxCol; col++)
{
string excelColName = ExcelColNumberToColText(col);
m_objRange = m_objSheet.get_Range(excelColName + excelrow.ToString(), m_objOpt);
m_objRange.Text.ToString().Contains("$");
TitleRow = excelrow;
break;
}
}
for (int excelrow = 1; excelrow <= ds.Tables[0].Rows.Count; excelrow++)
{
DataRow dr = ds.Tables[0].Rows[excelrow - 1];
for (int col = 0; col < maxCol; col++)
{
string excelColName = ExcelColNumberToColText(col);
m_objRange = m_objSheet.get_Range(excelColName + TitleRow.ToString(), m_objOpt);
//Microsoft.Office.Interop.Excel.Range item_objRange = m_objSheet.get_Range(excelColName + (maxRow + excelrow).ToString(), m_objOpt);
if (m_objRange.Text.ToString().Replace("$", "") == ds.Tables[0].Columns[col].ColumnName)
{
m_objSheet.Cells[maxRow + excelrow, col + 1].value = dr[col].ToString();
m_objSheet.Cells[maxRow + excelrow, col + 1].Style = m_objRange.Style;
Console.WriteLine(m_objSheet.Cells[maxRow + excelrow, col + 1].value + ":" + dr[col].ToString());
//item_objRange.Value2 = dr[col].ToString();
}
}
}
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)m_objSheet.Rows[TitleRow, m_objOpt];
range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
m_objExcel.DisplayAlerts = false;
m_objBook.SaveAs(Application.StartupPath + "\\ExcelBindingXml.xlsx", m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
m_objBook.Close(m_objOpt, m_objOpt, m_objOpt);
m_objExcel.Workbooks.Close();
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
m_objBook = null;
m_objExcel = null;
GC.Collect();
}
}
