سلام
پاسخ قسمت اول بلی میشه فرمت رو تعین کرد
newSheet.Cells[i, j].NumberFormat = "m/d/yyyy"
newSheet.Cells[i, j].HorizontalAlignment = ExcelAlignment.xlLeft; //or Excel.XlHAlign.xlHAlignLeft
/////////////////فرمت تاریخ
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a1", "b1");
formatRange.NumberFormat = "mm/dd/yyyy";
//formatRange.NumberFormat = "mm/dd/yyyy hh:mm:ss";
xlWorkSheet.Cells[1, 1] = "31/5/2014";
///////////////////////ترکیب سلوس merg
xlWorkSheet.get_Range("b2", "e3").Merge(false);
///////////////اضافه کردن هدر
xlWorkSheet.get_Range("b2", "e3").Merge(false);
chartRange = xlWorkSheet.get_Range("b2", "e3");
chartRange.FormulaR1C1 = "Your Heading Here";
chartRange.HorizontalAlignment = 3;
chartRange.VerticalAlignment = 3;
پاسخ قسمت دوم
using System;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
namespace WindowsFormsApplication3
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "ID";
xlWorkSheet.Cells[1, 2] = "Name";
xlWorkSheet.Cells[2, 1] = "1";
xlWorkSheet.Cells[2, 2] = "One";
xlWorkSheet.Cells[3, 1] = "2";
xlWorkSheet.Cells[3, 2] = "Two";
xlWorkBook.SaveAs("d:\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
MessageBox.Show("Excel file created , you can find the file d:\\csharp-Excel.xls");
}
}
}
با این روش میتونی هر ستونی رو خواستی حذف کنی
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Delete a row
worksheet.DeleteRow(3);
//Delete a column
worksheet.DeleteColumn(2);
workbook.SaveAs("Book1.xlsx");
}
int cr = 1;
int cc = 1;
//حرکت تو سلول های دیتاگرید
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
if (cr == 1)
{
xlWorkSheet.Cells[cr, cc] = dataGridView1.Columns[j].HeaderText;
}
cc++;
}
cc = 1;
cr++;
}
//اینجا میتونی ستون های یی رو که نمیخایی حذف کنی
for (int i = 0; i <= dataGridView1.RowCount - 1; i++)
{
for (int j = 0; j <dataGridView1.Columns.Count; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 2, j + 1] = cell.Value;
}
}