/// <summary>
/// Method to add an excel rows
/// </summary>
/// <param name="startRange"></param>
/// <param name="rowCount"></param>
/// <param name="colCount"></param>
/// <param name="values"></param>
private void AddExcelRows
(string startRange, int rowCount, int colCount, object values)
{
_range = _sheet.get_Range(startRange, _optionalValue);
_range = _range.get_Resize(rowCount, colCount);
_range.set_Value(_optionalValue, values);
}
/// <summary>
/// Fill the excel sheet with data along with the position specified
/// </summary>
/// <param name="columnrow"></param>
/// <param name="data"></param>
private void FillExcelWithData()
{
_excelRange = _excelSheet.get_Range("A1", _value);
_excelRange = _excelRange.get_Resize(RowCount + 1, ColumnCount);
_excelRange.set_Value(Missing.Value, ExcelData);
_excelRange.EntireColumn.AutoFit();
}
/// <summary>
/// fill the header columns for the range specified and make it bold if specified
/// </summary>
/// <param name="headers"></param>
/// <param name="startColumn"></param>
/// <param name="endColumn"></param>
public void FillHeaderColumn(object[] headers, string startColumn, string endColumn)
{
_excelRange = _excelSheet.get_Range(startColumn, endColumn);
_excelRange.set_Value(_value, headers);
if (BoldHeaders == true)
{
this.BoldRow(startColumn, endColumn);
}
_excelRange.EntireColumn.AutoFit();
}
static void Main(string[] args)
{
string file = AppDomain.CurrentDomain.BaseDirectory + "SetArrayToExcel.xlsx";
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = true;
Excel.Workbook wb = excelApp.Workbooks.Open(file, 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);
object[,] testTable = new object[2, 2] {
{ "6-Feb-10", 0.1 }, { "26-Mar-10", 1.2 }
};
Excel.Worksheet ws = wb.ActiveSheet as Excel.Worksheet;
Excel.Range rng = ws.get_Range("rngSetValue", Type.Missing);
//rng.Value2 = testTable;
rng.set_Value(Type.Missing, testTable);
}
private void butexcel_Click(object sender, EventArgs e)
{
try
{
DataTable tb = null;
string ss = "";
tb = (DataTable)this.headerUnitView1.DataSource;
SystemCfg cfg2 = new SystemCfg(2);
string hospitalName = cfg2.Config;
ss = hospitalName + "药品费用统计";
// 创建Excel对象
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count + 1;
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
colCount = colCount + 1;
}
//查询条件
string swhere = "";
string tjfs = "";
string tjks = "";
string tjlx = "";
tjfs = "统计方式:" + cmbtjfs.Text;
if (cmbtjfs.SelectedIndex != 1)
{
if (rdkd.Checked)
{
tjks = "统计科室：" + rdkd.Text;
}
else
{
tjks = "统计科室：" + rdgc.Text;
}
if (rbzy.Checked)
{
tjlx = "统计类型：" + rbzy.Text;
}
if (rbcy.Checked)
{
tjlx = "统计类型：" + rbcy.Text;
}
if (rbzs.Checked)
{
tjlx = "统计类型：" + rbzs.Text;
}
}
swhere = tjfs + " " + tjks + " " + tjlx + " " + " 记费日期从:" + dtpBjksj.Value.ToString() + " 到 " + dtpEjksj.Value.ToString();
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = ss;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 设置条件
Excel.Range range1 = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
range1.MergeCells = true;
Excel.Range range2 = xlSheet.get_Range(xlApp.Cells[3, 5], xlApp.Cells[3, colCount]);
range2.MergeCells = true;
range2.set_Value(Type.Missing, "药品费用");
range2.HorizontalAlignment = Excel.Constants.xlCenter;
// 创建缓存数据
object[,] objData = new object[RowCount + 2, colCount + 1];
// 获取列标题
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
if (i >= 4)
{
objData[1, colIndex++] = "药品费用";
}
else
{
objData[1, colIndex++] = tb.Columns[i].Caption;
}
}
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
objData[2, i] = tb.Columns[i].Caption;
if (i < 4)
{
Excel.Range range3 = xlSheet.get_Range(xlApp.Cells[3, i + 1], xlApp.Cells[4, i + 1]);
range3.MergeCells = true;
range3.HorizontalAlignment = Excel.Constants.xlCenter;
}
}
// 获取数据
objData[0, 0] = swhere;
string lastName1 = "";
string lastName2 = "";
int rowBegin = 5;
int rowEnd = 4;
int rowBegin1 = 5;
int rowEnd1 = 4;
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= tb.Columns.Count - 1; j++)
{
objData[i + 3, colIndex++] = "" + tb.Rows[i][j].ToString();
if (j == 0)
{
if (lastName1 != "" && !lastName1.Equals(tb.Rows[i][j].ToString()))
{
// Fun.DebugView(tb);
MergerCell(xlApp, xlSheet, rowBegin, rowEnd, 1);
rowBegin = rowEnd + 1;
rowEnd = rowEnd + 1;
lastName1 = tb.Rows[i][j].ToString();
}
else
{
rowEnd = rowEnd + 1;
}
}
if (j == 1)
{
if (lastName2 != "" && !lastName2.Equals(tb.Rows[i][j].ToString()))
{
MergerCell(xlApp, xlSheet, rowBegin1, rowEnd1, 2);
rowBegin1 = rowEnd1 + 1;
rowEnd1 = rowEnd1 + 1;
lastName2 = tb.Rows[i][j].ToString();
}
else
{
rowEnd1 = rowEnd1 + 1;
}
}
}
lastName1 = tb.Rows[i][0].ToString();
lastName2 = tb.Rows[i][1].ToString();
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 3, colCount]);
range.Value2 = objData;
//合并单元格
//
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 3, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 3, colCount]).Select();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 3, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 3, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
public void setValue(string colName, int row, string text)
{
Excel.Range range = m_objSheet.get_Range(colName + row.ToString(), miss);
range.set_Value(miss, text);
}
/// <summary>
/// 给单元格赋值
/// </summary>
/// <param name="col">列号</param>
/// <param name="row">行号</param>
/// <param name="text">值</param>
public void setValue(int col, int row, string text)
{
Excel.Range range = m_objSheet.get_Range(this.GetAix(col, row), miss);
range.set_Value(miss, text);
}
public bool WriteExcelFile(string sReportName, string sSheetName, DateTime time1, DateTime time2, DataTable dt)
{
string[] date = new string[2];
date[0] = dealdate(time1);
date[1] = dealdate(time2);
StringBuilder sAddrModel = new StringBuilder(40);
StringBuilder sAddrReport = new StringBuilder(40);
string sFileModel = "";
string sFileDer = "";
string colA = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
char[] colABC = new char[26] {
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
};
GetPrivateProfileString("Addr", "AddrModel", "", sAddrModel, sAddrModel.Capacity, FILE_NAME);
GetPrivateProfileString("Addr", "AddrReport", "", sAddrReport, sAddrReport.Capacity, FILE_NAME);
sFileModel = sAddrModel.ToString() + sReportName.Trim() + ".xls";
if (date[0].CompareTo(date[1]) == 0)
{
sReportName = sReportName.Trim() + "(" + date[0] + ")";
}
else
{
sReportName = sReportName.Trim() + "(" + date[0] + "-" + date[1] + ")";
}
sFileDer = sAddrReport.ToString() + sReportName.Trim() + ".xls";
if (System.IO.File.Exists(sFileModel) == false)
{
MyLog.Log("3", "模板文件不存在");
return(false);
}
if (System.IO.File.Exists(sFileDer) == false)
{
System.IO.File.Copy(sFileModel, sFileDer, true);
}
else
{
System.IO.File.Delete(sFileDer);
System.IO.File.Copy(sFileModel, sFileDer, true);
}
//从表中把数据导入SHEET中
#region
string FileName;
Excel.Workbooks eworkbooks = null;
Excel._Workbook eworkbook = null;
Excel.Sheets esheets = null;
Excel._Worksheet esheet = null;
Excel.Range range = null;
excelApp.Visible = false;
excelApp.UserControl = true;
//用Excel应用程序创建Workbooks
eworkbooks = excelApp.Workbooks;
FileName = sFileDer;
eworkbooks.Open(FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
//用Workbooks创建Workbook
eworkbook = eworkbooks.get_Item(1);
//用Workbook创建sheets
esheets = eworkbook.Worksheets;
int iSheetNumber = esheets.Count;
int index = 0;
for (int i = 1; i <= iSheetNumber; i++)
{
esheet = (Excel._Worksheet)esheets.get_Item(i);
if (esheet.Name.Trim() == sSheetName.Trim())
{
index++;
break;
}
}
if (index == 0)
{
esheet = null;//没有找到相应的SHEET页
}
else
{
int _rows = dt.Rows.Count;
int _cols = dt.Columns.Count;
int j = 4;
object[] date_write = new object[4];
date_write[0] = "日期:";
date_write[1] = date[0];
date_write[2] = "至";
date_write[3] = date[1];
range = esheet.get_Range("A" + 2, "D" + 2);
range.set_Value(Missing.Value, date);
date = null;
for (int i = 0; i < _rows; i++)
{
j = i + 5;//指定从哪一行开始写入数据
object[] currentRow = new object[_cols];
currentRow = dt.Rows[i].ItemArray;
range = esheet.get_Range("A" + j, colA.Substring(_cols - 1, 1) + j);
range.set_Value(Missing.Value, currentRow);
currentRow = null;
}
}
eworkbook.Save();
range = null;
eworkbook = null;
esheet = null;
esheets = null;
eworkbooks.Close();
#endregion//excel写数据处理
return(true);
}
/// <summary>
/// 将数据填充到Excel工作表的单元格中
/// </summary>
/// <param name="startRange"></param>
/// <param name="rowCount"></param>
/// <param name="colCount"></param>
/// <param name="values"></param>
private void AddExcelRows(string startRange, int rowCount, int colCount, object values)
{
_range = _sheet.get_Range(startRange, _optionalValue);
_range = _range.get_Resize(rowCount, colCount);
_range.set_Value(_optionalValue, values);
}
